导入 CSV 文件错误:包含列分隔符的列值

2021-12-30 00:00:00 csv sql delimiter sql-server ssis

我正在尝试使用 SSIS 将 Csv 文件导入 SQL SERVER

I am trying to Import a Csv File into SQL SERVER using SSIS

这是一个数据看起来如何的例子

Here's an example how data looks like 

Student_Name,Student_DOB,Student_ID,Student_Notes,Student_Gender,Student_Mother_Name
Joseph Jade,2005-01-01,1,Good listener,Male,Amy
Amy Jade,2006-01-01,1,Good in science,Female,Amy
....

Csv 列不包含文本限定符(引号)

Csv Columns are not containing text qualifiers (quotations)

我使用 SSIS 创建了一个简单的包将其导入 SQL，但有时 SQL 中的数据如下所示

I Created a simple package using SSIS to import it into SQL but sometime the data in SQL looked like below

Student_Name    Student_DOB Student_ID  Student_Notes   Student_Gender  Student_Mother_Name
Ali Jade    2004-01-01  1   Good listener   Bad in science  Male,Lisa

原因是有时 [Student_Notes] 列包含用作列分隔符的逗号 (,)，因此未正确导入行

The Reason was that somtimes [Student_Notes] column contains Comma (,) that is used as column delimiter so the Row are not imported Correctly

任何建议

推荐答案

警告:我不是一个普通的 C# 编码员.

A word of warning: I'm not a regular C# coder.

但无论如何这段代码做了以下事情:

But anyway this code does the following:

它打开一个名为 C:Input.TXT 的文件

It opens a file called C:Input.TXT

它搜索每一行.如果该行有超过 5 个逗号，它会从倒数第三个字段(注释)中取出所有多余的逗号

It searches each line. If the line has more than 5 commas, it takes all the extra commas out of the third last field (notes)

它将结果写入 C:Output.TXT - 这是您实际需要导入的内容

It writes the result to C:Output.TXT - that's the one you need to actually import

还有很多可以改进的地方:

There are many improvements that could be made:

  • 从连接管理器获取文件路径
  • 错误处理
  • 经验丰富的 C# 程序员可能会在代码中完成此操作

请记住，您的包需要对相应文件夹的写入权限

Keep in mind your package will need write access to the appropriate folder

public void Main()
{
    // Search the file and remove extra commas from the third last field
    // Extended from code at
    // http://stackoverflow.com/questions/1915632/open-a-file-and-replace-strings-in-c-sharp
    // Nick McDermaid        

    string sInputLine;
    string sOutputLine;
    string sDelimiter = ",";
    String[] sData;
    int iIndex;

    // open the file for read
    using (System.IO.FileStream inputStream = File.OpenRead("C:\Input.txt"))
    {
        using (StreamReader inputReader = new StreamReader(inputStream))
        {
            // open the output file
            using (StreamWriter outputWriter = File.AppendText("C:\Output.txt"))
            {
                // Read each line
                while (null != (sInputLine = inputReader.ReadLine()))
                {
                    // Grab each field out
                    sData = sInputLine.Split(sDelimiter[0]);
                    if (sData.Length <= 6)
                    {
                        // 6 or less fields - just echo it out
                        sOutputLine = sInputLine;
                    }
                    else
                    {
                        // line has more than 6 pieces 
                        // We assume all of the extra commas are in the notes field                                

                        // Put the first three fields together
                        sOutputLine =
                            sData[0] + sDelimiter +
                            sData[1] + sDelimiter +
                            sData[2] + sDelimiter;

                        // Put the middle notes fields together, excluding the delimiter
                        for (iIndex=3; iIndex <= sData.Length - 3; iIndex++)
                        {
                            sOutputLine = sOutputLine + sData[iIndex] + " ";
                        }

                        // Tack on the last two fields
                        sOutputLine = sOutputLine +
                            sDelimiter + sData[sData.Length - 2] +
                            sDelimiter + sData[sData.Length - 1];


                    }

                    // We've evaulted the correct line now write it out
                    outputWriter.WriteLine(sOutputLine);
                }
            }
        }
    }


    Dts.TaskResult = (int)Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
}

相关文章