Comma in a csv column

I am trying to  import a csv file into a data table. However one of the csv columns has a comma as part of the comma value. I am wondering what I need to do so the code treats the comma as part of the column value and does not think it is a separator.

Let me know if you need anything else from my end,
Thanks,
Aditya
LVL 1
pmac38CDSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dvast8nCommented:
I think using a better library that helps deal with situations like this. Here's more info....

http://www.codeproject.com/KB/database/CsvReader.aspx

0
anarki_jimbelSenior DeveloperCommented:
Normally, if some value possibly has a separator char in it - it has to be between quotas or single quotas. Or some escape chars are used for "data" comma. If not - not sure what to do. This is malformed csv...

If quotas you can distinguish it from other commas or whatever. If you want to use string.split() - you first need to replace "data" comma with something else, say, "@#$%". after splitting - revert the change. for the comma in issue.

Of course, you will need to parse the text yourself to search and handle "useful" commas.

I didn't check the reference above thoroughly, but my impression it does not handle situations like yours.
You may try to find another libraries.

I'd personally do the following:
1. Check if string has quotation chars. If not - use string.split
2. Otherwise - scan for all commas not inside quotas and put indeces to a list.
3. Prepare fields array based on this index list
0
AndyAinscowFreelance programmer / ConsultantCommented:
Use another character as the delimiter.  The character used to split the fields must NOT be a valid character for the data.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

anarki_jimbelSenior DeveloperCommented:
Indeed, if structure of csv depends on you - do as AndyAinscow says :)

But I'nm afraid it's not the case.
0
Carl TawnSystems and Integration DeveloperCommented:
If you have control of the format then "use another character as the delimeter" is a sledgehammer approach. Your values should be wrapped in quotes, as mentioned previously, if you need a comma in the value.
0
pmac38CDSAuthor Commented:
I don't have control over the format of the csv file so that might be the last option. Here is method I am using to read the csv into a datatable. If I have the comma in quotes what should the method below look like to account for the same.
public DataTable csvToDataTable(string file, bool isRowOneHeader)
    {

        DataTable csvDataTable = new DataTable();

        //no try/catch - add these in yourselfs or let exception happen
        String[] csvData = File.ReadAllLines(file);

        //if no data in file ‘manually’ throw an exception
        if (csvData.Length == 0)
        {
            throw new Exception("CSV File Appears to be Empty");
        }

        String[] headings = csvData[0].Split(comma);
        int index = 0; //will be zero or one depending on isRowOneHeader

        if (isRowOneHeader) //if first record lists headers
        {
            index = 1; //so we won’t take headings as data

            //for each heading
            for (int i = 0; i < headings.Length; i++)
            {
                //replace spaces with underscores for column names
                headings[i] = headings[i].Replace(" ", "_");

                //add a column for each heading
                csvDataTable.Columns.Add(headings[i], typeof(string));
            }
        }
        else //if no headers just go for col1, col2 etc.
        {
            for (int i = 0; i < headings.Length; i++)
            {
                //create arbitary column names
                csvDataTable.Columns.Add("col" + (i + 1).ToString(), typeof(string));
            }
        }

        //populate the DataTable
        for (int i = index; i < csvData.Length; i++)
        {
            //create new rows
            DataRow row = csvDataTable.NewRow();

            for (int j = 0; j < headings.Length; j++)
            {
                //fill them
                row[j] = csvData[i].Split(comma)[j];
            }

            //add rows to over DataTable
            csvDataTable.Rows.Add(row);
        }

        //return the CSV DataTable
        return csvDataTable;

    } 

Open in new window

0
Carl TawnSystems and Integration DeveloperCommented:
You might make it easier for yourself by using the OleDb providers ability to read CSV into a DataTable:

   http://www.switchonthecode.com/tutorials/csharp-tutorial-using-the-built-in-oledb-csv-parser
0
pmac38CDSAuthor Commented:
Prior to this I was using OleDb providers to read the csv into the datatable but I had a zipcode column with column values 12345-1234 which were not being pulled in as text.
0
tpaynCommented:
Couple of solutions to handle CSV files

