Csv Helper (.NET library)

Csv Helper

If you have ever tried to manually read a CSV (Comma-separated values) file using the .NET Framework’s File and StreamReader classes you may have run into issues such as:

  • Out of range exceptions
  • Encoding issues
  • Column order changes ect

There are probably many more depending on the files size and complexity.

First prize is to read the CSV file and map the columns to a POCO object (Plain Old CLR)

In software engineering, a plain old CLR object (POCO) is a simple object created in the Common Language Runtime (CLR) of the .NET Framework which is unencumbered by inheritance or attributes.

Enter the CsvHelper library to help reading and writing CSV files by Josh Close.

CsvHelper is a .NET library for reading and writing CSV files. Extremely fast, flexible and easy to use. Supports reading and writing of custom class objects.

Consider the following CSV file with headings Internal Id,Activity,Project Name,Date,Time Spent In Hours,User

1
2
3
4
5
6
7
8
9
Internal Id,Activity,Project Name,Date,Time Spent In Hours,User

123,Cloned repository and familiarize with code base,Accounts Project,01-01-2017,2,Joe Soap

124,Refactor class a for xyz,Accounts Project,01-01-2017,1,Joe Soap

89,Complete task xyz,Accounts Project,01-01-2017,4,Sue Skye

55,Complete task abc,Accounts Project,01-01-2017,1,Sue Skye

(You can check your system separator under ‘Region’ -> ‘Additional settings…’ -> ‘Numbers’ -> ‘List separator’)

Create your POCO class:

1
2
3
4
5
6
7
8
9
public class CsvContentModel
{
  public string User { get; set; }
  public string InternalId { get; set; }
  public string Activity { get; set; }
  public string ProjectName { get; set; }
  public string Date { get; set; }
  public string TimeSpentInHours{ get; set; }
}

** For simplicity I made all the properties of type string. You can use CsvHelper’s type converter if you want to convert the integer/dates automatically.*

Create a DefinitionMap to help map the columns with spaces in the names.

If you don’t supply a mapping file, auto mapping will be used. Auto mapping will map the properties in your class in the order they appear in.

1
2
3
4
5
6
7
8
9
10
11
12
sealed class CsvContentModelDefinitionMap : CsvClassMap<CsvContentModel>
{
  public CsvContentModelDefinitionMap()
  {
    Map(m => m.User).Name("User");
    Map(m => m.InternalId).Name("Internal Id");
    Map(m => m.Activity).Name("Activity");
    Map(m => m.ProjectName).Name("Project Name");
    Map(m => m.Date).Name("Date");
    Map(m => m.TimeSpentInHours).Name("Time Spent In Hours");
  }
}

Organize your CsvHelper logic into a class.

Your catch blocks should do something more helpful with the Exceptions :)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
public class ReadFile
{
  string FilePath;

  public ReadFile(string filePath)
  {
    FilePath = filePath;
  }

  public List<CsvContentModel> Get()
  {
    if (FilePath == null)
      return new List<CsvContentModel>();

    if (FilePath == "")
      return new List<CsvContentModel>();

    List<CsvContentModel> records = new List<CsvContentModel>();

    using (TextReader reader = File.OpenText(FilePath))
    {
      try
      {
        var csv = new CsvReader(reader);
        csv.Configuration.RegisterClassMap<CsvContentModelDefinitionMap>();
        records = csv.GetRecords<CsvContentModel>().ToList();
      }
      catch (CsvMissingFieldException ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.StackTrace);
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.StackTrace);
      }
    }

    return records;
  }
}

Then call the class

1
var data = new ReadFile("sample.csv").Get();

The instance of data will now have a reference to a list of type CsvContentModel for your business logic.

Csv Helper

CsvHelper has several other powerful features such as Type Converter and Configuration Delimiter.

References