Import CSV File Error : Column Value containing column delimiter

Question

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 Columns are not containing text qualifiers (quotations)

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

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

Any suggestions

Change the column delimiter, it is bad practice to have the column delimiter inside column data — DanielVorph
how to do it... i am not the ont who created these csv files — Hadi
Maybe you can create a program in some programming language so you read these file line by line and process data as you want — DanielVorph
Can you automatically determine which is the offending comma? If so you can write a script to clean it up. It seems like one way to fix this would be to count the number of commas per row and if there are too many, remove them starting from the right. Can you manually apply this algorithm to your data and see if it is correct. If so I can help you write a script that cleans it up beforehand. — Nick.McDermaid
I will give you some code but a word of advice: never say 'code plz' it's kind of annoying. — Nick.McDermaid

Nick.McDermaid Nick.McDermaid · Accepted Answer · 2016-11-04T12:06:33

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

But anyway this code does the following:

It opens a file called C:\Input.TXT

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)

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:

Get file paths from connection managers
Error handling
An experienced C# programmer could probably do this in hlaf the code

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;
}

Import CSV File Error : Column Value containing column delimiter

3 Answers