Adding detectable Nullable values to CsvHelper

Question

I was wondering if CsvHelper by Josh Close has anything in the configuration I am missing to translate values to null. I am a huge fan of this library, but I always thought there should be some sort of configuration to let it know what values represent NULL in your file. An example would be a column with the value "NA", "EMPTY", "NULL", etc. I am sure I could create my own TypeConverter, but I was hoping there would be an easier option to set somewhere in a config as this tends to be fairly common with files I encounter.

Is there a configuration setting to do this relatively easily?

I found the TypeConversion in the CsvHelper.TypeConversion namespace but am not sure where to apply something like this or an example of the correct usage:

new NullableConverter(typeof(string)).ConvertFromString(new TypeConverterOptions(), "NA")

I am also using the latest version 2.2.2

Thank you!

Answer 1

CsvHelper can absolutely handle nullable types. You do not need to roll your own TypeConverter if a blank column is considered null. For my examples I am assuming you are using user-defined fluent mappings.

The first thing you need to do is construct a CsvHelper.TypeConverter object for your Nullable types. Note that I'm going to use int since strings allow null values by default.

public class MyClassMap : CsvClassMap<MyClass>
{
     public override CreateMap()
     {
          CsvHelper.TypeConversion.NullableConverter intNullableConverter = new CsvHelper.TypeConversion.NullableConverter(typeof(int?));

          Map(m => m.number).Index(2).TypeConverter(intNullableConverter);
      }
 }

Next is setting the attribute on your CsvReader object to allow blank columns & auto-trim your fields. Personally like to do this by creating a CsvConfiguration object with all of my settings prior to constructing my CsvReader object.

CsvConfiguration csvConfig = new CsvConfiguration();
csvConfig.RegisterClassMap<MyClassMap>();
csvConfig.WillThrowOnMissingField = false;
csvConfig.TrimFields = true;

Then you can call myReader = new CsvReader(stream, csvConfig) to build the CsvReader object.

IF you need to have defined values for null such as "NA" == null then you will need to roll your own CsvHelper.TypeConversion class. I recommend that you extend the NullableConverter class to do this and override both the constructor and ConvertFromString method. Using blank values as null is really your best bet though.

Answer 2

There's now a built in way to handle this through adding an attribute on properties directly. The NullValuesAttribute takes a list of strings that will be mapped as null:

    [NullValues("NULL", "0001/01/01")]
    public DateTime? BoardDate { get; init; }

Answer 3

I think some time in the last seven years and thirteen versions since this question was asked the options for doing this without a custom type map class expanded, e.g.:

csvReader.Context.TypeConverterOptionsCache.GetOptions<string>().NullValues.Add("NULL");
csvReader.Context.TypeConverterOptionsCache.GetOptions<DateTime?>().NullValues.AddRange(new[] { "NULL", "0" });
csvReader.Context.TypeConverterOptionsCache.GetOptions<int?>().NullValues.Add("NULL");
csvReader.Context.TypeConverterOptionsCache.GetOptions<bool>().BooleanFalseValues.Add("0");
csvReader.Context.TypeConverterOptionsCache.GetOptions<bool>().BooleanTrueValues.Add("1");

Answer 4

I used "ConvertUsing"...

public class RecordMap : CsvHelper.Configuration.ClassMap<Record>
{
    public RecordMap()
    {
        AutoMap();
        Map(m => m.TransactionDate).ConvertUsing( NullDateTimeParser );
        Map(m => m.DepositDate).ConvertUsing( NullDateTimeParser );
    }

    public DateTime? NullDateTimeParser(IReaderRow row)
    {
        //"CurrentIndex" is a bit of a misnomer here - it's the index of the LAST GetField call so we need to +1
        //https://github.com/JoshClose/CsvHelper/issues/1168

        var rawValue = row.GetField(row.Context.CurrentIndex+1);

        if (rawValue == "NULL")
            return null;
        else
            return DateTime.Parse(rawValue);

    }
}