0

Using SQL Server 2012, we're trying to do a BULK INSERT into a simple table (all columns are NVARCHAR) of a comma-delimited csv-file, but running into an issue when there are comma's in the data. Everything goes fine, but consider the following row found in the csv-file:

,,"Test",,"Korea, Republic of",,,,

Our BULK INSERT would correctly insert empty values and "Test", but since it doesn't look at double quotes, it would split the value "Korea, Republic of" into "Korea and Republic of", while obviously we want "Korea, Republic of". Now I have researched a bit into this, and found that we could use format files. All good and well, we can say that quotes delimit our data per cell in the format file, but now the issue becomes that empty values in the csv-files don't work anymore like the example I have shown earlier. Then it should be:

"","","Test","","Korea, Republic of","","","",""

We have been looking at this for a while now, and I think the only thing we found was a new feature for BULK INSERT in SQL Server 2017, but we're using 2012 and upgrading is not a possibility. Also, we don't have much possibility in changing the source file, since pipe-separated, there would actually be no issue...

Christophe Loeys
  • 196
  • 2
  • 11
  • You could import the file using `SQL Server Integration Services`? – Menno Apr 26 '19 at 12:22
  • @Menno I should have said this as well, we used to do it with SSIS, but are trying to step off that. Worst case, if there is no other possible solution, we could revert back to using SSIS, but would rather not. – Christophe Loeys Apr 26 '19 at 12:27

0 Answers0