Unexpected end of file with bulk insert

In SQL server I am receiving a data file with blank lines at the end of it, I'm not sure if there is a way with a bulk insert to ignore those lines.  Adding at the last column to end with a /r/n only causes it to have an unexpected end of file error due to those blank lines.  I need if possible, a solution on how to either get rid of those blank lines with a relatively simple dos script or to make bulk insert ignore them.  Having the initial data file simply not include those lines is not an option.

I am using SQLserver2005
UnderSevenAsked:
Who is Participating?
 
YveauCommented:
Unfortunaltely BCP does not has an option to skip empty lines :-\
Try to run this command line batch to remove the empty lines:

@ECO OFF
For /F "tokens=* delims=" %%A in (%1) Do Echo %%A >> %2

Save it as CleanEmptyLines.bat or something ike that and then do:

CleanEmptyLines Original.txt New.txt

The New.txt will be without empty lines and will be suited for BCP.

Hope this helps ...
0
 
UnderSevenAuthor Commented:
I ended up using %G for my param, but it worked like a charm.  I thought I would have to write some C# code, but it didn't occur to me to use ECHO instead of FIND (as you know find places lines in your file and doesn't really work right anyway for this).  Also the for loop was great.  Thanks a lot for this response, it was just what the doctor ordered.
0
 
YveauCommented:
:-)
Glad I could be of any help !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.