Dear Group:
I have a text file I am trying to use BULK INSERT to import the data but getting an error about data conversion.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Cat_ID). I verified the cause of this. The text file has all values in double quotes as such: "4207","36","160957" so the error is being caused because the Cat_ID value in the text file is "4207" instead of 4207. If I remove the double quotes, I can get past this error, but how do I tell the BULK INSERT command to ignore them?
Please note: I do understand the easiest thing would be to get the file without the double quotes, but that isn't as easy as it sounds (Corporate Politics) so if there is a way to do it without having the file rewritten, that would be great.
Would appreciate any help to get around this error.
January 28, 2021 at 2:43 pm
Note: Your example shows double quotes, not parentheses.
FIELDTERMINATOR=‘","’,
January 28, 2021 at 3:37 pm
My apologies. I corrected the original question to say "double quotes". Not sure why I did that, but definitely added to some confusion. As to the second, unfortunately using field terminator as you suggested doesn't work. I get the same error. I believe because although "," would be a field separator, there would still be leading and trailing double quotes.
"4207","36","160957"
Would still be
"4207,36,160957"
I will look into the link you posted about a format file. Appreciate the help
They have added a new parameter for BULK INSERT named FIELDQUOTE. Add that to your command and specify the double-quote as the field quote and it should get you past this error.
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 28, 2021 at 7:07 pm
Jeff:
My apologies. I noticed that previously and attempted to use it, but it gives me an error about "Incorrect syntax near 'FIELDQUOTE'". As such, I do not believe our version of SQL Server supports this 🙁
I thought we had SQL Server 2017 that would have allowed this, but it appears we do not. It is only 2016 SP1
January 28, 2021 at 7:46 pm
Note: Your example shows double quotes, not parentheses.
FIELDTERMINATOR=‘","’,
My apologies. I corrected the original question to say "double quotes". Not sure why I did that, but definitely added to some confusion. As to the second, unfortunately using field terminator as you suggested doesn't work. I get the same error. I believe because although "," would be a field separator, there would still be leading and trailing double quotes.
"4207","36","160957"
Would still be
"4207,36,160957"
I will look into the link you posted about a format file. Appreciate the help
Not needed here. You're using SQL Server 2017, which has a new bit of functionality to explicitly handle true CSV files (per RFC 4180 ), which your file qualifies as. Lookup BULK INSERT for 2017 and above and search the documentation for CSV.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply