How to Read CSV file

Question

I have a big CSV file (200 MB)

ID,Item,Description
1,TV,"Excellent condition, no marks, and brand new"
2,PC, "Used, missing keyboard"
....

I need to put it into a datatable so then in my C# Windows Forms application I can build a TreeView and GridView.

I found many examples to do so..

One approach is using System.IO

using System.IO.File;

return ReadAllLines(@"C:\MyFile.csv").Select(line => line.Split(',')).ToList();

and the other approach is using GenericParsing

        var adapter = new GenericParsing.GenericParserAdapter(path);
        DataTable dt = adapter.GetDataTable();

Approach 1 has a problem is that it split columns by delimeter ","

This is a problem because in my file comma can appear in the same column as a char not as delimeter

The 2nd approach required high memory and failing due to this error

This exception was originally thrown at this call stack: [External Code] Encoder.FrmCoder.GetDataTableFromCsv(string, bool) in FrmCoder.cs Encoder.FrmCoder.FrmCoder_Load(object, System.EventArgs) in FrmCoder.cs [External Code]

Any other approach can load big file easily without issues?

I've never used GenericParsing, but I'd be surprised if a library intended to read CSV files can't handle the delimiter appearing in a quoted cell value. Perhaps you should try the more commonly used CsvHeper.io instead. — ProgrammingLlama, May 06 '21 at 03:06
Don't try to do this yourself. Use CSVHelper. There are so many edge-cases with CSV files that you won't think of. — Andy, May 06 '21 at 03:16
It's extremely [easy to use](https://duckduckgo.com/?q=c%23+csvhelper). — Andy, May 06 '21 at 03:18
There are *thousands* of CSVHelper samples here...some quite extensive...but yeah it is easy to use and learn — Ňɏssa Pøngjǣrdenlarp, May 06 '21 at 03:54
Does this answer your question? [Populating a dataset from a CSV file](https://stackoverflow.com/questions/16606753/populating-a-dataset-from-a-csv-file) — , May 06 '21 at 05:31
Does this answer your question? [export data from CSV to datatable in c#](https://stackoverflow.com/questions/31668314/export-data-from-csv-to-datatable-in-c-sharp) — , May 06 '21 at 05:31

score 0 · Accepted Answer · answered May 06 '21 at 04:19

There is a class TextFieldParser located in Microsoft.VisualBasic.FileIO namespace.

This class can be configured like:

using TextFieldParser parser = new TextFieldParser(stream)
{
    Delimiters = new[] { "," },
    HasFieldsEnclosedInQuotes = true,
    TextFieldType = FieldType.Delimited,
    TrimWhiteSpace = true,
};

Usage of the parser:

while (!parser.EndOfData)
{
    var fields = parser.ReadFields() ?? throw new InvalidOperationException("Parser unexpectedly returned no data");
    
    // your code 
}

The benefit of using a CSV parser as mentioned is: You can process line by line

Memory consumption is extremly low as you are reading the file sequentially instead of loading everything into memory.

How to Read CSV file

1 Answers1