We help IT Professionals succeed at work.

Bulk insert file with randomly inserted blank lines in it

rerard asked
I am using the following command to import data from a flat file:

BULK INSERT SmartTime.dbo.st_AvayaLogs
FROM 'c:\source.txt' WITH
(FORMATFILE = 'c:\format.fmt')

3 rows of sample data:

0008 0003 9                      2090 5104673034                            0 001     0     #92  0   3     0 0 0                  

0132 0001 9                      2700 2072207883                            0 001     0     #70  0         0 0 0                  
0150 0018 7    9  #70     07930261094       8900                            5     007 4          0         0 1 0                  

The format file is attached below.  The data is spread over 2 lines, that is no problem, but the problem is sometimes there is a blank line between two rows and sometimes there isn't, it appears random.  I used a program called "replace pioneer" to remove all the blank lines from my file, and it imports fine.  However I need this to be automated.  My question is, is there a way to tell the bulk insert command to ignore totally blank lines?   format.txt
Watch Question

Top Expert 2012

I suspect they are not blank lines but rather you have embedded Cr or CrLf or Lf characters.
You can do OPENROWSET and apply WHERE on the selected data before INSERT


That sounds promising, can you give me an example?  The only thing is every 2 or 3 (depending on the extra carriage return or blank line) rows in the file is a 1 row of data, so I'm not sure if what you are suggesting will work but I'm willing to try.  It might make more sense to just clean up the blank lines in a separate process before the import but I am not sure how to do that either, not in an automated way anyway.
I dediced to attack the problem a different way, I imported the data into a temp table with one field, one row per line.  Then I deleted the "NULL" rows and merged the odds/evens.