We help IT Professionals succeed at work.
Get Started

Bulk insert file with randomly inserted blank lines in it

rerard
rerard asked
on
1,136 Views
Last Modified: 2012-05-09
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
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE