C# - Splitting by columns on a CSV file

Question

I want to import some data from a csv file, but I've encountered a small problem I can't really figure out.

The person who gave me this file, added comma seperated values in cells, so when I split them they will be added to the list. Instead, I would like to get all values per column as a string, I just can't really figure out how.

For example, the column I'm talking about, is about the days a restaurant is open. This can be Mo, Tu, We, Su, but it can also be Mo, Tu.

Is there a way I can just loop over de values per column, instead of by the comma seperated values?

I'm currently using it like this, but this just adds each day to the total list of values:

using (var fs = File.OpenRead(csvUrl))
using (var reader = new StreamReader(fs, Encoding.UTF8))
{
    while (!reader.EndOfStream)
    {
        var line = reader.ReadLine();
        if (i > 0)
        {
            var values = line.Split(',');
        }
    }
}

Possible duplicate of [Reading CSV file and storing values into an array](https://stackoverflow.com/questions/5282999/reading-csv-file-and-storing-values-into-an-array) — Balagurunathan Marimuthu, Jun 29 '17 at 11:32
split by comma whcih will yield you a array or list on which you can loop over — Rajshekar Reddy, Jun 29 '17 at 11:33
@RajshekarReddy Like I said, that adds each seperate day as a single value in the array. I want to group the values together per column. — , Jun 29 '17 at 11:33
So, is there some sort of quoted-field for the comma-containing cells? — grek40, Jun 29 '17 at 11:33
can you provide a sample of the input? are the columns containing commas wrapped in quotes? — Marco Forberg, Jun 29 '17 at 11:34
So, how would you ever distinguish between a change of cells and a cell-internal list of values? — grek40, Jun 29 '17 at 11:34
So you mean to say the file is `comma-separated-values` and also your values have a `comma` in it? is that what you say — Rajshekar Reddy, Jun 29 '17 at 11:35
@RandomStranger also note that `csv` can have any character used as a separator.. meaning you can have `,` or `tabs` or any other character.. But the issue is if at all it is comma separated and also you have values with comma in it.. you better make sure that is handled properly.. — Rajshekar Reddy, Jun 29 '17 at 11:37

Romano Zumbé · Accepted Answer · 2017-06-29T11:36:47.177

Use TextFieldParser to parse CSV files:

TextFieldParser parser = new TextFieldParser(new StringReader(lineContent));
parser.SetDelimiters(",");
string[] rawFields = parser.ReadFields();

lineContent is a string with the content of the current line in your file.

TextFieldParser is available in the namespace:

Microsoft.VisualBasic.FileIO

Don't mind abaout the Visual Basic part it works fine in C#

EDIT

In your code you could implement it like this:

using (var fs = File.OpenRead(csvUrl))
using (var reader = new StreamReader(fs, Encoding.UTF8))
{
    while (!reader.EndOfStream)
    {
        var line = reader.ReadLine();
        if (i > 0)
        {
            TextFieldParser parser = new TextFieldParser(new StringReader(lineContent));
            parser.SetDelimiters(",");
            string[] rawFields = parser.ReadFields();
        }
    }
}

That's exactly what I was looking for, thank you! I'll accept it whenever SO lets me — , Jun 29 '17 at 11:36

score 2 · Answer 2 · answered Jun 29 '17 at 11:39

Best solution so far to deal with CSV values is using the .NET built in libraries: Its explained here in my StackOverflow answer here: Reading CSV file and storing values into an array

For easy reference, I am including the code here as well.

using Microsoft.VisualBasic.FileIO;

var path = @"C:\Person.csv"; // Habeeb, "Dubai Media City, Dubai"
using (TextFieldParser csvParser = new TextFieldParser(path))
{
 csvParser.CommentTokens = new string[] { "#" };
 csvParser.SetDelimiters(new string[] { "," });
 csvParser.HasFieldsEnclosedInQuotes = true;

 // Skip the row with the column names
 csvParser.ReadLine();

 while (!csvParser.EndOfData)
 {
  // Read current line fields, pointer moves to the next line.
  string[] fields = csvParser.ReadFields();
  string Name = fields[0];
  string Address = fields[1];
 }
}

More details about the parser is given here: http://codeskaters.blogspot.ae/2015/11/c-easiest-csv-parser-built-in-net.html

C# - Splitting by columns on a CSV file

2 Answers2