Bulk insert with format file (fixed width) - Unexpected end of file was detected

Question

BULK INSERT [Alldlyinventory] FROM 'C:\Users\Admin\Documents\2NobleEstates\DATA\Download\Output\test.txt' WITH (FORMATFILE = 'C:\SQL Data\FormatFiles\test.xml'); 

File format:

  <?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="1" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharFixed" LENGTH="7" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="3" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="4" xsi:type="CharFixed" LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="5" xsi:type="CharFixed" LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="DAY_NUMBER" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="2" NAME="LCBO_NO" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="LOCATION_NUMBER" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="4" NAME="LISTING_STATUS" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="5" NAME="QTY_ON_HAND" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT> 

But I get the following error on SQL Server 2014:

Msg 4832 Level 16 State 1 Line 1 Bulk Upload: An unexpected end to file was detected in the data file. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for the linked server "(null)" reported an error. The provider did not provide any error information. Msg 7330, Level 16, State 2, Line 1 Unable to get the row from the OLE DB provider "BULK" for linked server "(null)".

Import fixed width.

Txt example:

 2016032803170570371L 000000014 2016032803367430371L 000000013 2016032803403800371L 000000036 2016032804007540371L 000000015 

Answer 1

Looking at your sample text file, it looks like you have a line terminator that is carriage return ( {CR} ) + linefeed ( {LF} ).

You can verify this by opening a text file with a text editor that can display special characters. I can recommend Notepad ++ , which is free and useful for this purpose (Menu View>Show Symbol>Show All Characters ).

If the string terminator is really {CR}{LF} , you should use xsi:type="CharTerm" with the TERMINATOR="\r\n" attribute for the last <FIELD> in the <RECORD> element:

 <RECORD> ... <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> 

---

You can find more information about importing a fixed field at the following link: XML format files (SQL Server) # Importing fixed or fixed width fields