SQL bulkCopy writes empty string instead NULL

Question

I have function that inserts new values into database:

public async Task BulkAdd(IDataReader data)
{
    if (Connection.State == ConnectionState.Broken || Connection.State == ConnectionState.Closed)
    {
        await Connection.OpenAsync();
    }
    using (SqlBulkCopy bulk = new SqlBulkCopy(Connection))
    {
        bulk.DestinationTableName = GetTableName();
        bulk.BatchSize = BATCH_SIZE;
        bulk.BulkCopyTimeout = 0; // for infinity write 0
        bulk.EnableStreaming = true;
        await bulk.WriteToServerAsync(data);
    }
}

insert strings are generated in order and look like: ,,11111,,7,,620,7 11111,04/15/2013 00:00:00,false,Bulgaria, and then are converted to CsvDataReader:

var csvStreamReader = MapDataExtraWithHeaders(reader, clientId, dataExtraHeadersMap, delimiter, uploadDataId, dateFormat);
using (var csv = new CsvReader(csvStreamReader))
{
    csv.Configuration.BadDataFound = null;
    csv.Configuration.Delimiter = delimiter;
    

// ADDED
csv.Configuration.TypeConverterCache.AddConverter<string>(new EmptyAsNullConverter());

    var dataReader = new CsvDataReader(csv);
      csv.ReadHeader();
       if (!HeadersValid(csv.Context.HeaderRecord, DataHeaders))
             throw new CvtException(CVTExceptionCode.Import.InvalidHeaders);
   await _transactionsDataRepository.BulkAdd(dataReader);
}

I added null constraints like:

alter table [dbo].[Extra] add constraint [DF_Custom] default (null) for [Custom]

however when I look into what was added I see that instead of NULL, empty string was added. How can that be fixed?

What CSV library do you use? Maybe you have a option on column to read empty value as null. — vernou, Sep 14 '20 at 08:58
Can you add the line where `CsvDataReader` is passed to `BulkAdd`? — vernou, Sep 14 '20 at 08:59
This question is similar to https://stackoverflow.com/questions/41364205/how-to-convert-empty-string-to-null-while-reading-csv-records — vernou, Sep 14 '20 at 09:54

score 2 · Answer 1 · answered Sep 14 '20 at 09:51

You can write a custom converter to convert empty to null :

public class EmptyAsNullConverter : CsvHelper.TypeConversion.StringConverter
{
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        if (string.IsNullOrEmpty(text))
            return null;
        else
            return base.ConvertFromString(text, row, memberMapData);
    }
}

Credit to brandonw

Example of use :

static void Main(string[] args)
{
    var text =
        "a,b,c,e,f" + Environment.NewLine +
        "a,,c,e,f" + Environment.NewLine +
        "a,b,c,,f";
    using (var csv = new CsvReader(new StringReader(text), CultureInfo.InvariantCulture))
    {
        csv.Configuration.TypeConverterCache.AddConverter<string>(new EmptyAsNullConverter());
        while(csv.Read())
        {
            for(int i = 0; i < 5; i++)
            {
                Console.Write($"{csv.GetField<string>(i) ?? "null"}\t");
            }
            Console.WriteLine();
        }
    }
    Console.Read();
}

Output :

a       b       c       e       f
a       null    c       e       f
a       b       c       null    f

Can you look at my change? I added this line ` csv.Configuration.TypeConverterCache.AddConverter(new EmptyAsNullConverter());` but it doesn't get triggered .... and values still are written as strings — user122222, Sep 16 '20 at 07:41

score 1 · Answer 2 · answered Sep 14 '20 at 09:01

you can bind CSV to class and write a converter for it.

public class EmptyStringConverter : ConverterBase
{
    public override object StringToField(string sourceString)
    {
        if (String.IsNullOrWhiteSpace(sourceString))
            return null;
        return sourceString;
    }
}

[FieldConverter(typeof(EmptyStringConverter))]
public string MyStrField;

SQL bulkCopy writes empty string instead NULL

2 Answers2