csvHelper - Make sure that empty values remain null in the datatable

Sylvain C. 0
Viewed 16

I have the following code to fill my datatable before a sqlbulkcopy:

Using (StreamReader readerDatatable = new StreamReader(filePath, Encoding.UTF8))
                            using (CsvReader csvReaderDatatable = new CsvReader(readerDatatable, csvConfig))
                            using (CsvDataReader dataReader = new CsvDataReader(csvReaderDatatable))
                            using (var dt = new DataTable())
                            {

                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<string>().NullValues.Add("NULL");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<DateTime?>().NullValues.AddRange(new[] { "NULL", "0", "N/A" });
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<float?>().NullValues.Add("NULL");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<float?>().NullValues.Add("NA");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<float?>().NullValues.Add("N/A");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<decimal?>().NullValues.Add("NULL");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<decimal?>().NullValues.Add("NA");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<decimal?>().NullValues.Add("N/A");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<bool>().BooleanFalseValues.Add("0");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<bool>().BooleanTrueValues.Add("NA");
                                csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<bool>().BooleanTrueValues.Add("1");


                                var bulkCopy = new SqlBulkCopy("Data Source=test;Initial Catalog=test;Trusted_Connection=True;");                             

                                dt.Load(dataReader);
}

The datatable is correctly populated. However the empty values (,,) are converted as empty strings ('') which makes an error when I do the bulkcopy WriteToServer (String can not be converted to real).

I thought that the TypeConverterOptionsCache would have solved my problem but it is not. How can I make sure that empty values in the csv file remain null in the datatable.

Thank you

csvhelper
2 Answers

I just added a new DataColumn to my datatable with an expression to check if there is an empty string like:

dt.Columns.Add("Lat_REAL", typeof(float), "IIF(Lat='N/A',NULL,Lat)");

It works fine for the bulk copy

Sylvain C.0

First, NullValues.Add() adds those string values that if found, you want them turned into a null value. If you have empty strings and want them turned into a null value then you have to write something like

csvReaderDatatable.Context.TypeConverterOptionsCache.GetOptions<decimal?>().NullValues.Add(string.Empty);

However, I found that even when doing that, the DataTable still wasn't loading it as a DBNull value. The only solution I could come up with is to manually rewrite all empty string values to DBNull if that column allows DBNUll.

dt.Load(dataReader);

foreach (System.Data.DataColumn col in dt.Columns) col.ReadOnly = false; 

for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Rows[i].ItemArray.Length; j++)
    {
        if (dt.Columns[j].AllowDBNull && dt.Rows[i][j] is string && (string)dt.Rows[i][j] == string.Empty)
        {
            dt.Rows[i][j] = DBNull.Value;
        }
    }
}
David Specht0
Related