none
BULK INSERT error: Bulk load: An unexpected end of file was encountered in the data file.

    Question

  • Hi all,

    Bulk load: An unexpected end of file was encountered in the data file.

    I get this error because the last line of the data file from which the import is done is not complete.

    How can I ignore the last row?
    (I cannot specify LAST_ROW because I don't know how many lines are there)
    (I tried to put new lines at the end of the file but it still doesn't work)

    The options are:
    -> to delete the last line. How?
    -> to complete with column separators the line. I don't know which is the column separator (I would have to read the format file)

    Any better solution?

    Thanks!
    Thursday, March 08, 2007 10:41 PM

Answers

  • But if the file consists of 30 GB Data ....We will not be able to open the Text File right ??


    What is the source of this data file?

    Can SSIS be used to transfer from the source (not in .txt format) to destination?


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Sunday, February 13, 2011 11:04 AM
  • I just had the same error.

    I had created my data file my using Management Studio to save all rows to text file prior to using BULK INSERT to attempt load.

    After getting the error on import I checked the file and realised that I had not set nocount on so the file had "(840502 row(s) affected)" at bottom.

     

    If you have created your file the same way (your file is too big to open and check) then create it again with "SET NOCOUNT ON" at top of script

    Saturday, May 28, 2011 4:42 AM

All replies

  • I have the same problem...

    Is there nobody who can assist?

    I've searched high and wide with NO solution. The import works great if I use the "import & export" wizard but the same file crashes when I try a bulk insert from vb2008.

    Tuesday, July 06, 2010 10:34 AM
  •  (Ludgy) writes:

    I have the same problem...

    Is there nobody who can assist?

    I've searched high and wide with NO solution. The import works great if
    I use the "import & export" wizard but the same file crashes when I try
    a bulk insert from vb2008.


    First: start a new thread.

    When you this, please include the following in your post:

    1) The CREATE TABLE statement for your table.

    2) You BULK INSERT statement.

    3) Your format file if any.

    4) Sample data with four to five records of data. Please test that the
    sample data demonstrates the problem you are seeing.

    Generally: BULK INSERT is a very different animal than the Import/Export
    Wizard.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    Tuesday, July 06, 2010 8:42 PM
  • But if the file consists of 30 GB Data ....We will not be able to open the Text File right ??
    Tuesday, February 08, 2011 7:19 PM
  • I like to use openrowset for bulk imports and set maxerrors to appropriate number, if the loss of data is acceptable.  

     

     

    Tuesday, February 08, 2011 8:58 PM
  • > But if the file consists of 30 GB Data ....We will not be able to open the Text File right ??

    Certainly not in Notepad. Maybe there are some editor www.downloads.com which is specially target to handle monster files. But if you have a 30 GB file that does not bulk load properly, you are likely to have a problem.

    In any case, rather than posting to an old thread, I suggest that you start a new and describe your problem from the beginning.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, February 08, 2011 10:29 PM
  • But if the file consists of 30 GB Data ....We will not be able to open the Text File right ??


    What is the source of this data file?

    Can SSIS be used to transfer from the source (not in .txt format) to destination?


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Sunday, February 13, 2011 11:04 AM
  • I've googled and found this link http://www.vedit.com/text-editor.htm - may be it'll work (it has 30 days trial).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, February 13, 2011 3:39 PM
  • I just had the same error.

    I had created my data file my using Management Studio to save all rows to text file prior to using BULK INSERT to attempt load.

    After getting the error on import I checked the file and realised that I had not set nocount on so the file had "(840502 row(s) affected)" at bottom.

     

    If you have created your file the same way (your file is too big to open and check) then create it again with "SET NOCOUNT ON" at top of script

    Saturday, May 28, 2011 4:42 AM