CSV Loader Cannot Handle Nullable Ints

Jan 14, 2016 at 3:13 PM
I had a few teething issues with setting up the CSV data loader for my tests. Chief among these was that SQL Server handles booleans as a bit data type, which means that upon export, I have to go and manually edit all of the boolean fields to "true" or "false" in place or 1 or 0. This is mostly just an inconvenience though, as I could build a query to generate the correct text values for any required bulk data import.

The bigger problem I found, though, was in how nullable integer types are handled. For a long time, my CSV was failing to import due to a mysterious "Input not in correct format" exception. I could see in the stack trace that the root cause of the problem was in a call to Int32.Parse(). In the end I found that the import could only be made to work if I supplied integer values for all of the cells in a column that was supposed to map to a nullable int property (unfortunately whoever designed my enterprise database appears to have been obsessed with nullable fields).

This is incredibly unfortunate, as it would obviously make it impossible for me to test scenarios in my DAL involving null values. I'm curious if there's some deeper issue behind this limitation, or whether it's just an oversight.