SQL Server 2008 R2 import flat file failed


  • Hi, I am using SQL Server 2008 R2 to import a flat file and get the following error message.

    BULK INSERT dbo.Test 
          FROM 'C:\Tes.txt'
          WITH (FIELDTERMINATOR = ',',  Rowterminator = '\r\n')

    Well, the last column does not have any value I changed it to "NULL" and still get unexpected end of file error.

    Error Message:

    Msg 4832, Level 16, State 1, Line 1
    Bulk load: An unexpected end of file was encountered in the data file.
    Msg 7301, Level 16, State 2, Line 1
    Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

    Thanks in advance.


    Monday, July 08, 2013 10:23 PM


All replies

  • Best to use SSIS Import/Export Wizard:

    You can also try BULK INSERT with FORMAT file.

    Nonetheless, your flat file source should be as clean as possible.  You may also try to upload into a staging table first.

    Kalman Toth Database & OLAP Architect
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Monday, July 08, 2013 11:37 PM
  • Please correct your WITH parameter as below,

          FROM 'C:\Test.txt'
          WITH (FIELDTERMINATOR = ',',  Rowterminator = '\n')
    The file format which you have attached is a CSV file.

    Regards, RSingh

    Tuesday, July 09, 2013 3:32 AM
  • Thanks all for your response. @Kalman I want to do it using Bulk Insert and not SSIS. @RSingh I tried changing different field & row terminator combinations but I get the same error message. How do I create a format file?



    Tuesday, July 09, 2013 4:39 AM
  • This means that your input file in not in proper format i.e csv.

    My query will work for the below format,




    Regards, RSingh

    Tuesday, July 09, 2013 6:18 AM
  • Well, The actual source file will be a .txt file and sample data would be something like below.

    "N","4.1","20120808","BACF","BACF","04","#85L015557",,,,,,"3","2","1985","19850815","19850815","3F10308  071562 R","3F10107  010626 H","3F10308  070012 S02","F","H",,,"4.53",,,,,,,"0000","4000",,,,,,,,



    Tuesday, July 09, 2013 3:21 PM
  • This is most likely caused by the last row not having the row terminator.  Bulk insert requires every row to be the same.

    Tuesday, July 09, 2013 10:04 PM
  • Also, Bulk Insert does not handle the double quotes around strings.  I would suggest using SSIS, which will handle this properly.

    Tuesday, July 09, 2013 10:05 PM
  • Thanks Tom, I understand what you explain. But curious enough to know if a format file would solve this problem.



    Wednesday, July 10, 2013 4:31 AM
  • Hi ione721,

    As Tom suggested, the flat file columns should be in the same format. If you persist to use Bulk insert, we need to remove some commas so that the left commas can be used as a row separator.

    Allen Li
    TechNet Community Support

    Monday, July 15, 2013 8:41 AM