Bulk Insert and Blank Lines

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

Answer 1

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/

Answer 2

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)

Answer 3

Thank you, this is what I've done.

---

Sean