Post reply

Help with bulk insert

  • April 11, 2011 at 9:25 am

    #248697

    I'm trying to do a bulk insert from a csv file and one of the columns i'm trying to insert into is an integer field that allows nulls. My csv file contains NULL for that value, but it's still throwing the error 'Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 10 (VINGrossVehicleWeightRatingID)'.

    Here's my bulk insert statement:

    Bulk Insert IARTS..VINVehicleDescription

    From 'F:\VINVehicleDescriptionUpdate.csv'

    With

    (

    FirstRow = 1,

    FieldTerminator = ',',

    RowTerminator = '',

    KeepNulls

    )

    and here's the first row of data:

    541201,JA3AU26U*8,2008,367147,328948,100055,7,1,4,NULL,2943,15990,MITS,LAN,JA3AU26U*8*******,0,Mar 30 2011 8:31AM,VINALoader ,VINALoader,Mar 30 2011 8:31AM,100055,7

    Notice the NULL is in the row and I have specified "KeepNulls". What am I doing wrong here?

    Edit: BTW, my "RowTerminator" is backslash n for the end-of-line character.

  • April 11, 2011 at 9:30 am

    #1309909

    KEEPNULLS will keep any NULL value as in what would be in column C here ... "A,B,,D"

    But your data has a value, and that value is the word "NULL".

    I do believe, you'll either need to pull in your data as varchar, change NULL to 0 or actually set to NULL, or intercept/modify the input data before importing.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • April 11, 2011 at 9:31 am

    #1309910

    i think the issue is BULK INSERT cannot differentiate between a string between two commas and the keyword NULL;

    I'm not sure how to tackle that, other than putting it into a temporary table and importing from that based on a case statement.

    SomeValue,1,OtherValue --expected int value

    SomeValue,,OtherValue --would make the second value in the set NULL,

    SomeValue,Null,OtherValue --assumes it's a string

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 11, 2011 at 9:36 am

    #1309913

    Ah, didn't think about my value being the WORD null.

  • April 11, 2011 at 9:39 am

    #1309919

    can you pre-process the file by replacing Null with String.Empty? then you could continue using bulk insert

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply