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:
(I)(OI)(CI)(F) (I)(OI)(CI)(DENY)(WDAC,WO,S,DC) (I)(OI)(CI)(F)
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.)