Bulk Insert / (type mismatch or invalid character for the specified codepage)
-
Dear friends, I need your help and expertise. I'm trying to import several log files into a table but after I created a configuration file, I can't import a single record, because for some reason this doesn't understand my datetimes (2 values) saying that something is incorrect, an invalid character, I tried to remove the time, format the date sql like... nothing seems to work... in the following image, I have included my table structure, the 1 record data, the configuration file, my bulk insert query and my ugly result... my version of sql is 2008 (not R2)
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Hello sipi41
Sorry you have had to wait for your question to be bumped, and hopefully you are still interested in getting an answer!
Bulk insert goes like a rocket, if you can get it to work, so it may be worth getting your head around its idiosyncrasies. For a clear and full discussion, see Using the Bulk-Load Tools in SQL Server (sommarskog.se) .
The first change I would try is to make ALL the fields in your format file SQLCHAR. Forget about SQLINT and SQLDATE.
Good success.
MarkD
- Mark Dalley wrote:
The first change I would try is to make ALL the fields in your format file SQLCHAR. Forget about SQLINT and SQLDATE.
Agreed. I often load into a temporary table with all fields defined as VARCHAR(50) or bigger as needed. So I would have MarketingCallLog_TEMP with no constraints and all VARCHAR fields
Then I can better find oddities in the data, and correct or omit them as I import from the temp table into the live table.
-
Thank you guys... yes, it did work when changing all of these to SQLCHAR, thank you all!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply