Bulk insert file with randomly inserted blank lines in it
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:
%100423
0008 0003 9 2090 5104673034 0 001 0 #92 0 3 0 0 0
%100423
0132 0001 9 2700 2072207883 0 001 0 #70 0 0 0 0
%100423
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
BULK INSERT SmartTime.dbo.st_AvayaLogs
FROM 'c:\source.txt' WITH
(FORMATFILE = 'c:\format.fmt')
3 rows of sample data:
%100423
0008 0003 9 2090 5104673034 0 001 0 #92 0 3 0 0 0
%100423
0132 0001 9 2700 2072207883 0 001 0 #70 0 0 0 0
%100423
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
Anthony Perkins
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.