Parsing CSV data

Question

I am trying to parse a CSV file with data with no luck, i have tried a bunch of tools online and none has been able to parse the CSV file correctly. I am baffled by the fact that i am in here asking for help as one would think parsing CSV data would be something super easy.

The format of the CSV data is like this:

",95,54070,3635,""Test Reservation"",0,102,0.00,0.00,2014-12-31,""Name of customer"",""$12.34 + $10, special price"",""extra information"",,CustomerName,,,,,1234567890,youremail@domain.com,CustomerName,2014-12-31,23:59:59,16,0,60,2,120,0,NULL,NULL,NULL,"

Current code:

private void btnOpenFileDialog_Click(object sender, EventArgs e)
    {
        DialogResult result = openFileDialog1.ShowDialog();

        if (result == DialogResult.OK)
        {
            using (StreamReader reader = new StreamReader(openFileDialog1.FileName))
            {
                string line;

                while ((line = reader.ReadLine()) != null)
                {
                    ParseCsvLine(line);
                }
            }
        }
    }

    private void ParseCsvLine(string line)
    {
        if (line != string.Empty)
        {
            string[] result;

            using (var csvParser = new TextFieldParser(new StringReader(line)))
            {
                csvParser.Delimiters = new string[] { "," };

                result = csvParser.ReadFields();
            }

            foreach (var item in result)
            {
                Console.WriteLine(item + Environment.NewLine);
            }
        }
    }

The result variable only has one item and its:

,95,54070,3635,"Test Reservation",0,102,0.00,0.00,2014-12-31,"Name of customer","$12.34 + $10, special price","extra information",,CustomerName,,,,,1234567890,youremail@domain.com,CustomerName,2014-12-31,23:59:59,16,0,60,2,120,0,NULL,NULL,NULL,

Well, your delimiter seems to be a coma in your example and you use a tab in your split command. — David Brabant, Nov 24 '14 at 11:16

mihai · Accepted Answer · 2014-11-24T13:09:55.997

0

// Add Microsoft.VisualBasic.dll to References.
using Microsoft.VisualBasic.FileIO; 

// input is your original line from csv.

// Remove starting and ending quotes.
input = input.Remove(0, 1);
input = input.Remove(input.Length - 1);

// Replace double quotes with single quotes.
input = input.Replace("\"\"", "\"");

string[] result;
using (var csvParser = new TextFieldParser(new StringReader(input)))
{
    csvParser.Delimiters = new string[] { "," };
    result = csvParser.ReadFields();
}

edited Nov 24 '14 at 13:09

answered Nov 24 '14 at 11:25

mihai

3,988
3
23
41

Mihai any other suggestion? – user2595352 Nov 24 '14 at 13:10
@user2595352, yes, just posted my edit :) you'll have to remove the double quotes and replace them with single quotes. Also, I've removed the starting and ending quotes from your line. This is kinda messy. Maybe there is another solution out there. – mihai Nov 24 '14 at 13:11
Yes that seem to read it all correctly! :) I am baffled as to why this seem to be such a complicated task though, i mean i thought CSV had some sort of standard to it and would be easy to read. Thank you for all the help! – user2595352 Nov 24 '14 at 13:20
@user2595352, well, I think you'll come over these kind of issues more often than you might like. I agree with you though. Please don't forget to [accept the answer](http://stackoverflow.com/help/someone-answers). – mihai Nov 24 '14 at 13:23

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

You can check out a previous post that deals with those pesky commas in csv files. I'm linking it here.

Also Mihai, your solution works well for just the one line but will fail once there are many lines to parse.

edited May 23 '17 at 11:50

Community

1
1

answered Nov 24 '14 at 11:42

No, I don't think it will fail. Just extract the solution into a function that accepts one `string` and returns `string[]`. Also, your post is not an answer. You should use comments for this. – mihai Nov 24 '14 at 11:45

Parsing CSV data

2 Answers2