Reading CSV files using C#

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:

using (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
        }
    }
}

It works great for me in my C# projects.

Here are some more links/informations:

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

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.


I recommend CsvHelper from Nuget.

PS: Regarding other more upvoted answers, I'm sorry but adding a reference to Microsoft.VisualBasic is:

  • Ugly
  • Not cross-platform, because it's not available in .NETCore/.NET5 (and Mono never had very good support of Visual Basic, so it may be buggy).

Tags:

C#

Csv