Reading CSV files using C#

ID : 20217

viewed : 47

Tags : c#csvc#

Top 5 Answer for Reading CSV files using C#

vote vote

92

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:

vote vote

87

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).
vote vote

72

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.

vote vote

60

Sometimes using libraries are cool when you do not want to reinvent the wheel, but in this case one can do the same job with fewer lines of code and easier to read compared to using libraries. Here is a different approach which I find very easy to use.

  1. In this example, I use StreamReader to read the file
  2. Regex to detect the delimiter from each line(s).
  3. An array to collect the columns from index 0 to n

using (StreamReader reader = new StreamReader(fileName))     {         string line;           while ((line = reader.ReadLine()) != null)         {             //Define pattern             Regex CSVParser = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");              //Separating columns to array             string[] X = CSVParser.Split(line);              /* Do something with X */         }     } 
vote vote

54

CSV can get complicated real fast.

Use something robust and well-tested:
FileHelpers: www.filehelpers.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.

Top 3 video Explaining Reading CSV files using C#

Related QUESTION?