How to Read a CSV File Into a DataTable in C#

1. Libraries for Reading a CSV File Into a DataTable in C#
2. Use the GenericParser by Andrew Rissing to Read a CSV File and Display It in a DataTable in C#
3. Use CSV Reader by Sebastien Lorion to Read a CSV File and Display It in a DataTable in C#
4. Use the Text Field Parser to Read a CSV File and Display It in a DataTable in C#

This article explores different C# libraries that can be used to parse a CSV file and show its contents in a DataTable in a C# window.

Libraries for Reading a CSV File Into a DataTable in C#

When doing programming, we often encounter a situation where we need to convert one data format into another. The CSV format is one of the formats to save some semi-structured data in a file.

A CSV file is a comma-delimited file in which data is stored in multiple columns and fields and is separated using a column delimiter (,).

Many different C# libraries are available for parsing such types of files. For this article, we will discuss the three most popular and commonly used libraries, which are:

1. Generic Parser by Andrew Rissing
2. CSV Reader by Sebastien Lorion
3. Text Field Parser

These libraries are developed in C# language and provide simple ways to get the data from CSV files and display that on the screen.

Use the GenericParser by Andrew Rissing to Read a CSV File and Display It in a DataTable in C#

GenericParser is a lightweight, portable solution that efficiently parses delimited and fixed-width data with few external requirements. Any Microsoft-supplied solution can be replaced with the GenericParser, which also offers unique functionality.

The code has a clear layout and is simple to understand and modify. This parser is the most efficient compared to the other two parsers in terms of memory and time.

Install and Use of GenericParser

To install the required library files for GenericParser, we will first create a Windows Form Application in Visual Studio and navigate to Tools->Manage Nuget Packages for the Solution, as shown in the screenshot below:

A Window will appear showing different Packages available for your project.

Head to the Browse tab, search for Generic Parser, and select Install after selecting your project from the window’s right pane. This is demonstrated in the screenshot below:

This will install all the required library files in your project. After the installation is complete, you can use the library in your code.

For the code, we have created a DataGridView in a Form to display the Data Table that will be populated from a CSV file. Open the Form1.cs file and write the code in the Form load function.

The first step is to create an object of GenericParserAdapter. We are using the Adapter class as we need to display the data in the Data Table.

The Adapter class provides us with the method to copy the whole data in the Data Table directly.

After the object is created, we provide the path for the CSV file to be read using the SetDataSource function. When the data source is provided, different attributes of the data file are set, like column delimiter, first row headers, buffer size, max number of rows, and the text qualifier.

After the initialization, we will get the data in the DataTable using the GetDataTable function. This function automatically populates all the data from the CSV file in the Data Table, including the row headers.

Let’s encapsulate this in a C# code:

DataTable dataTable = new DataTable();

using GenericParserAdapter dataParser = new GenericParserAdapter();
dataParser.SetDataSource("SampleCSVFile.csv");
dataParser.ColumnDelimiter = ',';
dataParser.FirstRowHasHeader = true;
dataParser.SkipStartingDataRows = 0;
dataParser.MaxBufferSize = 4096;
dataParser.MaxRows = 1000000;
dataParser.TextQualifier = '\"';

dataTable = dataParser.GetDataTable();

When the Data Table is populated, we will bind it with the DataGridView on the form so that all the data is displayed in tabular form.

dataGridView1.DataSource = dataTable;

When executed, this will give the following output:

Use CSV Reader by Sebastien Lorion to Read a CSV File and Display It in a DataTable in C#

The CSV reader by Sebastian Lorion is known as Lumen Works CSV Reader. It is a forward-only, non-cached reader whose design is based on System.IO.StreamReader (the same as what is sometimes called a fire-hose cursor).

It performs about 15 times faster when compared to OLEDB and regex methods, yet it uses very little memory.

To install this library, we will browse Nuget Packages (see the previous example for this step) and search for LumenWorks CSV Reader.

It has multiple classes that provide different functionalities for reading CSV files. CsvReader and CachedCsvReader are among them.

CsvReader is used when you need to read line by line and display it on the screen. CachedCsvReader is used when you need to bind the CSV data to some data viewer on the screen, like a DataGrid.

It is as simple as just opening the file and displaying it on the screen.

Let’s consider we have a sample CSV file as in the previous example, and we need to parse that CSV file and display it on the DataGridView on the Form application (just like the previous example).

using (CachedCsvReader dataReader = new CachedCsvReader(new StreamReader("SampleCSVFile.csv"),
                                                        true)) {
  // The headers in the CSV file will be the columns of the DataTable
  dataGridView1.DataSource = dataReader;
}

Since we need to display the data on a DataGridView, we used the class CachedCsvReader. Creating its objects takes in an object of StreamReader because its underlying design is based on StreamReader.

After opening the file, all the file data will be saved in the dataReader object. We will bind that object with the DataGridView on the Form screen.

The DataGridView will be automatically populated according to the number of columns and even the names of the columns. You don’t need to create the columns by yourself.

The output will be the same as the previous one, as we have used the same file for both code segments.

Use the Text Field Parser to Read a CSV File and Display It in a DataTable in C#

The TextFieldParser class was ported from the Microsoft namespace, VisualBasic.FileIO, formerly a component of Visual Basic.

Please be aware that this parser is only compatible with versions of .NET Core 3.0 and higher. It does not support .NET Core versions 2.2 and earlier.

The TextFieldParser offers methods and properties to parse structured text files. It also supports two types of files: delimited (CSV) and fixed-width.

The only drawback is that it is not as fast and efficient as the previous two parsers. Moreover, it does not provide many functionalities like the other parsers discussed in this article, like binding data with some views or getting the data in tabular form.

You need to explicitly read the data row by row and populate the table by yourself. This makes it a lengthy process and requires more coding steps.

We need to install the Nuget Package to implement the Text Field Parser. Browse through the NuGet Package window and search for Text Field Parser:

After installing the required library files, head to the code file and create a function to read the data from the CSV file. In this function, we will first create an object of TextFieldParser and a DataTable object.

Moving further, we will specify the delimitation token symbol to the object and initialize other values.

After that, we will read the file’s first row to make the columns of the Data Table and add those columns. Then, we will read and add the remaining data to the DataTable row by row.

Let’s encapsulate it in a coding function:

private static DataTable ParseDataFromCSV(string path) {
  DataTable myData = new DataTable();
  try {
    using (TextFieldParser reader = new TextFieldParser(path)) {
      reader.SetDelimiters(new string[] { "," });
      reader.HasFieldsEnclosedInQuotes = true;
      string[] col_headers = reader.ReadFields();
      foreach (string h in col_headres) {
        DataColumn d1 = new DataColumn(h);
        d1.AllowDBNull = true;
        myData.Columns.Add(d1);
      }

      while (!reader.EndOfData) {
        string[] row_data = reader.ReadFields();
        myData.Rows.Add(row_data);
      }
    }
  }

Call this function from the driver function providing the file path like this:

DataTable dataTable = new DataTable();
dataTable = ParseDataFromCSV("SampleCSVFile.csv");
dataGridView1.DataSource = dataTable;

The output of this code segment will also be the same as previous ones.

C# users can read CSV files using the straightforward and user-friendly TextFieldParser. It offers techniques and attributes that make it simple to parse files like delimited (CSV) and fixed-width files.

You don’t need to use any third-party software, as it is already included by default in .NET Core 3.0+.