Cinchoo ETL — CSV Reader

Simple CSV file reader for .NET

64 min readJun 18, 2021

1. Introduction

ChoETL is an open source ETL (extract, transform and load) framework for .NET. It is a code based library for extracting data from multiple sources, transforming, and loading into your very own data warehouse in .NET environment. You can have data in your data warehouse in no time. This article talks about using CSVReader component offered by ChoETL framework. It is a simple utility class to extract CSV data from file / source.

Features:

Fast CSV Reader, parses CSV file with 15 columns / 500,000 records in 15 seconds.
Stream based parsers allow for ultimate performance, low resource usage, and nearly unlimited versatility scalable to any size data file, even tens or hundreds of gigabytes.
Event based data manipulation and validation allows total control over the flow of data during the bulk insert process.
Follows CSV standard file rules. Gracefully handles data fields that contain commas and line breaks.
In addition to comma, most delimiting characters can be used, including tab delimited fields.
Exposes IEnumarable list of objects - which is often used with LINQ query for projection, aggregation and filtration etc.
Supports deferred reading.
Supports processing files with culture specific date, currency and number formats.
Supports different character encoding.
Recognizes a wide variety of date, currency, enum, boolean and number formats when reading files.
Provides fine control of date, currency, enum, boolean, number formats when writing files.
Detailed and robust error handling, allowing you to quickly find and fix problems.

2. Requirement

This framework library is written in C# using .NET 4.x Framework / .NET core 2.x.

3. “Hello World!” Sample

Open VS.NET 2013 or higher
Create a sample VS.NET (.NET Framework 4.5) Console Application project
Install ChoETL via Package Manager Console using Nuget Command based on the .NET environment:

.NET Framework

install-package ChoETL

.NET Core

install-package ChoETL.NETStandard

Use the ChoETL namespace

Let’s begin by looking into a simple example of reading CSV file having 2 columns

Listing 3.1 Sample CSV data file (Emp.csv)

Id,Name
1,Tom
2,Carl
3,Mark

There are number of ways you can get the CSV file parsing started with minimal setup

3.1. Quick load — Data First Approach

It is the zero config, quick way to load a CSV file in no time. No POCO object is required. Sample code below shows how to load the file

Listing 3.1.1 Load CSV file using iterator

foreach (dynamic rec in new ChoCSVReader("Emp.csv")
    .WithFirstLineHeader())
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

Listing 3.1.2 Load CSV file using loop

var reader = new ChoCSVReader("Emp.csv").WithFirstLineHeader();
dynamic rec;
 
while ((rec = reader.Read()) != null)
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

You can also access csv fields by index as well. Sample below shows how to access them by index

Listing 3.1.3 Access by index

foreach (dynamic rec in new ChoCSVReader("Emp.csv")
    .WithFirstLineHeader())
{            
    Console.WriteLine($"Id: {rec[0]}");
    Console.WriteLine($"Name: {rec[1]}");
}

If the CSV file does not comes with header, CSVReader auto name the columns as Column1, Column2 … in the dynamic object.

3.2. Code First Approach

This is another zero config way to parse and load CSV file using POCO class. First define a simple data class to match the underlying CSV file layout

Listing 3.2.1 Simple POCO entity class

public partial class EmployeeRec
{
    public int Id { get; set; }
    public string Name { get; set; } 
}

In above, the class defines two properties matching the sample CSV file template.

Listing 3.2.2 Load CSV file

foreach (var rec in new ChoCSVReader<EmployeeRec>("Emp.csv")
    .WithFirstLineHeader())
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

3.3. Configuration First Approach

In this model, we define the CSV configuration with all the necessary parsing parameters along with CSV columns matching with the underlying CSV file.

Listing 3.3.1 Define CSV configuration

ChoCSVRecordConfiguration config = new ChoCSVRecordConfiguration();
config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration("Id", 1));
config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration("Name", 2));

In above, the class defines two properties matching the sample CSV file template.

Listing 3.3.2 Load CSV file without POCO object

foreach (dynamic rec in new ChoCSVReader("Emp.csv", config)
    .WithFirstLineHeader())
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

