Post reply

Bulk Insert Issues

  • April 28, 2009 at 2:49 pm

    #208754

    Having issues with Bulk Insert. I have used the same bit of code for ages now to move data from a csv file to SQL. I have always used the ROWTERMINATOR command without issue. I recently got a new file and all that would import was the header record and NONE of the data rows. I finallay found a suggestion of replacing the with CHAR(10) as shown below. NOW all the data imports, eveyrtime witht he EXCEPTION of the LAST row of data. Everytime, every file lose the last record.

    Any suggestions would be appreciated.

    SELECT @vSQL='BULK INSERT dbo.Source FROM ' + ''''+@v_Path +''''

    + ' ' + 'WITH (FIELDTERMINATOR = '+''''+','+''''+' ,ROWTERMINATOR = '''+CHAR(10)+''')'

  • April 28, 2009 at 9:29 pm

    #985620

    Hi,

    use the SPACE(10) instead of the CHAR(10)

    SELECT @vSQL='BULK INSERT dbo.Source FROM ' + ''''+@v_Path +''''

    + ' ' + 'WITH (FIELDTERMINATOR = '+''''+','+''''+' ,ROWTERMINATOR = '''+space(10)+''')'

    ARUN SAS

  • April 29, 2009 at 8:27 am

    #985963

    Thanks for the suggestion of Space(10) but that did not work. When I did that, only the header row came in.

    With the help of one of my co-workers we tried something off-beat. We deleted the very last column from the table and tried the Bulk Insert again... amazingly it worked... all rows imported using the Char(10). In this case, I do not need the last column, but this was a one script "fits all" process I created. I will be using the exact same process for other clients, so even though it worked in this case, I may need that last column in a new client setup.

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

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