Post reply

Bulk Insert - Row Terminator Problem?

  • April 20, 2004 at 3:35 am

    #107965

    My file contains the following first few lines:

    01/02/2004, 19/02/2004

    O2O ,BEI ,I,ABLU,ONMLRX79030 ,1,PEAK O2O ,BEI ,I,ABLU,ONMLRX79030 ,2,STANDARD

    I am using the following Stored Procedure:

    SET @sqlquery = 'BULK INSERT tblMyTable

    FROM ' + '''' + @Path + '''' + '

    WITH

    (

    FIELDTERMINATOR = '+ ''''+ ',' + '''' + ',

    FIRSTROW = 2,

    ROWTERMINATOR = ' + '''' + '\n' + '''' +

    ')'

    EXEC sp_ExecuteSQL @sqlquery

    I don't want to insert the first line and hence have set the FIRSTROW parameter to 2. This, however, will also ignore the second line as well. If I set it to 1, it attempts the load the first line!

    Anyone know how to get it to start from row 2?

    Thanks

  • April 20, 2004 at 4:49 am

    #503458

    Just to shed some further light, I have put this file in a Hex editor and there is a valid line feed and carriage return after each line very confused now...

  • April 20, 2004 at 6:21 am

    #503466

    Richard,

    It's a bug and it's in BCP as well as bulk insert.  The only time the "First Row" option works correctly is when the rows to be ignored, the first row in your case, has exactly the same number of delimiters as the rest of the rows.  Either edit the first row so that it's in the same format or delete the first row.  Because I needed an automated process, I wrote a formless VB app to do this for me.

    One thing you can try is setting the "m" option (I think that's the right one) to allow a couple of errors and forget the "first row" setting.  That way, it'll mostly ignore the first row.

    --Jeff Moden

    --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

  • April 20, 2004 at 6:43 am

    #503469

    Thanks Jeff, I had a feeling it might be after trying everything... The problem I have is that the file is about 1GB, mine too is an automated process so if I do anything with it I might as well not use BI / bcp as it will involve iterating through the whole thing anyway... I will try your solution.

    Disappointing that such a bug exits, wish windows had equivalent functions such as Unix (head, tail etc)

     

     

  • April 21, 2004 at 12:56 am

    #503590

    "wish windows had equivalent functions such as Unix (head, tail etc)" - try MKS Toolkit. I am sure there are other products that do the same.

    Ideally windows should have these functions built in.

  • April 21, 2004 at 4:41 am

    #503614

    Looked at that, quite impressive, $5000 price tag a bit steep

    As this was an automated process I used ADO in the end to extract the first row and left bulk insert as was with no significant drop in processing time.

    Only problem I might get is if they fix the bug!

    Thanks for all the help

     

     

     

     

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

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