Listing 3.3.3 Load CSV file with POCO object

foreach (var rec in new ChoCSVReader<EmployeeRec>("Emp.csv", config).WithFirstLineHeader())
{            
  Console.WriteLine(String.Format("Id: {0}", rec.Id));
  Console.WriteLine(String.Format("Name: {0}", rec.Name));
}

3.4. Code First with Data Annotation

Data annotations is a simple attribute based configuration method where attributes can be applied to POCO classes to configure the model. id is required column and name is optional value column with default value "XXXX". If name is not present, it will take the default value.

Listing 3.4.1 Define POCO Object

[ChoCSVFileHeader]
[ChoCSVRecordObject(ObjectValidationMode = ChoObjectValidationMode.MemberLevel)]
public class EmployeeRec
{
    [Required]
    public int? Id
    {
        get;
        set;
    }
    [DefaultValue("XXXX")]
    public string Name
    {
        get;
        set;
    }public override string ToString()
    {
        return $"{Id}. {Name}.";
    }
}

The code above illustrates about defining POCO object to carry the values of each record line in the input file. Define the members in the order of CSV columns apprears in the file. [ChoCSVFileHeader] attribute tells the parser the first line is header line. By default, validaton is turned off by the parser. To turn on, you must specify either ChoObjectValidationMode as MemberLevel / ObjectLever using ChoCSVRecordObject attribute. Id is a required property. We decorated it with RequiredAttribute. Name is given default value using DefaultValueAttribute. It means that if the Name CSV column contains empty value in the file, it will be defaulted to 'XXXX' value.

It is very simple and ready to extract CSV data in no time.

Listing 3.4.2 Main Method

class Program
{
    static void Main(string[] args)
    {
        string csv = @"Id, Name
1, Mark
2, Tom";
        foreach (var rec in ChoCSVReader<EmployeeRec>.LoadText(csv))
        {
            Console.WriteLine($"Id: {rec.Id}");
            Console.WriteLine($"Name: {rec.Name}");
        }
    }
}

We start by creating a new instance of ChoCSVReader object. That's all. All the heavy lifting of parsing and loading CSV data stream into the objects is done by the parser under the hood.

By default, CSVReader discovers and uses default configuration parameters while loading CSV file. These can be overridable according to your needs. The following sections will give details about each configuration attributes.

3.5. Code First with Fluent API

This is the one another approach to define POCO entity class along with custom selective CSV configuration using Fluent API. Define POCO class

Listing 3.5.1 Define POCO Object

public class EmployeeRec
{
    public int Id
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
    public string Address
    {
        get;
        set;
    }
}

Sample below shows how to do the custom CSV field mapping using Fluent API. POCO has 3 members defined and CSV file comes with only Id, Name columns, you can use the fluent API to specify the parse to consider only Id, Name columns only.

Listing 3.5.2 Main Method

class Program
{
    static void Main(string[] args)
    {
        string csv = @"Id, Name
1, Mark
2, Tom";
        using (var p = ChoCSVReader<EmployeeRec>.LoadText(csv)
            .WithField(c => c.Id)
            .WithField(c => c.Name)
        )
        {
            foreach (var rec in p)
            {
                Console.WriteLine($"Id: {rec.Id}");
                Console.WriteLine($"Name: {rec.Name}");
            }
        }
    }
}

We start by creating a new instance of ChoCSVReader object. That's all. All the heavy lifting of parsing and loading CSV data stream into the objects is done by the parser under the hood.

4. Reading All Records

It is as easy as setting up POCO object match up with CSV file structure, you can read the whole file as enumerable pattern. It is a deferred execution mode, but takes care of making any aggregate operation on them. FYI, careful of doing aggregate operation on them, since these type of operations will load the entire file records into memory.

Listing 4.1 Read CSV File

foreach (var rec in new ChoCSVReader<EmployeeRec>("Emp.csv").WithFirstLineHeader())
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

or:

Listing 4.2 Read CSV file stream

