question

GuillermoPerez-9757 avatar image
0 Votes"
GuillermoPerez-9757 asked GuillermoPerez-9757 commented

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)

195720-image.png


sql-server-transact-sql
image.png (87.5 KiB)
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered GuillermoPerez-9757 commented

You have the format file wrong. You have a text file, so all data types in the file should be SQLCHAR (ANSI code page) or SQLNCHAR (Unicode file). SQLDATETIME etc is only used when you have native types, that is binary data.

Note that if you have a Unicode file, you need to account for that with the separators.

I have an article on my web site entitled Using the bulk-load tools in SQL Server that can give you further guidance.

· 2
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuillermoPerez-9757 avatar image GuillermoPerez-9757 ·

Thank you @ErlandSommarskog but I'm not sure that I understood, you said... you have a text file... I have 2 text files, 1 config and 1 with data... which one ??... would you please explain a little more, sorry :-(

0 Votes 0 ·
GuillermoPerez-9757 avatar image GuillermoPerez-9757 GuillermoPerez-9757 ·

Thank you, I did change all types to SQLCHAR (on the format file) and it works now... damn Microsoft... not sure why all their docs are made not to understand them... !

0 Votes 0 ·