none
Bulk Insert and Blank Lines RRS feed

  • Question

  • Hi,

    I've an automated import that uses Bulk Insert to insert a csv file into a table.

    Occasionally the file I receive has blank lines at the end which causes the  errorBulk load: An unexpected end of file was encountered in the data file.

    Before I use something other than bulk insert to import the file or trim the spaces of the end, has anyone any other suggestions.

    I assume that bulk insert can't handle this situation itself ?

    thanks in advance.


    Sean
    Thursday, April 15, 2010 4:57 PM

Answers

  • Hi Sean,

    Unfortunatley there is not way to determine this.  The bulk insert process has to read the entire flat file and process accordingly and cannot make judgements about whether or not a blank line should be there or not.  It sounds like the person responsible for creating the flat file is doing it manually, which explains inconsistencies.  Have the person use an automated and more reliable means to generate the file.  Unfortunately, the alternative is to import the file and scrub it.  After which you can process it yourself or output it and reimport the file.

    The best solution is to make sure the file is consistent. 


    http://jahaines.blogspot.com/
    • Marked as answer by Sean2000 Friday, April 16, 2010 6:14 PM
    Thursday, April 15, 2010 6:16 PM
    Moderator
  • Hi Sean,

     I one way you can do is,

    1. Create a temp table with a single nvarchar(max) column.

    2. By using "BULK INSERT" statement insert all the records into the temp table.

    3. Delete all records which are empty or NULL from temp table.

    4. After confirming that all empty/NULL records are successfully deleted, export the Temp table data.

    5. Again re-import the newly generated file into your actual table.

    This way, you can remove empty lines(records) from your file.


    Kiran (www.ggktech.com)
    • Marked as answer by Sean2000 Friday, April 16, 2010 6:12 PM
    Thursday, April 15, 2010 7:07 PM

All replies

  • Hi Sean,

    Unfortunatley there is not way to determine this.  The bulk insert process has to read the entire flat file and process accordingly and cannot make judgements about whether or not a blank line should be there or not.  It sounds like the person responsible for creating the flat file is doing it manually, which explains inconsistencies.  Have the person use an automated and more reliable means to generate the file.  Unfortunately, the alternative is to import the file and scrub it.  After which you can process it yourself or output it and reimport the file.

    The best solution is to make sure the file is consistent. 


    http://jahaines.blogspot.com/
    • Marked as answer by Sean2000 Friday, April 16, 2010 6:14 PM
    Thursday, April 15, 2010 6:16 PM
    Moderator
  • Hi Sean,

     I one way you can do is,

    1. Create a temp table with a single nvarchar(max) column.

    2. By using "BULK INSERT" statement insert all the records into the temp table.

    3. Delete all records which are empty or NULL from temp table.

    4. After confirming that all empty/NULL records are successfully deleted, export the Temp table data.

    5. Again re-import the newly generated file into your actual table.

    This way, you can remove empty lines(records) from your file.


    Kiran (www.ggktech.com)
    • Marked as answer by Sean2000 Friday, April 16, 2010 6:12 PM
    Thursday, April 15, 2010 7:07 PM
  • Thank you, this is what I've done.
    Sean
    Friday, April 16, 2010 6:13 PM