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