Read CSV File in .NET using CsvHelper

In big data applications, it is not unusual to seed a SQL database from a dataset contained in a .csv file. In this tutorial, you will learn the CsvHelper library can make this job a lot easier.

Getting Started

To get started using the CsvHelper library, first add the NuGet package. To do this from the Package Manager Console, execute the following command.

Install-Package CsvHelper

Suppose you have a .csv file called customers.csv with a header in the root of your project directory formatted as follows:

last_name,first_name,phone_number
Doe,Jane,555-123-4567
Smith,John,555-987-6543
Wells,Bradley,555-555-5555

While you are testing, remember to congigure the .csv file to copy to the output directory.

CsvHelper Class Mapping

CsvHelper works by using a standard disposable StreamReader object to open the file, and a disposable CsvReader object to process the stream. In most cases, the first step is to create a class that corresponds to the elements of the .csv file you wish to save, process, or use. In this example, create the following class.

class Customer
{
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string Phone { get; set; }
}

Next, if your variable names do not match the header values in the .csv file, you can map the .csv header values to your class's parameter names. Create a class called CustomerMap that implements ClassMap.

sealed class CustomerMap : ClassMap<Customer>
{
    public CustomerMap()
    {
        Map(m => m.LastName).Name("last_name");
        Map(m => m.FirstName).Name("first_name");
        Map(m => m.Phone).Name("phone_number");
    }
}

The value passed to Name property corresponds to the header name in the top row of the .csv file.

Read CSV File Line-by-Line

Create the using statements for the StreamReader and CsvReader objects.

using (var reader = new StreamReader(Path.Combine(Directory.GetCurrentDirectory(), "customers.csv")))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    //Do things here
}

Now, let's fill in the placeholder by adding code to read and process the file. First, register the class map you previously created.

csv.Context.RegisterClassMap<CustomerMap>();

Next, create a list of customers List<Customer> object that we can use to store the records from the .csv file in memory.

csv.Context.RegisterClassMap<CustomerMap>();
List<Customer> customers = new();

Finally, read the header line and begin to read each subsequent line row-by-row using a while loop until the end-of-file is reached. Note the Read() method returns true if there are more records in the file and false if not, so we can use this as our condition for terminating the loop.

csv.Context.RegisterClassMap<CustomerMap>();
List<Customer> customers = new();
var isHeader = true;
while (csv.Read())
{
    if (isHeader)
    {
        csv.ReadHeader();
        isHeader = false;
        continue;
    }
    else
    {
        customers.Add(csv.GetRecord<Customer>());
    }
}

If you run this program now, your List object will be populated with a Customer for each record contained in customers.csv.

To verify, you can use the debugger or print each record to the console.

foreach(var customer in customers)
{
    Console.WriteLine($"{customer.LastName}, {customer.FirstName} : {customer.Phone}");
}

The Bottom Line

This example demonstrates the use of the CsvHelper library's CsvReader class to read a .csv file line-by-line. In real-world applications, it is not unusual to have a .csv file contained hundreds of thousands of records. In this case, you will not want to store the entire contents of the file to memory as an object. Instead, you could read the file line-by-line, as in the above example, and write each record as a row to a table in a SQL database.