Post reply

BULK INSERT with multiple character ROWTERMINATOR split in source

  • May 30, 2014 at 1:49 am

    #293836

    There are some flat files we are trying to give to another organisation.

    Due to the nature of the data (lots of free-text fields, some funny characters, many, many carriage returns), the column terminator of ^C^ is used, and the row terminator of ^|^ is used.

    This, mostly, works fine, but I have come across one file where the row terminator is split across two lines.

    When the bulk insert process is run, the new line is read as another character, so the row terminator isn't recognised.

    I'm wondering if there is any way I can get BULK INSERT to ignore new line characters in the source?

    SQL SERVER 2008 R2

    MICROSOFT SERVER 2008

  • June 1, 2014 at 6:22 pm

    #1718095

    UPDATE

    The code used for this output is an Oracle Spool Command (don't ask, it's a long story).

    The linesize property in the Spool Command was set to 2000, where as it should have been 5000.

    This should mitigate the line delimiter being split over two lines, but it would be nice to have a solution to the original problem.

    Thanks

  • June 2, 2014 at 6:06 am

    #1718166

    pogla.the.grate (6/1/2014)

    UPDATE

    The code used for this output is an Oracle Spool Command (don't ask, it's a long story).

    The linesize property in the Spool Command was set to 2000, where as it should have been 5000.

    This should mitigate the line delimiter being split over two lines, but it would be nice to have a solution to the original problem.

    Thanks

    Actually, that would be the solution for the original problem. Regardless of system, most import systems simply won't tolerate stray characters within any multi-character delimiter.

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

  • June 2, 2014 at 6:12 am

    #1718168

    The only way I can think of to process data with split row delimiter is to BULK load the data as CLOB using OPENROWSET and then parsing the data.

    Without knowing volume this could be costly in performance.

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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