CSV files are widely used for exporting and importing data. As a .NET Core developer, you can easily generate CSV files from your model data using the CSVHelper library. In this blog, we'll explore how to set up CSVHelper and use it to export data into CSV files in .NET Core, and we'll explain both the automatic column mapping and the manual column mapping approaches.

Why CSVHelper?

CSVHelper is a powerful and flexible library that simplifies the process of reading from and writing CSV files. It automatically maps class properties to CSV columns and handles complex cases like escaping commas, quotes, and other tricky aspects of CSV formats, making CSV handling easy and reliable.

Steps to Generate CSV Files from Model Data in .NET Core

Step 1: Install the Nuget package

Install-Package CsvHelper

Step 2: Defining Your Model

Assume you have a simple model representing employees with fields like ID, Name, and Position. Here's an example:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
}

Step 3: Writing Data to CSV Using Automatic Mapping

CSVHelper automatically maps the properties of your model to the CSV columns based on their names. This makes it quick and easy to export data without any additional setup. Here's how you can generate a CSV file using automatic mapping:

using CsvHelper;
using System.Globalization;
using System.IO;
using Microsoft.AspNetCore.Mvc;

public MemoryStream GetEmployeeCSV()
{
   MemoryStream memory = new MemoryStream();
        StreamWriter stream = new StreamWriter(memory);
        //memory writes with the stream writer

        CsvWriter csv = new CsvWriter(stream, CultureInfo.InvariantCulture, leaveOpen: true);
        csv.WriteHeader<Employee>(); // to add headings in a file
        csv.NextRecord();
        List<Employee> list = _db.Employee.ToList(); // get the data from database
        csv.WriteRecord(list); // To write records in file
        memory.Position = 0; //Internally, cursor is waiting in the last.         // Reset the memory stream position for reading
}

public IActionResult EmployeeCSVFile()
{
  MemoryStream memory = GetEmployeeCSV();
  return File(memory,"application/octet-stream","employee.csv");
  //File() is converting memoryStream to file, 
  //"application/octet-stream" - return type of csv file
 // employee.csv - name of the file
}

In this approach:

• Automatic Mapping: CSVHelper automatically maps each property of the Employee class to a corresponding CSV column using the property names as headers.
• Ease of Use: This method is quick and simple if you don't need to change column order, names, or exclude certain properties.

Step 4: Writing Data to CSV Using Manual Mapping with ClassMap

If you want more control over the column order, headers, or wish to exclude certain properties, you can define a ClassMap to map the columns manually. This is useful when you need to customize the structure of the CSV file.

Here's how to create a ClassMap for manual mapping:

using CsvHelper.Configuration;

public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        // Define custom column order and headers
        Map(m => m.Id).Name("Employee ID");
        Map(m => m.Name).Name("Full Name");
        Map(m => m.Position).Name("Job Title");
    }
}

You can then use this map when writing the CSV data:

public MemoryStream GetCustomEmployeeCSV()
{
    var memory = new MemoryStream();
    using (var writer = new StreamWriter(memory, leaveOpen: true))
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        // Register the custom class map
        csv.Context.RegisterClassMap<EmployeeMap>();

        // Get data and write to CSV with custom mapping
        List<Employee> employees = _db.Employee.ToList();
        csv.WriteRecords(employees);

        writer.Flush();
    }

    memory.Position = 0;
    return memory;
}

In this approach:

• Manual Mapping with ClassMap: You have full control over how each property is mapped to the CSV columns. You can customize the column order, set custom headers, or exclude fields.
• Flexibility: This method allows you to change the default mapping behavior to suit specific needs (e.g., for integration with external systems that require particular column formats).

Step 5: Methods of CsvWriter

CsvWriter provides several methods to help you control how your data is written to the CSV file. Here are the most commonly used ones:

• WriteRecord(): Writes a single object to the CSV file.

csv.WriteRecord(employee);

• WriteRecords(): Writes all objects in a collection to the CSV file. All properties of each object are written as separate columns. If headers are enabled, they are written first.

csv.WriteRecords(employees);

• WriteHeader(): Writes the object's property names as the CSV header (e.g., the first row). You can call this before writing the records.

csv.WriteHeader<Employee>();

• WriteField(): Writes a specific value to the CSV file. This is helpful when you want to manually control each column.

csv.WriteField(employee.Name);

• NextRecord(): Moves to the next line in the CSV. Use this when you want to end the current record and start a new one.

csv.NextRecord();

• Flush(): Writes the data that's been buffered in memory to the stream. This is necessary to ensure all data is actually written to the CSV file.

writer.Flush();

These methods allow you to have complete control over how the CSV file is generated. You can either use automatic methods like writeRecords() for convenience or mix-and-match writeField(), writeHeader(), NextRecord() for custom behavior.

Step 6: Reading Data from CSV

CSVHelper can also be used to read CSV files and map the data back into your models. Here's how to read a CSV file into a list of Employee objects using automatic or manual mapping:

using CsvHelper;
using System.Globalization;
using System.IO;

public List<Employee> ReadEmployeeCSV(string filePath)
{
    using (var reader = new StreamReader(filePath))
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {
        // Register custom map if needed
        // csv.Context.RegisterClassMap<EmployeeMap>();
       // Use this line if manual mapping is required

        var employees = csv.GetRecords<Employee>().ToList();
        return employees;
    }
}

Whether you use automatic or manual mapping, Getrecord<Employee>() will read the CSV data into a list of Employee objects.

Which Approach Should You Choose?

• Automatic Mapping: This is ideal when the CSV structure matches your model properties, and you want to quickly export data without much customization.
• Manual Mapping with ClassMap: Use this when you need to control the order of columns, customize headers, or exclude certain properties. This is especially useful for integrating with external systems that require specific CSV formats.

Handling CSV Customizations

• Custom Column Headers: Use ClassMap to rename headers, reorder columns, or ignore fields.
• Handling Special Characters: CSVHelper automatically handles complex cases, such as escaping commas, quotes, and new lines.
• Header Configuration: By default, CSVHelper writes headers based on property names, but you can disable this or customize it via configuration.

Conclusion

CSVHelper is a robust and simple tool for working with CSV files in .NET Core. With its intuitive API, you can quickly export and import data with minimal effort. This makes it an ideal choice for applications that need to handle large datasets or provide data export functionality. Whether you need quick and simple exports with automatic mapping or more detailed control with manual mapping, CSVHelper offers the tools to handle both scenarios.

Automatic mapping is perfect for straightforward cases, while manual mapping with ClassMap provides full control over the CSV format, making it ideal for more complex requirements.

Have questions or insights to share? Feel free to leave comments, engage with the community, and explore related articles on .NET development.

Stay Connected

Don't miss out on more informative articles and tutorials. Subscribe to our blog, and join the conversation. Your feedback and suggestions are always welcome as we continue to explore the world of .NET development together.

Edited and produced by Kratika Jain