none
Problem with bulk insert RRS feed

  • Question

  • Hey

    i got some SQL that is loading a deltafile every day to a table on a sqlserver.

    I use bulkinsert but i got one problem:

    at the deltafile (tekstfile) i have a [LF] at the end of the last line, witch gives me a blank line at the bottom.

    i use the followingen bulkinsert:

    DECLARE @sql AS VARCHAR(4000)
    SET @sql = 'BULK INSERT Data.dbo.Temp_indlaest FROM ''G:\SQLData\DELTA.csv'' WITH (CODEPAGE=''1252'', FIELDTERMINATOR=''|'', ROWTERMINATOR=''' + CHAR(10) + ''')'
    PRINT @sql
    EXECUTE (@sql)


    and i get this error:

    Server: Msg 4832, Level 16, State 1, Line 1
    Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
    The statement has been terminated.


    My plan was to use LASTRAW (filecount -1)', but i got no clue how to do that.

    If i manualy delete the last line in the file my bulk insert works just fine.

    hope someone can help.

    //Kasper

    Thursday, February 23, 2012 7:36 AM

Answers

  • Hi KasperLund,

    Regarding to your description, seems the source provider is expecting data when none was given or you've got an EOF character in the file that's causing issues. Ensure your data is clean and mapped appropriately.

    But there is one option for you that is to use the -L parameter of BCP to specify the last row, which will let you ignore the lines at the end that is not formatted correctly. However, you have to count the lines in the file and subtract the offending number of lines to specify the value. If this doesn't work for you then you will have to correct the data file before using it with BCP or BULK INSERT as I mentioned above.

    Meanwhile you can try to use SSIS, which provide an option or CLR stored procedure which opened the file and trimmed the final lines. For more information about SSIS, please link to SSIS forum for further help.

    Additional, please refer to this article about bcp Utility: http://msdn.microsoft.com/en-us/library/ms162802(v=sql.105).aspx

    Regards, Amber zhang

    Tuesday, February 28, 2012 8:14 AM
    Moderator

All replies

  • Hi,

    Please refer the link below,

    http://sinshith.wordpress.com/2010/04/12/bulkinsert-process/

    http://social.msdn.microsoft.com/Forums/zh/transactsql/thread/78b889e4-5f2f-4b30-b486-2b92e58e7e4a


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, February 23, 2012 7:47 AM
  • my problem is that the recordcount on the deltafile will change from day to day

    in your link they just refere to a static number like =

    declare @sql nvarchar(4000)
    set @sql =‘BULK INSERT Dest_table FROM ”\\Loc ”
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ”|”,
    ROWTERMINATOR = ”’ + nchar(10) + ”’ ,
    LASTROW=3355072
    )’
    select @sql

    exec(@sql)

    But i dont know how many lines my file got, and it wil change from day to day.

    Thursday, February 23, 2012 9:42 AM
  • Hi KasperLund,

    Regarding to your description, seems the source provider is expecting data when none was given or you've got an EOF character in the file that's causing issues. Ensure your data is clean and mapped appropriately.

    But there is one option for you that is to use the -L parameter of BCP to specify the last row, which will let you ignore the lines at the end that is not formatted correctly. However, you have to count the lines in the file and subtract the offending number of lines to specify the value. If this doesn't work for you then you will have to correct the data file before using it with BCP or BULK INSERT as I mentioned above.

    Meanwhile you can try to use SSIS, which provide an option or CLR stored procedure which opened the file and trimmed the final lines. For more information about SSIS, please link to SSIS forum for further help.

    Additional, please refer to this article about bcp Utility: http://msdn.microsoft.com/en-us/library/ms162802(v=sql.105).aspx

    Regards, Amber zhang

    Tuesday, February 28, 2012 8:14 AM
    Moderator