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!

Ileenileitis

Currently you will need to create a custom converter like @JNYRanger said and extend NullableConverter. Having the ability to set a configuration of other values that evaluate to null seems like a handy feature, and I'm surprised this hasn't come up before. I'll add it as a new feature request. – Desiccator

@JoshClose, Even a "PrepareData" event on the Map would work, ie Map(m => m.Prop).Prepare = func<instring,outstring>, we can sorta do this with convertusing, but that completely overrides the automap – Chung

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.

Gastric

Answer 2

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");

Yoakum

For someone new to CsvHelper in 2021 (v22.1.2), the TypeConverterOptionsCache is no longer on the Configuration property. It is now: csvReader.Context.TypeConverterOptionsCache.GetOptions<>. Not sure when it changed but thought I'd put it out there :) – Tanker

Thanks S. Rasmussen, I verified and updated the post – Yoakum

Answer 3

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);

    }
}

Chung

Answer 4

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; }

Faustino

This answer should be higher! – Burgoyne