Post reply

Bulk Insert Using Openrowset

  • September 13, 2007 at 5:25 am

    #177469

    I have a text file with 4 columns (semi-colon seperated) and variable number of rows. I have managed to import the data IF the data has consistency, however the natural txt output file is like this

    col1;col2;col3;col4

    col1;col2;col3;col4

    col1;col2

    col1;col2

    col1;col2;col3;col4

    If I add 2 semi-colons at the end of lines 3 and 4 , I can import ok using an INSERT and OPENROWSET with xml FORMATFILE.

    My question is , how can i still import the data without altering the original format because at the moment it errors on col3 unless I add the 2 semi-colons. Any suggestions would be helpful

    thanks

  • September 13, 2007 at 5:54 am

    #733441

    My first step would be to talk to the vendor providing the data and get them to provide the proper format of having the same number of semicolons on every line.  Would probably make your life a lot easier if the followed every column, including the last one, with semicolons.

    If that doesn't work, you could always import the data into a wide column table and split it there.  That, of course, will be a lot slower.

    Of course, modifying the original file (which you said you don't want to do) might be faster than either option above.  Maybe a nice little VBS script or something similar.

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

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

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