[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Unexpected end of file with bulk insert

Posted on 2007-10-06
Medium Priority
Last Modified: 2008-01-09
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
Question by:UnderSeven
  • 2
LVL 18

Accepted Solution

Yveau earned 1200 total points
ID: 20032619
Unfortunaltely BCP does not has an option to skip empty lines :-\
Try to run this command line batch to remove the empty lines:

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 ...

Author Comment

ID: 20033582
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.
LVL 18

Expert Comment

ID: 20033600
Glad I could be of any help !

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question