Post reply

Bulk load data conversion error

  • October 1, 2010 at 8:33 am

    #224582

    Hi friends,

    I am trying to insert a bulk data into my database. I follow this steps,

    i attached my text file with this,

    my table is look like this...

    Column Name Type

    ===============================

    sr.no int (pk)

    id int

    name varchar(20)

    profile varchar(max)

    dob datetime

    picture image

    add varchar(20)

    city varchar(5)

    ----------------------------------------------------

    i am using this command for bulk insert into my table.

    BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')

    i am getting this error,

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 1 (sr.no).

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 1 (sr.no).

    Msg 4865, Level 16, State 1, Line 1

    Cannot bulk load because the maximum number of errors (10) was exceeded.

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    pls help me out by this problem.

    Thanks in Advance.

    M.I

    ________________________________________
    M.I.
    [font="Times New Roman"]

    Learning is a path with no destination...
    [/font]

  • October 1, 2010 at 1:58 pm

    #1230493

    Try using a format file. This will help you get started

    SELECT * FROM OPENROWSET (BULK 'C:\TxtFile1.txt', FORMATFILE = 'C:\TxtFile1.fmt') AS Z

    TxtFile1.fmt

    7.0

    9

    1 SQLCHAR 0 0 "," 1 col1

    2 SQLCHAR 0 0 ",\"" 2 col2

    3 SQLCHAR 0 0 "\",\"" 3 col3

    4 SQLCHAR 0 0 "\",\"" 4 col4

    5 SQLCHAR 0 0 "\",\"" 5 col5

    6 SQLCHAR 0 0 "\",\"" 6 col6

    7 SQLCHAR 0 0 "\",\"" 7 col7

    8 SQLCHAR 0 0 "\",\"" 8 col8

    9 SQLCHAR 0 0 "\"\r\n" 9 col9

Viewing 2 posts - 1 through 1 (of 1 total)

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