I have CSV files structured as string ; string ; string
First string is quoted with "". The other 2 strings are not.
I'm bulk inserting them into a table using nchar(x) for each of the 3 columns with a sufficiently large value for x.

BULK INSERT [table] FROM 'import.csv' WITH (FIELDTERMINATOR = ';')

This has been working for a couple of years.
Since a few days the import is giving issues on some rows. After investigation each of the failing rows gets a conversion/type mismatch error on the 3rd column.
The thing that has changed about the data is that in the middle of this 3rd string the text (DENY) is now present in each of the failing rows. This was previously never the case.

 Msg 4864, Level 16, State 1, Line 1
 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1019, column 3 (Rights).

Any ideas why the presence of the substring (DENY) causes this to happen ?
(I know it is a SQL reserved word, but why is it parsed in this position ?)
How to work around it ? I still need those rows to be imported, including that text.

Sample content of column 3:


The 2nd line gives the error. The other 2 import fine.
(And in case you are wondering: Yes, it is ICACLS output that I am parsing.)

  • Are you seeing an error message? – LowlyDBA Feb 25 '15 at 14:50
  • @JohnM Added the exact error message. The error refers to the line with the data-sample I gave further down. – Tonny Feb 25 '15 at 15:08
  • I'm not sure it's the deny word that is causing the problem. I just did a quick test and was able to bulk insert a file with your example data without issue. Is it possible that that are some hidden/special characters in those rows as well. you can use something like notepad++ to open the file and view hidden/special characters. – Bob Klimes Feb 25 '15 at 15:45
  • @BobKlimes Nothing hidden. Already checked the files with Notepad++. And the files are generated by a script I wrote myself. I can vouch for there not being any weird hidden characters. I have about 300 sample lines in 12 files that cause the error. They ALL have that (DENY) part. If I remove the word DENY form those lines (or just remove 1 character of the word) they import fine. I can do that as a final cleanup step in my own script, but I like to avoid that if possible, because I would like to keep all the post-processing in 1 place (SQL). – Tonny Feb 25 '15 at 16:11
  • 4
    I'm voting to close this question as off-topic because it lacks a clear reproduction script. – Paul White 9 Feb 27 '15 at 15:20

After some experimentation I found the following:

The word DENY is indeed a red herring. The problem is caused by the (xx,xx,xx,xx) sequence behind it.
For some obscure reason this causes errors if the bulk insert is run from a dynamic SQL string.
(I was using dynamic SQL as I need to iterate through a list of input filenames derived from another table. bulk insert doesn't allow the use of a variable for the input filename. So I had to programmatically generate each bulk insert statement and then EXEC() it.)

The bulk insert with the same input file works fine if run directly. BCP import of the same file is OK too.

If the column containing this sequence is NOT the last column of the input row, it works as expected.

As I have full control of the script that generates these files I could add a 4th dummy column containing just a =. This solved the issues with the bulk insert.

Obviously I still don't understand the root-cause of this. At least I have a workaround.

Microsoft states on MSDN numerous times that

"bulk insert of CSV files is not supported, but may work in some cases"

They NEVER say why or what the exact problem is...
Makes me wonder if I have run into a known problem...

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.