Post reply

Bulk Insert and row terminators

  • February 20, 2012 at 12:09 pm

    #253325

    Hi, so I have a directory of hundreds of text files which I am using bulk insert to bring into SQL. I am running across a catch however, some of the files have row terminators of CR/LF and others just the LF. My question comes down to this, is there a way to determine which of these two row terminators are being used in a file automatically?

    The process now gets the list of files needing importing, and via a loop bulk inserts each file. If there is a code way to determine what the row terminator was, I could direct the code to use the correct bulk insert for the particular file.

    Unfortunately, the source of these files is inconsistant between the files, even those of the same type of files, or I would just specify the files directly as LF vs CR/LF

  • February 20, 2012 at 9:13 pm

    #1449467

    If you can read in a large enough sample of text you might use the following to determine how a line is terminated.

    DECLARE @NewLineChar AS CHAR(2)

    SET @NewLineChar = CHAR(13) + CHAR(10) --carriage return & linefeed

    DECLARE @D AS VARCHAR(100)

    SET @D ='SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL'

    SELECT PATINDEX('%'+@NewLineChar+'%',@D) AS 'Location of line feed carraige return'

    PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )

    GO

    DECLARE @NewLineChar AS CHAR(1)

    SET @NewLineChar = CHAR(10) --line feed

    DECLARE @D AS VARCHAR(100)

    SET @D ='SELECT FirstLine AS FL plus more' +@NewLineChar + 'SELECT SecondLine AS SL'

    SELECT PATINDEX('%'+@NewLineChar+'%',@D) AS 'Location of line feed'

    GO

    Result:

    Location of line feed carraige return

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

    24

    Location of line feed

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

    33

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 21, 2012 at 8:28 am

    #1449743

    Thanks Bitbucket, I will have to give that a try in addition to the non-linear thinking way I am testing now.

    (Which is, import all files with LF as the row terminator, and strip out the potential CR in what would be the last data field.)

    This would be so much simpler if the vendor could just pick a format!

  • March 8, 2012 at 6:36 pm

    #1456912

    You may use LF for BULK INSERT and immediately after it's completed strip the last character from the last column there it is CHAR(13).

    _____________
    Code for TallyGenerator

  • March 9, 2012 at 12:08 pm

    #1457340

    Thanks Sergiy , that ended up being what I did, and it worked smoothly.

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

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