none
SQL Server 2008 R2 import flat file failed

    Question

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


    Ione


    • Edited by ione721 Tuesday, July 09, 2013 3:19 PM typo
    Monday, July 08, 2013 10:23 PM

Answers

All replies

  • Best to use SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-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 sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


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

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

    Regards, RSingh


    • Edited by RSingh() Tuesday, July 09, 2013 3:33 AM add text
    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?

    Regards.............


    Ione

    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,

    "FieldName1","FieldName2","FieldName3"

    "Value1","Value2","Value3"

    "Value11","Value22","Value33"


    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",,,,,,,,

    Thanks...........


    Ione

    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
    Moderator
  • 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
    Moderator
  • Thanks Tom, I understand what you explain. But curious enough to know if a format file would solve this problem.

    Regards...


    Ione

    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
    Moderator