Search code examples
c#csvsqlbulkcopyidatareader

Handling empty fields in csv during bulk import to database

I'm importing geocode data to our database from a csv file.

I've used the following library A fast csv reader to read the csv and then using SqlBulkCopy

Here's an example of the data I'm importing

"AB10","1BH","L",0,0,,,20
"AB10","1BR","L",39320,80570,57.14214,-2.11400,21

It works ok on good data but on the top line it will throw an exception because the database is set up to not accept null values.

Is there a way to tell bulkcopy to ignore bad data? I've tried to get the csv reader to ignore bad lines by using the in built properties of the library like so but they don't appear to work.

csv.SkipEmptyLines = true;
csv.MissingFieldAction = MissingFieldAction.ParseError;
csv.DefaultParseErrorAction = ParseErrorAction.AdvanceToNextLine;

I guess another option would be to pre-parse the csv and remove all the offending rows. Perhaps there's a better csv library out there for .net?

Solution

  • To deal with the null entries I ended up parsing the csv into a DataTable object 1000 entries at a time and then imported them as I went.