using (var stream = File.Open(@"test.csv", FileMode.Open))
{
    foreach (var rec in new ChoCSVReader<EmployeeRec>(stream).WithFirstLineHeader())
    {           
        Console.WriteLine($"Id: {rec.Id}");
        Console.WriteLine($"Name: {rec.Name}");
    }
}

This model keeps your code elegant, clean, easy to read and maintain. Also leverages LINQ extension methods to to perform grouping, joining, projection, aggregation etc.

Listing 4.3 Using LINQ

var list = (from o in new ChoCSVReader<EmployeeRec>("Emp.csv").WithFirstLineHeader()
           where o.Name != null && o.Name.StartsWith("R")
           select o).ToArray();
 
foreach (var rec in list)
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

5. Read Records Manually

It is as easy as setting up POCO object match up with CSV file structure, you can read the whole file as enumerable pattern

Listing 5.1 Read CSV file

var reader = new ChoCSVReader<EmployeeRec>("Emp.csv").WithFirstLineHeader();
EmployeeRec rec = null;
 
while ((rec = reader.Read()) != null)
{            
    Console.WriteLine($"Id: {rec.Id}");
    Console.WriteLine($"Name: {rec.Name}");
}

6. Customize CSV Record

Using ChoCSVRecordObjectAttribute, you can customize the POCO entity object declaratively.

Listing 6.1 Customizing POCO object for each record

