Using the CsvHelper library, how can I read an empty field as null rather than an empty string?

user_648667 0
Viewed 2396

I'm using the CsvHelper library by Josh Close.

I'd like to configure the CsvReader to convert empty fields to null rather than an empty string, with the intent of storing these values in a database.

Notably, I am not mapping to a class but am reading in fields manually.

Attempts:

  1. I tried setting CsvConfiguration.UseNewObjectForNullReferenceMembers to false (it defaults to true), thinking perhaps that meant it wouldn't create a "new string object" when it found a null field.
  2. I tried adding a custom type converter that extended TypeConversion.StringConverter and overrode the ConvertFromString method.
  3. I tried adding an empty string to CsvConfiguration.TypeConverterOptions.getOptions.NullValues.
CsvConfiguration csvConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture);

//csvConfiguration.UseNewObjectForNullReferenceMembers = false;
//csvConfiguration.TypeConverterOptionsCache.GetOptions<string>().NullValues.Add("");
//csvConfiguration.TypeConverterCache.AddConverter<string>(new NullStringConverter());

using (StreamReader streamReader = new StreamReader(fileStream))
using (CsvReader csv = new CsvReader(streamReader, csvConfiguration))
{
    string value = csv.GetField(index); // <-- I want this to be null not ""
}

public class NullStringConverter : StringConverter
{
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        if (string.IsNullOrEmpty(text))
        {
            return null;
        } 
        else
        {
            return base.ConvertFromString(text, row, memberMapData);
        } 
    }
}
csvhelper type-conversion .net c#
1 Answers

The key is that I was using the wrong overload of CsvReader's GetField function. This is somewhat apparent in hindsight when reading the difference in the functions' descriptions, but I think it's easy to overlook and so hopefully this can help others in the future, because other questions that were similar in nature did not get me to this answer.

Before:

string value = csv.GetField(index); // Gets the raw field

After:

string value = csv.GetField<string>(index); // Gets the field converted to a string

StringConverter seemingly does not default to using null instead of empty strings, so I also needed to change the configuration using either option 2 or 3 from my initial attempts. Option 2 seems much simpler, so that's the solution I've gone with.

CsvConfiguration csvConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture);

csvConfiguration.TypeConverterOptionsCache.GetOptions<string>().NullValues.Add("");

using (StreamReader streamReader = new StreamReader(fileStream))
using (CsvReader csv = new CsvReader(streamReader, csvConfiguration))
{
    string value = csv.GetField<string>(index); // Yay this returns null!
}
user_6486670
Related