Empty Value on DataTable Fill

Sep 27, 2013 at 6:47 PM
Edited Sep 27, 2013 at 6:49 PM
I have a file with rows like :

the delim is set to '|' no problem there. However some values are blank. Notice the || to the right 350 ....this must go into a int type column (that does allow nulls). THe datatable is set up correctly. but calling Fill using the CsvReader fails ...

System.ArgumentException: Input string was not in a correct format.Couldn't store <> in ByteSize Column. Expected type is Int32.

Is there a solution ?
Sep 30, 2013 at 3:38 AM

The CSV parser correctly reads such values as an empty string rather than null. Unfortunately, the DataTable class does not convert an empty string to null when the column is nullable. Therefore, there isn't a way for you to leverage the Fill method. You could, of course, simply forgo the Fill extension and instead do the translation yourself by converting your string.Empty to null before passing into DataTable.Rows.Add.

I will add an issue to address this, though I'm not sure how I will do so yet, since sometimes you do want an empty string to be treated as an empty string rather than null (even if the column is nullable).

Sep 30, 2013 at 3:43 AM
Here is the work item: #12699
Oct 1, 2013 at 6:25 PM
Thanks for the reply,

How about an overload to Fill with a boolean to set empty strings to nulls. This allows the caller to decide behavior.
Oct 1, 2013 at 11:51 PM
That would solve your particular case, but wouldn't help people who have a special null sentinel value (such as "<<NULL>>"), which is common when an empty string is also a valid value. Moreover, it doesn't allow per-column behavior.