Post reply

BULK INSERT

  • June 18, 2009 at 9:52 am

    #133017

    I am using SQLServer 2005. I am trying to import a pipe delimited text file using BULK INSERT, my script is;

    BULK INSERT mCommunicationHistoryAIP1

    FROM 'I:\outhistory\RBShistory\1_RBS06_CH_M_09_P.psv'

    WITH

    (FIELDTERMINATOR ='|',ROWTERMINATOR = '\r')

    The text file has '|' delimted fields and I believe a line feed row terminator. The original file was created in a unix environment and has now been copied over to the SQLServer server. I have checked the HEX code for the text file and confirmed the existence of 0A as the row terminator.

    The problem is that the insert only populates a single row and continues to squeeze the next line of data into the last field row after row untill the field max of 8000 is met.

    The statement does not error and returns '(1 row(s) affected)'

    What am I doing wrong ?

  • June 18, 2009 at 10:02 am

    #1011890

    ASCII '0A' is '\ n' not '\r', try changing the ROWTERMINATOR

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 18, 2009 at 10:22 am

    #1011914

    Thank you Mark, but unfortunately even with this change the same result is occuring 🙁

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

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