[ChoCSVRecordObject(Encoding = "Encoding.UTF32", 
ErrorMode = ChoErrorMode.IgnoreAndContinue, IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All)]
public class EmployeeRec
{
    [ChoCSVRecordField(1, FieldName = "id")]
    public int Id { get; set; }
    [ChoCSVRecordField(2, FieldName ="Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    public string Name { get; set; }
}

Here are the available attributes to carry out customization of CSV load operation on a file.

Delimiter - The value used to separate the fields in a CSV row. Default is Culture.TextInfo.ListSeparator used.
EOLDelimiter - The value used to separate CSV rows. Default is \r\n (NewLine).
CultureName - The culture name (ex. en-US, en-GB) used to read and write CSV data.
IgnoreEmptyLine - A flag to let the reader know if a record should be skipped when reading if it's empty. A record is considered empty if all fields are empty.
Comments - The value used to denote a line that is commented out. Multiple comments can be specified. Must be separated by comma.
QuoteChar - The value used to escape fields that contain a delimiter, quote, or line ending.
QuoteAllFields - N/A for reader.
Encoding - The encoding of the CSV file.
HasExcelSeperator - N/A for reader. Reader seamlessly recognize the excel separator if specified in the CSV file and use them for parsing.
ColumnCountStrict - This flag indicates if an exception should be thrown if reading an expected field is missing.
ColumnOrderStrict - This flag indicates if an exception should be thrown if reading an expected field is in wrong position in the file. This check will be performed only when ColumnCountStrict is true.
BufferSize - The size of the internal buffer that is used when reader is from the StreamReader.
NullValue — Special null value text expect to be treated as null value from CSV file at the record level.
ErrorMode - This flag indicates if an exception should be thrown if reading and an expected field is failed to load. This can be overridden per property. Possible values are:
IgnoreAndContinue - Ignore the error, record will be skipped and continue with next.
ReportAndContinue - Report the error to POCO entity if it is of IChoNotifyRecordRead type
ThrowAndStop - Throw the error and stop the execution
IgnoreFieldValueMode - A flag to let the reader know if a record should be skipped when reading if it's empty / null. This can be overridden per property. Possible values are:
Null - N/A
DBNull - N/A
Empty - skipped if the record value is empty
WhiteSpace - skipped if the record value contains only whitespaces
ObjectValidationMode - A flag to let the reader know about the type of validation to be performed with record object. Possible values are:
Off - No object validation performed.
MemberLevel - Validation performed at the time of each CSV property gets loaded with value.
ObjectLevel - Validation performed after all the properties are loaded to the POCO object.

7. Customize CSV Header

If the CSV file has header, you can instruct the POCO entity by using ChoCSVFileHeaderAttribute.

Listing 6.1 Customizing POCO object for file header

[ChoCSVFileHeader]
public class EmployeeRec
{
    [ChoCSVRecordField(1, FieldName = "id")]
    public int Id { get; set; }
    [ChoCSVRecordField(2, FieldName ="Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    public string Name { get; set; }
}

Here are the available members to add some customization to it according to your need.

FillChar - N/A for reader
Justification - N/A for reader
IgnoreCase — true, CSV column matching are done with case insensitive. Otherwise false.
TrimOption - This flag tells the reader to trim whitespace from the beginning and ending of the CSV column header when reading. Possible values are Trim, TrimStart, TrimEnd.
Truncate - N/A for reader
IgnoreColumnsWithEmptyHeader - true, ignore CSV columns having empty headers. Otherwise false.
QuoteAll - N/A for reader
HeaderLineAt - If CSV file comes with header NOT at first line, you can use this attribute to specify the header line.
IgnoreHeader - true, to ignore the file header and load the data by field position.

8. Customize CSV Fields

For each CSV column, you can specify the mapping in POCO entity property using ChoCSVRecordFieldAttribute.

Listing 6.1 Customizing POCO object for CSV columns

[ChoCSVFileHeader]
public class EmployeeRec
{
    [ChoCSVRecordField(1, FieldName = "id")]
    public int Id { get; set; }
    [ChoCSVRecordField(2, FieldName ="Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    public string Name { get; set; }
}

Here are the available members to add some customization to it for each property:

FieldPosition - When mapping by position, you specify the index of the CSV column that you want to use for that property. It is 1 based.
FieldName - When mapping by name, you specify the name of the CSV column that you want to use for that property. For this to work, the CSV file must have a header record. The name you specify must match with the name of the header record.
FillChar - N/A for reader.
FieldValueJustification - N/A for reader.
FieldValueTrimOption - This flag tells the reader to trim whitespace from the beginning and ending of the field value when reading. Possible values are Trim, TrimStart, TrimEnd.
Truncate - Truncate field value if over maximum field size.
Size - Maxiimum field value size of field.
QuoteField - A flag that tells the reader that the CSV column value is surrounded by quotes.
NullValue — Special null value text expect to be treated as null value from CSV file at the field level.
FormatText - Specify custom format specifier to parse the CSV field values.
ErrorMode - This flag indicates if an exception should be thrown if reading and an expected field failed to load. Possible values are:
IgnoreAndContinue - Ignore the error and continue to load other properties of the record.
ReportAndContinue - Report the error to POCO entity if it is of IChoRecord type.
ThrowAndStop - Throw the error and stop the execution.
IgnoreFieldValueMode - A flag to let the reader know if a record should be skipped when reading if it's empty / null. Possible values are:
Null - N/A
DBNull - N/A
Empty - skipped if the record value is empty.
WhiteSpace - skipped if the record value contains only whitespaces.

8.1. DefaultValue

It is the value used and set to the property when the CSV value is empty or whitespace (controlled via IgnoreFieldValueMode).

Any POCO entity property can be specified with default value using System.ComponentModel.DefaultValueAttribute.

8.2. ChoFallbackValue

It is the value used and set to the property when the CSV value failed to set. Fallback value only set when ErrorMode is either IgnoreAndContinue or ReportAndContinue.

Any POCO entity property can be specified with fallback value using ChoETL.ChoFallbackValueAttribute.

8.3. Type Converters

Most of the primitive types are automatically converted and set them to the properties. If the value of the CSV field can’t automatically be converted into the type of the property, you can specify a custom / built-in .NET converters to convert the value. These can be either IValueConverter, IChoTypeConverter or TypeConverter converters.

There are couple of ways you can specify the converters for each field

Declarative Approach
Configuration Approach

8.3.1. Declarative Approach

This model is applicable to POCO entity object only. If you have POCO class, you can specify the converters to each property to carry out necessary conversion on them. Samples below shows the way to do it.

Listing 8.3.1.1 Specifying type converters

[ChoCSVFileHeader]
public class EmployeeRec
{
    [ChoCSVRecordField(1, FieldName = "id")]
    [ChoTypeConverter(typeof(IntConverter))]
    public int Id { get; set; }
    [ChoCSVRecordField(2, FieldName ="Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    public string Name { get; set; }
}

Listing 8.3.1.2 IntConverter implementation

public class IntConverter : IValueConverter
{
    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
    {
        return value;
    }
 
    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
    {
        return value;
    }
}

In the example above, we defined custom IntConverter class. And showed how to use it with 'Id' CSV property.

8.3.2. Configuration Approach

This model is applicable to both dynamic and POCO entity object. This gives freedom to attach the converters to each property at runtime. This takes the precedence over the declarative converters on POCO classes.

Listing 8.3.2.1 Specifying TypeConverters

ChoCSVRecordConfiguration config = new ChoCSVRecordConfiguration();
config.FileHeaderConfiguration.HasHeaderRecord = true;
config.ThrowAndStopOnMissingField = false;ChoCSVRecordFieldConfiguration idConfig = new ChoCSVRecordFieldConfiguration("Id", 1);
idConfig.AddConverter(new IntConverter());
config.CSVRecordFieldConfigurations.Add(idConfig);config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration("Name", 2));
config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration("Name1", 2));

In above, we construct and attach the IntConverter to ‘Id’ field using AddConverter helper method in ChoCSVRecordFieldConfiguration object.

Likewise, if you want to remove any converter from it, you can use RemoveConverter on ChoCSVRecordFieldConfiguration object.

8.3.3. Custom Value Converter Approach

This approach allows to attach value converter to each CSV member using Fluenrt API. This is quick way to handle any odd conversion process and avoid creating value converter class.

Listing 8.3.3.1 POCO class

[ChoCSVFileHeader]
public class EmployeeRec
{
    [ChoCSVRecordField(1, FieldName = "id")]
    public int Id { get; set; }
    [ChoCSVRecordField(2, FieldName ="Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    public string Name { get; set; }
}

With the fluent API, sample below shows how to attach value converter to Id column

Listing 8.3.3.2 Attaching Value Converter

using (var dr = new ChoCSVReader<EmployeeRec>(@"Test.csv")
    .WithFirstLineHeader()
    .WithField(c => c.Id, valueConverter: (v) => Convert.ToInt32(v as string))
    )
{
    foreach (var rec in dr)
    {
        Console.WriteLine(rec.Id);
    }
}

8.4. Validations

CSVReader leverages both System.ComponentModel.DataAnnotations and Validation Block validation attributes to specify validation rules for individual fields of POCO entity. Refer to the MSDN site for a list of available DataAnnotations validation attributes.

Listing 8.4.1 Using validation attributes in POCO entity

[ChoCSVFileHeader]
[ChoCSVRecordObject(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
       IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public partial class EmployeeRec
{
    [ChoCSVRecordField(1, FieldName = "id")]
    [ChoTypeConverter(typeof(IntConverter))]
    [Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
    [ChoFallbackValue(1)]
    public int Id { get; set; }
 
    [ChoCSVRecordField(2, FieldName = "Name")]
    [Required]
    [DefaultValue("ZZZ")]
    [ChoFallbackValue("XXX")]
    public string Name { get; set; }
}

In example above, used Range validation attribute for Id property. Required validation attribute to Name property. CSVReader performs validation on them during load based on Configuration.ObjectValidationMode is set to ChoObjectValidationMode.MemberLevel or ChoObjectValidationMode.ObjectLevel.

Sometime you may want override the defined declarative validation behaviors comes with POCO class, you can do with Cinchoo ETL via configuration approach. The sample below shows the way to override them.

static void ValidationOverridePOCOTest()
{
    ChoCSVRecordConfiguration config = new ChoCSVRecordConfiguration();
    var idConfig = new ChoCSVRecordFieldConfiguration("Id", 1);
    idConfig.Validators = new ValidationAttribute[] { new RequiredAttribute() };
    config.CSVRecordFieldConfigurations.Add(idConfig);
    config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration("Name", 2));
    config.CSVRecordFieldConfigurations.Add(new ChoCSVRecordFieldConfiguration("Salary", 3) { FieldType = typeof(ChoCurrency) });
 
    using (var stream = new MemoryStream())
    using (var reader = new StreamReader(stream))
    using (var writer = new StreamWriter(stream))
    using (var parser = new ChoCSVReader<EmployeeRecWithCurrency>(reader, config))
    {
        writer.WriteLine("1,Carl,$100000");
        writer.WriteLine("2,Mark,$50000");
        writer.WriteLine("3,Tom,1000");
 
        writer.Flush();
        stream.Position = 0;
 
        object rec;
        while ((rec = parser.Read()) != null)
        {
            Console.WriteLine(String.Format("Id: {0}", rec.Id));
            Console.WriteLine(String.Format("Name: {0}", rec.Name));
            Console.WriteLine(String.Format("Salary: {0}", rec.Salary));
        }
    }
}
 
public class EmployeeRecWithCurrency
{
    public int? Id { get; set; }
    public string Name { get; set; }
    public ChoCurrency Salary { get; set; }
}

Some cases, you may want to take control and perform manual self validation within the POCO entity class. This can be achieved by inheriting POCO object from IChoValidatable interface.

Listing 8.4.2 Manual validation on POCO entity

[ChoCSVFileHeader]
[ChoCSVRecordObject(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
       IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public partial class EmployeeRec : IChoValidatable
{
    [ChoCSVRecordField(1, FieldName = "id")]
    [ChoTypeConverter(typeof(IntConverter))]
    [ChoFallbackValue(1)]
    public int Id { get; set; }
 
    [ChoCSVRecordField(2, FieldName = "Name")]
    [Required]
    [DefaultValue("ZZZ")]
    [ChoFallbackValue("XXX")]
    public string Name { get; set; }
 
    public bool TryValidate(object target, ICollection<ValidationResult> validationResults)
    {
        return true;
    }
 
    public bool TryValidateFor(object target, string memberName, ICollection<ValidationResult> validationResults)
    {
        return true;
    } 
}

Sample above shows how to implement custom self-validation in POCO object.

IChoValidatable interface exposes below methods

TryValidate — Validate entire object, return true if all validation passed. Otherwise return false.
TryValidateFor — Validate specific property of the object, return true if all validation passed. Otherwise return false.

8.5. ChoIgnoreMember

If you want to ignore a POCO class member from CSV parsing in OptOut mode, decorate them with ChoIgnoreMemberAttribute. Sample below shows Title member is ignored from CSV loading process.

Listing 8.5.1 Ignore a member

[ChoCSVFileHeader]
public class EmployeeRec
{
    public int Id { get; set; }
    public string Name { get; set; }
    [ChoIgnoreMember]
    public string Title { get; set; }
}

8.6. StringLength

In OptOut mode, you can specify the size of the CSV column by using System.ComponentModel.DataAnnotations.StringLengthAttribute.

Listing 8.6.1 Specifying Size of CSV member

[ChoCSVFileHeader]
public class EmployeeRec
{
    public int Id { get; set; }
    [StringLength(25)]
    public string Name { get; set; }
    [ChoIgnoreMember]
    public string Title { get; set; }
}

8.7. Display

In OptOut mode, you can specify the name of CSV column mapped to member using System.ComponentModel.DataAnnotations.DisplayAttribute.

Listing 8.7.1 Specifying name of CSV column

[ChoCSVFileHeader]
public class EmployeeRec
{
    public int Id { get; set; }
    [Display(Name="FullName")]
    [StringLength(25)]
    public string Name { get; set; }
    [ChoIgnoreMember]
    public string Title { get; set; }
}

8.8. DisplayName

In OptOut mode, you can specify the name of CSV column mapped to member using System.ComponentModel.DataAnnotations.DisplayNameAttribute.

Listing 8.8.1 Specifying name of CSV column

[ChoCSVFileHeader]
public class EmployeeRec
{
    public int Id { get; set; }
    [DisplayName("FullName")]
    [StringLength(25)]
    public string Name { get; set; }
    [ChoIgnoreMember]
    public string Title { get; set; }
}

9. Excel Field Separator

If the input CSV file contains Excel field separator, the reader automatically detects and use them as field separator.

Listing 9.1 Sample CSV file with Excel field separator

sep=,
1,"Eldon Base for stackable storage shelf, platinum"
2,"1.7 Cubic Foot Compact ""Cube"" Office Refrigerators"
3,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl"
4,R380
5,Holmes HEPA Air Purifier

10. Callback Mechanism

CSVReader offers industry standard CSV parsing out of the box to handle most of the parsing needs. If the parsing is not handling any of the needs, you can use the callback mechanism offered by CSVReader to handle such situations. In order to participate in the callback mechanism, you can use either of the following models

Using event handlers exposed by CSVReader via IChoReader interface.
Inheriting POCO entity object from IChoNotifyRecordRead / IChoNotifyFileRead / IChoNotifyRecordFieldRead interfaces
Inheriting DataAnnotation’s MetadataType type object by IChoNotifyRecordRead / IChoNotifyFileRead / IChoNotifyRecordFieldRead interfaces.
Inheriting IChoNotifyRecordFieldConfigurable / IChoNotifyRecordFieldConfigurable configuration interfaces

Note: Any exceptions raised out of these interface methods will be ignored.

IChoReader exposes the below events:

BeginLoad - Invoked at the begin of the CSV file load
EndLoad - Invoked at the end of the CSV file load
BeforeRecordLoad - Raised before the CSV record load
AfterRecordLoad - Raised after CSV record load
RecordLoadError - Raised when CSV record load errors out
BeforeRecordFieldLoad - Raised before CSV column value load
AfterRecordFieldLoad - Raised after CSV column value load
RecordFieldLoadError - Raised when CSV column value errors out
SkipUntil — Raised before the CSV parsing kicks off to add custom logic to skip record lines.
DoWhile — Raised during CSV parsing where you can add custom logic to stop the parsing.

IChoNotifyRecordRead exposes the below methods:

BeforeRecordLoad - Raised before the CSV record load
AfterRecordLoad - Raised after CSV record load
RecordLoadError - Raised when CSV record load errors out

IChoNotifyFileRead exposes the below methods:

BeginLoad - Invoked at the begin of the CSV file load
EndLoad - Invoked at the end of the CSV file load
SkipUntil — Raised before the CSV parsing kicks off to add custom logic to skip record lines.
DoWhile — Raised during CSV parsing where you can add custom logic to stop the parsing.

IChoNotifyRecordFieldRead exposes the below methods:

BeforeRecordFieldLoad - Raised before CSV column value load
AfterRecordFieldLoad - Raised after CSV column value load
RecordFieldLoadError - Raised when CSV column value errors out

IChoNotifyRecordConfigurable exposes the below methods:

RecondConfigure - Raised for CSV record configuration

IChoNotifyRecordFieldConfigurable exposes the below methods:

RecondFieldConfigure - Raised for each CSV record field configuration

10.1. Using CSVReader events

This is more direct and simplest way to subscribe to the callback events and handle your odd situations in parsing CSV files. Downside is that code can’t be reusable as you do by implementing IChoNotifyRecordRead with POCO record object.

Sample below shows how to use the BeforeRecordLoad callback method to skip lines stating with ‘%’ characters.

Listing 10.1.1 Using CSVReader callback events

static void IgnoreLineTest()
{
    using (var parser = new ChoCSVReader("IgnoreLineFile.csv").WithFirstLineHeader())
    {
        parser.Configuration.Encoding = Encoding.BigEndianUnicode;
 
        parser.BeforeRecordLoad += (o, e) =>
        {
            if (e.Source != null)
            {
                e.Skip = ((string)e.Source).StartsWith("%");
            }
        };
        foreach (var e in parser)
            Console.WriteLine(e.Dump());
    }
}

Likewise you can use other callback methods as well with CSVReader.

10.2. Implementing IChoNotifyRecordRead interface

Sample below shows how to implement IChoNotifyRecordRead interface to direct POCO class.

Listing 10.2.1 Direct POCO callback mechanism implementation

[ChoCSVFileHeader]
[ChoCSVRecordObject(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
       IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public partial class EmployeeRec : IChoNotifyRecordRead
{
    [ChoCSVRecordField(1, FieldName = "id")]
    [ChoTypeConverter(typeof(IntConverter))]
    [Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
    [ChoFallbackValue(1)]
    public int Id { get; set; }
    
    [ChoCSVRecordField(2, FieldName = "Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    [ChoFallbackValue("XXX")]
    public string Name { get; set; }
  
    public bool AfterRecordLoad(object target, int index, object source)
    {
        throw new NotImplementedException();
    }
 
    public bool BeforeRecordLoad(object target, int index, ref object source)
    {
        throw new NotImplementedException();
    }public bool RecordLoadError(object target, int index, object source, Exception ex)
    {
        throw new NotImplementedException();
    }
}

Sample below shows how to attach Metadata class to POCO class by using MetadataTypeAttribute on it.

Listing 10.2.2 MetaDataType based callback mechanism implementation

[ChoCSVFileHeader]
[ChoCSVRecordObject(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
       IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public class EmployeeRecMeta : IChoNotifyRecordRead
{
    [ChoCSVRecordField(1, FieldName = "id")]
    [ChoTypeConverter(typeof(IntConverter))]
    [Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
    [ChoFallbackValue(1)]
    public int Id { get; set; }[ChoCSVRecordField(2, FieldName = "Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    [ChoFallbackValue("XXX")]
    public string Name { get; set; }
 
    public bool AfterRecordLoad(object target, int index, object source)
    {
        throw new NotImplementedException();
    }
 
    public bool BeforeRecordLoad(object target, int index, ref object source)
    {
        throw new NotImplementedException();
    }
 
    public bool RecordLoadError(object target, int index, object source, Exception ex)
    {
        throw new NotImplementedException();
    }
}[MetadataType(typeof(EmployeeRecMeta))]
public partial class EmployeeRec
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Sample below shows how to attach Metadata class for sealed or third party POCO class by using ChoMetadataRefTypeAttribute on it.

Listing 10.2.3 ChoMetaDataRefType based callback mechanism implementation

[ChoMetadataRefType(typeof(EmployeeRec))]
[ChoCSVFileHeader]
[ChoCSVRecordObject(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
       IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public class EmployeeRecMeta : IChoNotifyRecordRead
{
    [ChoCSVRecordField(1, FieldName = "id")]
    [ChoTypeConverter(typeof(IntConverter))]
    [Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
    [ChoFallbackValue(1)]
    public int Id { get; set; }[ChoCSVRecordField(2, FieldName = "Name", QuoteField = true)]
    [Required]
    [DefaultValue("ZZZ")]
    [ChoFallbackValue("XXX")]
    public string Name { get; set; }
 
    public bool AfterRecordLoad(object target, int index, object source)
    {
        throw new NotImplementedException();
    }
 
    public bool BeforeRecordLoad(object target, int index, ref object source)
    {
        throw new NotImplementedException();
    }
 
    public bool RecordLoadError(object target, int index, object source, Exception ex)
    {
        throw new NotImplementedException();
    }
}public partial class EmployeeRec
{
    public int Id { get; set; }    
    public string Name { get; set; }
}

10.3. BeginLoad

This callback invoked once at the beginning of the CSV file load. source is the CSV file stream object. In here you have chance to inspect the stream, return true to continue the CSV load. Return false to stop the parsing.

Listing 10.3.1 BeginLoad Callback Sample

public bool BeginLoad(object source)
{
    StreamReader sr = source as StreamReader;
    return true;
}

10.4. EndLoad

This callback invoked once at the end of the CSV file load. source is the CSV file stream object. In here you have chance to inspect the stream, do any post steps to be performed on the stream.

Listing 10.4.1 EndLoad Callback Sample

public void EndLoad(object source)
{
    StreamReader sr = source as StreamReader;
}

10.5. BeforeRecordLoad

This callback invoked before each record line in the CSV file is loaded. target is the instance of the POCO record object. index is the line index in the file. source is the CSV record line. In here you have chance to inspect the line, and override it with new line if want to.

TIP: If you want to skip the line from loading, set the source to null.

TIP: If you want to take control of parsing and loading the record properties by yourself, set the source to String.Empty.