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
– vernou, Commented Sep 14, 2020 at 8:58
Can you add the line where CsvDataReader is passed to BulkAdd? — vernou
– vernou, Commented Sep 14, 2020 at 8:59
This question is similar to stackoverflow.com/questions/41364205/… — vernou
– vernou, Commented Sep 14, 2020 at 9:54

vernou · Accepted Answer · 2020-09-14 09:51:14Z

2

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

answered Sep 14, 2020 at 9:51

vernou

7,6825 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user122222 Over a year ago

Can you look at my change? I added this line ` csv.Configuration.TypeConverterCache.AddConverter<string>(new EmptyAsNullConverter());` but it doesn't get triggered .... and values still are written as strings

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-09-14 09:01:07Z

1

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;

answered Sep 14, 2020 at 9:01

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Collectives™ on Stack Overflow

SQL bulkCopy writes empty string instead NULL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related