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

Question

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:

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.
I tried adding a custom type converter that extended TypeConversion.StringConverter and overrode the ConvertFromString method.
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);
        } 
    }
}

Does this answer your question? [Adding detectable Nullable values to CsvHelper](https://stackoverflow.com/questions/18733819/adding-detectable-nullable-values-to-csvhelper) — Panagiotis Kanavos, Jan 20 '21 at 08:06
I saw that link when searching for answers to my problem, and it didn't help. I am not mapping a class like MyClassMap. It focuses on a nullable int rather than string because, "strings allow null values by default." Both of the solutions it suggests, using TypeConverterOptionsCache or creating a custom TypeConverter, rely on passing the string type explicitly to the GetField function. So for me, that was the key piece of information unavailable elsewhere. — Alec, Jan 21 '21 at 06:24

score 5 · Answer 1 · answered Jan 20 '21 at 07:56

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

For current version (v30.0.1) `TypeConverterOptionsCache` is not in `CsvConfiguration` anymore. Instead use `Context` property of the reader like so: `csv.Context.TypeConverterOptionsCache.GetOptions().NullValues.Add("");` — Aoi Karasu, Feb 24 '23 at 12:23

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

1 Answers1