Reading csv file

Question

Help needed guys! I'm writing a simple import application an need to read a csv file. Show result in a datagrid and show corrupted lines of csv file in another grid, for example show the lines that are shorter than 5 values in another grid. I'm trying to do that like this:

StreamReader sr = new StreamReader(FilePath);
importingData = new Account();
string line;
string[] row = new string [5];
while ((line = sr.ReadLine()) != null)
{
    row = line.Split(',');

        importingData.Add(new Transaction
        {
            Date = DateTime.Parse(row[0]),
            Reference = row[1],
            Description = row[2],
            Amount = decimal.Parse(row[3]),
            Category = (Category)Enum.Parse(typeof(Category), row[4])
        });
}

but its very difficult to operate on arrays in this case. Is there any better way to split the values. Any suggestions appreciated

Thanks guys. The information you provided was very useful. I found another way doing that using DataTable. Very interesting approach, here is the link : link text

Answer 1

How about regular expressions? You could use named groups to get your fields from each "row", as is done in this example: http://zamov.online.fr/EXHTML/CSharp/CSharp7.html

Answer 2

Don't reinvent the wheel. Take advantage of what's already in .NET BCL.

• add a reference to the Microsoft.VisualBasic (yes, it says VisualBasic but it works in C# just as well - remember that at the end it is all just IL)
• use the Microsoft.VisualBasic.FileIO.TextFieldParser class to parse CSV file

Here is the sample code:

TextFieldParser parser = new TextFieldParser(@"c:\temp\test.csv");
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
while (!parser.EndOfData) 
{
    //Processing row
    string[] fields = parser.ReadFields();
    foreach (string field in fields) 
    {
        //TODO: Process field
    }
}
parser.Close();

It works great for me in my C# projects. Give it a try.

Edit: Here are some more links/informations

• MSDN: Read From Comma-Delimited Text Files in Visual Basic
• MSDN: TextFieldParser Class

Answer 3

CSV can get complicated real fast.

Use something robust and well-tested:
FileHelpers v2.0: http://filehelpers.sourceforge.net/

The FileHelpers are a free and easy to use .NET library to import/export data from fixed length or delimited records in files, strings or streams.

Answer 4

My experience is that there are many different csv formats. Specially how they handle escaping of quotes and delimiters within a field.

These are the variants I have ran into:

• quotes are quoted and doubled (excel) i.e. 15" -> field1,"15""",field3
• quotes are not changed unless the field is quoted for some other reason. i.e. 15" -> field1,15",fields3
• quotes are escaped with \. i.e. 15" -> field1,"15\"",field3
• quotes are not changed at all (this is not always possible to parse correctly)
• delimiter is quoted (excel). i.e. a,b -> field1,"a,b",field3
• delimiter is escaped with \. i.e. a,b -> field1,a\,b,field3

I have tried many of the existing csv parsers but there is not a single one that can handle the variants I have ran into. It is also difficult to find out from the documentation which escaping variants the parsers support.

In my projects I now use either the VB TextFieldParser or a custom splitter.

Answer 5

I use this here:

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

Last time I was looking for something like this I found it as an answer to this question.