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)

    screenshot

    Attachments:
    You must be logged in to view attached files.
  • 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.

    • This reply was modified 2 weeks, 4 days ago by  homebrew01.
    • This reply was modified 2 weeks, 4 days ago by  homebrew01.
    • This reply was modified 2 weeks, 4 days ago by  homebrew01.
  • 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