Don't split the string if contains in double marks

Question

I have a text delimeted file need to convert into datatable. Given the text something like this :

Name,Contact,Email,Date Of Birth,Address
JOHN,01212121,hehe@yahoo.com,1/12/1987,"mawar rd, shah alam, selangor"
JACKSON,01223323,haha@yahoo.com,1/4/1967,"neelofa rd, sepang, selangor"
DAVID,0151212,hoho@yahoo.com,3/5/1956,"nora danish rd, klang, selangor"

And this is how i read the text file in C#

DataTable table = new DataTable();                

                    using (StreamReader sr = new StreamReader(path))
                    {
                        #region Text to csv
                        while (!sr.EndOfStream)
                        {
                            string[] line = sr.ReadLine().Split(',');
                            //table.Rows.Add(parts[0], parts[1], parts[2], parts[3], parts[4], parts[5]);

                            if (IsRowHeader)//Is user want to read first row as the header
                            {
                                foreach (string column in line)
                                {
                                    table.Columns.Add(column);
                                }

                                totalColumn = line.Count();

                                IsRowHeader = false;
                            }
                            else
                            {
                                if (totalColumn == 0)
                                {
                                    totalColumn = line.Count();

                                    for (int j = 0; j < totalColumn; j++)
                                    {
                                        table.Columns.Add();
                                    }

                                }

                                // create a DataRow using .NewRow()
                                DataRow row = table.NewRow();

                                // iterate over all columns to fill the row
                                for (int i = 0; i < line.Count(); i++)
                                {
                                    row[i] = line[i];
                                }

                                // add the current row to the DataTable
                                table.Rows.Add(row);
                            }          
                        }

The column is dynamic, the user can add or remove the column on the text file. So I need to check how many column and set to datatable, after that I will read for each line, set value to datarow and then add row to table.

If I don't remove the semicolon inside the double marks, it will show the error "Cannot find column 5" because on the first line is only 4 column (start from 0).

What the best way to deal with text delimited?

Do you need to generate the db table every time after you determine how many columns you will have? — TheBoyan, Jun 04 '15 at 10:24
possible duplicate of [How to split csv whose columns may contain ,](http://stackoverflow.com/questions/6542996/how-to-split-csv-whose-columns-may-contain) — Luke Quinane, Jun 04 '15 at 10:29

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

Don't try and re-invent the CSV-parsing wheel. Use the parser built into .NET: Microsoft.VisualBasic.FileIO.TextFieldParser

See https://stackoverflow.com/a/3508572/7122.

edited May 23 '17 at 12:21

Community

1
1

answered Jun 04 '15 at 10:25

David Arno

42,717
16
86
131

Why was this down-voted? Seems like a perfectly good answer to me. +1 – amcdermott Jun 04 '15 at 10:29
@amcdermott. I thought it had been downvoted because I'd misunderstood the question, but I don't think I have. – David Arno Jun 04 '15 at 10:31

score 0 · Answer 2 · answered Jun 04 '15 at 10:33

No, just don't. Don't try and write your own CSV parser - there's no reason to do it.

This article explains the problem and recommends using FileHelpers - which are decent enough.

There is also the Lumenworks reader which is simpler and just as useful.

Finally apparently you can just use DataSets to link to your CSV as described here. I didn't try this one, but looks interesting, if probably outdated.

Rubens Farias · Answer 3 · 2015-06-04T10:39:37.603

I usually go with something like this:

const char separator = ',';
using (var reader = new StreamReader("C:\\sample.txt"))
{

    var fields = (reader.ReadLine() ?? "").Split(separator);

    // Dynamically add the columns
    var table  = new DataTable();
    table.Columns.AddRange(fields.Select(field => new DataColumn(field)).ToArray());

    while (reader.Peek() >= 0)
    {
        var line = reader.ReadLine() ?? "";

        // Split the values considering the quoted field values
        var values = Regex.Split(line, ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)")
            .Select((value, current) => value.Trim())
            .ToArray()
            ;

        // Add those values directly
        table.Rows.Add(values);
    }

    // Demonstrate the results
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine();
        foreach (DataColumn col in table.Columns)
        {
            Console.WriteLine("{0}={1}", col.ColumnName, row[col]);
        }
    }
}

If it is actually CSV being parsed, then has a few fundamental problems. In particular, embedded newlines within quoted fields screw up parsing, and it does not handle escaped quotes within fields, and it does not strip quote characters from quoted fields. The code works fine for the given input but would not be suitable for a more general case. — dreamlax, Jun 04 '15 at 15:20

Don't split the string if contains in double marks

3 Answers3