Unexpected end of file with bulk insert

Posted on 2007-10-06
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
    LVL 18

    Accepted Solution

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

    Author Comment

    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

    Glad I could be of any help !

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now