Post reply

Bulk Insert

  • October 10, 2007 at 11:07 pm

    #178836

    Hi

    I am using bulk insert to upload .csv file to sql server.

    The first 3 rows of my file contain different format than the

    rest of the file.I am using FIRSTROW to skip 3 rows,but it is giving me an error :

    [Could not bulk insert because column too long in data file. Make sure FieldTerminator and RowTerminator were specified correctly]

    My file looks like :

    STOCK EXCHANGE

    DAILY TRANSACTION

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

    itemcode,name,stock

    1,pen,10

    2,pencil,20

    3,eraser,30

    I think reason of error is that first 3 rows dont contain field terminator(which is comma) .

    Plz help me to skip first 3 rows

  • October 11, 2007 at 2:49 am

    #741099

    after copying your example to G:\bulk1.txt:

    BULK INSERT bulk1 FROM 'G:\bulk1.txt' WITH ( FIELDTERMINATOR = ',',

    ROWTERMINATOR = '\n', FIRSTROW = 2 )

    seems to work for me. Are you specifying the field terminator?

    Also, just for debugging purposes, if you manually remove the first three lines, do you get the same error?

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • October 11, 2007 at 4:36 am

    #741126

    Thanks for reply

  • October 12, 2007 at 10:28 pm

    #741776

    Hello Sir

    Thanks for your reply.

    I am using the same procedure as you mentioned,But no luck.

    I am getting error like this :

    [The line is too long for row 1 column 3.Verify that field terminator and row terminator are specified correctly]

    May I know ur email id, so that I can mail my sample .csv file to you.

    Plz help

    Regards

    Jeet

  • October 13, 2007 at 10:56 am

    #741799

    Could be any of a dozen different problems... you need to post the code you're using and the CREATE statement for the table you are importing to.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 15, 2007 at 2:17 am

    #741896

    I've sent you my email address in a pm, but there are many experienced people looking at these forums, so instead of email it would be nicer if you posted, as Jeff mentioned: the schema of the table, the exact statement you are executing for the bulk insert, and the first part of the file. This will not only let others answer your question, but the resolution will stay here for people who experience the same problem in the future.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • October 15, 2007 at 5:24 am

    #741928

    i would lean towards the data not being in the expected format....maybe it's a unix based file, and contains only \r for the rowterminator and not , which is Carriage Return Line feed.

    but we wont know without more details.

    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!

  • November 19, 2007 at 8:22 am

    #752308

    Could it be that you actually need to skip the first 4 rows? Looks like your 3rd row is actually the headers for the columns, and 'itemcode' & 'stock' probably wouldn't fit into columns expecting int. Just a thought.

    shirish_padekar (10/10/2007)

    Hi

    I am using bulk insert to upload .csv file to sql server.

    The first 3 rows of my file contain different format than the

    rest of the file.I am using FIRSTROW to skip 3 rows,but it is giving me an error :

    [Could not bulk insert because column too long in data file. Make sure FieldTerminator and RowTerminator were specified correctly]

    My file looks like :

    STOCK EXCHANGE

    DAILY TRANSACTION

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

    itemcode,name,stock

    1,pen,10

    2,pencil,20

    3,eraser,30

    I think reason of error is that first 3 rows dont contain field terminator(which is comma) .

    Plz help me to skip first 3 rows

  • November 19, 2007 at 5:12 pm

    #752489

    Even with that, it's still gonna blow up... the skipped rows have to have the same format as those that aren't. It's a fault in both Bulk Insert and BCP. I get around it by writting some VBS...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

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