Handling DateTime fields from CsvDataReader that contains an empty string

Question

I'm trying load in data from a CSV file using CsvHelper to create a datatable with datacolumns that have a specified type.

                var textReader = new StreamReader(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"{tableName}.csv"));
                var csvReader = new CsvReader(textReader);
                var csvDataReader = new CsvDataReader(csvReader);
                var dataTable = new DataTable();
                foreach(var column in metaColumns)
                {               

                    var dataColumn = new DataColumn(column.columnName, GetPropertyType(column.dataType));
                    dataColumn.AllowDBNull = column.isNull;
                    dataTable.Columns.Add(dataColumn);
                }

                dataTable.Load(csvDataReader);

On the load method I'm getting the following error:

String '' was not recognized as a valid DateTime.Couldn't store <> in derived_mdd_date Column. Expected type is DateTime.

Apparently CsvHelper is loading the column from the CSV file as an empty string and then when given the DateTime type it's not converting the empty string to a null value.

After some research and just trying things I've added

            csvReader.Configuration.TypeConverterOptionsCache.GetOptions<DateTime>().NullValues.Add("null");
            csvReader.Configuration.TypeConverterOptionsCache.GetOptions<DateTime?>().NullValues.Add("null");
            csvReader.Configuration.TypeConverterOptionsCache.GetOptions<string>().NullValues.Add("null");
            csvReader.Configuration.TypeConverterCache.AddConverter<DateTime>(new DateFieldConverter());
            csvReader.Configuration.TypeConverterCache.AddConverter<DateTime?>(new DateFieldConverter());
...
    public class DateFieldConverter : DateTimeConverter
    {
        public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
        {
            bool result = DateTime.TryParse(text, out DateTime ret);
            if (result) return ret;
            return null;
        }
    }

Still getting the same error. I placed a breakpoint on the DateFieldConverter and it's never getting hit so something isn't syncing up correctly. I would think that the default behavior for a DateTime column would be either DateTime.MinValue or null but it's just throwing the error instead.

Answer 1

Unfortunately, it looks like CsvDataReader treats all values as strings and ignores the TypeConverters for other types. There appears to be a feature request to add that capability.

I can offer a workaround that might work for you. You might also check my answer here for another option.

public static void Main(string[] args)
{
    using (MemoryStream stream = new MemoryStream())
    using (StreamWriter writer = new StreamWriter(stream))
    using (StreamReader reader = new StreamReader(stream))
    using (CsvReader csv = new CsvReader(reader))
    {
        writer.WriteLine("DateTime,DateTimeNullable");
        writer.WriteLine("5/4/2019,");
        writer.WriteLine(",5/5/2019");
        writer.Flush();
        stream.Position = 0;

        csv.Configuration.TypeConverterCache.AddConverter<DateTime>(new DateFieldConverter());
        csv.Configuration.TypeConverterCache.AddConverter<DateTime?>(new DateFieldNullableConverter());

        var dataTable = new DataTable();
        dataTable.Columns.Add("DateTime", typeof(DateTime)).AllowDBNull = false;
        dataTable.Columns.Add("DateTimeNullable", typeof(DateTime)).AllowDBNull = true;

        csv.Read();
        csv.ReadHeader();
        while (csv.Read())
        {
            var row = dataTable.NewRow();
            foreach (DataColumn column in dataTable.Columns)
            {
                if (column.DataType == typeof(DateTime) && column.AllowDBNull)
                {
                    row[column.ColumnName] = csv.GetField(typeof(DateTime?), column.ColumnName);
                }
                else
                {
                    row[column.ColumnName] = csv.GetField(column.DataType, column.ColumnName);
                }                        
            }
            dataTable.Rows.Add(row);
        }                
    }
}

public class DateFieldConverter : DateTimeConverter
{
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        if (text == string.Empty)
        {
            return DateTime.MinValue;
        }

        return base.ConvertFromString(text, row, memberMapData);                
    }
}

public class DateFieldNullableConverter : DateTimeConverter
{
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        if (text == string.Empty)
        {
            return DBNull.Value;
        }

        return base.ConvertFromString(text, row, memberMapData);
    }
}