1.) As carl_tawn pointed out OLeDB
2.) Use the TextFieldParser. Its hidden in the Visual Basic Namespace but it is really good. http://msdn.microsoft.com/en-us/library/cakac7e6.aspx
3.) Try this Snippet from http://www.kimgentes.com/worshiptech-web-tools-page/2008/10/14/regex-pattern-for-parsing-csv-files-with-embedded-commas-dou.html and http://www.programmersheaven.com/user/Jonathan/blog/73-Splitting-CSV-with-regex/

We have used this in a couple of our projects without errors.

private string[] SplitCsvLine(string line)
{
    return (from System.Text.RegularExpressions.Match m
            in System.Text.RegularExpressions.Regex.Matches(line,
            @"(((?<x>(?=[,\r\n]+))|""(?<x>([^""]|"""")+)""|(?<x>[^,\r\n]+)),?)",                                   System.Text.RegularExpressions.RegexOptions.ExplicitCapture)
            select m.Groups[1].Value).ToArray();
}

Open in new window

0
pmac38CDSAuthor Commented:
tpayn: How can I modify the following method to use the SplitCsvLine method
public DataTable csvToDataTable(string file, bool isRowOneHeader)
    {

        DataTable csvDataTable = new DataTable();

        //no try/catch - add these in yourselfs or let exception happen
        String[] csvData = File.ReadAllLines(file);

        //if no data in file ‘manually’ throw an exception
        if (csvData.Length == 0)
        {
            throw new Exception("CSV File Appears to be Empty");
        }

        String[] headings = csvData[0].Split(comma);
        int index = 0; //will be zero or one depending on isRowOneHeader

        if (isRowOneHeader) //if first record lists headers
        {
            index = 1; //so we won’t take headings as data

            //for each heading
            for (int i = 0; i < headings.Length; i++)
            {
                //replace spaces with underscores for column names
                headings[i] = headings[i].Replace(" ", "_");

                //add a column for each heading
                csvDataTable.Columns.Add(headings[i], typeof(string));
            }
        }
        else //if no headers just go for col1, col2 etc.
        {
            for (int i = 0; i < headings.Length; i++)
            {
                //create arbitary column names
                csvDataTable.Columns.Add("col" + (i + 1).ToString(), typeof(string));
            }
        }

        //populate the DataTable
        for (int i = index; i < csvData.Length; i++)
        {
            //create new rows
            DataRow row = csvDataTable.NewRow();

            for (int j = 0; j < headings.Length; j++)
            {
                //fill them
                row[j] = csvData[i].Split(comma)[j];
            }

            //add rows to over DataTable
            csvDataTable.Rows.Add(row);
        }

        //return the CSV DataTable
        return csvDataTable;

    } 

Open in new window

0
tpaynCommented:
Something like this
public DataTable csvToDataTable(string file, bool isRowOneHeader)
{
     DataTable csvDataTable = new DataTable();
     //no try/catch - add these in yourselfs or let exception happen
     String[] csvData = File.ReadAllLines(file);

     //if no data in file ‘manually’ throw an exception
     if (csvData.Length == 0)
     {
         throw new Exception("CSV File Appears to be Empty");
     }

     String[] headings = SplitCsvLine(csvData[0]);
     int index = 0; //will be zero or one depending on isRowOneHeader

     if (isRowOneHeader) //if first record lists headers
     {
         index = 1; //so we won’t take headings as data

         //for each heading
         for (int i = 0; i < headings.Length; i++)
         {
             //replace spaces with underscores for column names
             headings[i] = headings[i].Replace(" ", "_");

             //add a column for each heading
             csvDataTable.Columns.Add(headings[i], typeof(string));
         }
     }
     else //if no headers just go for col1, col2 etc.
     {
         for (int i = 0; i < headings.Length; i++)
         {
             //create arbitary column names
             csvDataTable.Columns.Add("col" + (i + 1).ToString(), typeof(string));
         }
     }

     //populate the DataTable
     for (int i = index; i < csvData.Length; i++)
     {
         //create new rows
         DataRow row = csvDataTable.NewRow();

         string[] fieldData = SplitCsvLine(csvData[i]);

         for (int j = 0; j < headings.Length; j++)
         {
             //fill them
             row[j] = fieldData[j];
         }

         //add rows to over DataTable
         csvDataTable.Rows.Add(row);
     }

      //return the CSV DataTable
     return csvDataTable;

 }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.