Set empty fields to null, rather than defaults when importing .CSV to SQL database

Question

I am creating an application that can import a .csv file into an SQL database. The .csv file is read into 7 columns: Id, InvoiceAmount, InvoiceDate, InvoiceNumber, DeniedDate, DeniedReasonId, and DeniedNotes. There is an Id for every row in the .csv, but only three of the next six fields will be populated. Either InvoiceAmount, InvoiceDate, InvoiceNumber OR DeniedDate, DeniedReasonId, DeniedNotes.

    [Table("TemporaryCsvUpload")]
    public class OutstandingCredit
    {
        [CsvColumn(FieldIndex = 1, CanBeNull = false)]
        public string Id { get; set; }
        [CsvColumn(FieldIndex = 2, OutputFormat = "dd MMM HH:mm:ss")]
        public DateTime InvoiceDate { get; set; }
        [CsvColumn(FieldIndex = 3)]
        public string InvoiceNumber { get; set; }
        [CsvColumn(FieldIndex = 4, OutputFormat = "C")]
        public decimal InvoiceAmount { get; set; }
        [CsvColumn(FieldIndex = 5, OutputFormat = "dd MMM HH:mm:ss")]
        public DateTime DeniedDate { get; set; }
        [CsvColumn(FieldIndex = 6)]
        public int DeniedReasonId { get; set; }
        [CsvColumn(FieldIndex = 7)]
        public string DeniedNotes { get; set; }
    }

So here's the issue: In every row in the .csv, either the InvoiceDate or DeniedDate will be empty. When this happens, I'd like the corresponding field in the database to stay null. But instead, it is defaulting to 1/1/0001 12:00. Additionally, when the DeniedReasonId is empty in the .csv, it is defaulting to 0. I am trying to keep that null instead of defaulting as well.

This is where I am using LinqToCsv to loop the values in form the .csv:

var cc = new CsvContext();
        var filePath = uploadFile(csvFile.InputStream);
        var model = cc.Read<OutstandingCredit>(filePath, inputFileDescription);

        try
        {
            var entity = new ManagementEntities();
            foreach (var item in model)
            {
                var tc = new TemporaryCsvUpload
                {
                    Id = item.Id,
                    InvoiceAmount = item.InvoiceAmount,
                    InvoiceDate = item.InvoiceDate,
                    InvoiceNumber = item.InvoiceNumber,
                    DeniedDate = item.DeniedDate,
                    DeniedReasonId = item.DeniedReasonId,
                    DeniedNotes = item.DeniedNotes
                };
                entity.TemporaryCsvUploads.Add(tc);

Any help would be appreciated. Thanks!

Why can't you use `??`. `InvoiceAmount = item.InvoiceAmount ?? String.Empty` for instance. — Greg, Jul 28 '15 at 20:45
I think you want to use `Nullable` properties and set `CanBeNull` to `true` in the `CsvColumn` attribute. Based on this [comment](http://www.codeproject.com/Messages/4970215/For-values-that-can-be-missing-properties-must-be.aspx) — juharr, Jul 28 '15 at 20:52
@juharr: write your comment up as an answer so this question can be closed properly, please. — Sam Axe, Jul 29 '15 at 00:51

juharr · Accepted Answer · 2015-07-29T12:25:02.287

If you want empty values in the CSV file to translate to null you have to change your properties to Nullable types (DateTime?, decimal?, and int?). The reason you are currently seeing 1/1/0001 12:00 AM and 0 is because those are the default values for DateTime and int. By changing them to Nullable types the default will be changed to null (Or more precisely to a Nullable<T> where HasValue is false, but C# will basically treat that as a null).

[Table("TemporaryCsvUpload")]
public class OutstandingCredit
{
    [CsvColumn(FieldIndex = 1, CanBeNull = false)]
    public string Id { get; set; }

    [CsvColumn(FieldIndex = 2, OutputFormat = "dd MMM HH:mm:ss")]
    public DateTime? InvoiceDate { get; set; }

    [CsvColumn(FieldIndex = 3)]
    public string InvoiceNumber { get; set; }

    [CsvColumn(FieldIndex = 4, OutputFormat = "C")]
    public decimal? InvoiceAmount { get; set; }

    [CsvColumn(FieldIndex = 5, OutputFormat = "dd MMM HH:mm:ss")]
    public DateTime? DeniedDate { get; set; }

    [CsvColumn(FieldIndex = 6)]
    public int? DeniedReasonId { get; set; }

    [CsvColumn(FieldIndex = 7)]
    public string DeniedNotes { get; set; }
}

You'll also have to make sure that the corresponding types in TemporaryCsvUpload are also Nullable if they are not already.

Set empty fields to null, rather than defaults when importing .CSV to SQL database

1 Answers1