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

Format file:

 <?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 am getting the following error on SQL Server 2014:

Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

It's a fixed width import.

Sample txt:

2016032803170570371L 000000014
2016032803367430371L 000000013
2016032803403800371L 000000036
2016032804007540371L 000000015
TT.
  • 15,774
  • 6
  • 47
  • 88
Ben
  • 109
  • 2
  • 8
  • Let me state the obvious? **The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.** – TT. Mar 29 '16 at 17:57
  • Sorry, this is a fixed width bulk insert – Ben Mar 29 '16 at 18:51
  • Your txt file might be having blank at the end. Can you use the `ERRORFILE` and find the row that is causing the problem ? Also try using `ROWTERMINATOR = '0x0A'` (undocumented though) to see if it helps. – Kin Shah Mar 29 '16 at 19:00
  • Hi, there's a blank at the end. I got the error: Row 1269290 File Offset 38078670 ErrorFile Offset 0 - HRESULT 0x80004005. Please see sample data as well. – Ben Mar 29 '16 at 19:14

1 Answers1

7

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

You can inspect this by opening the text file with a text editor that can show special symbols. I can recommend Notepad++ which is free and good for this purpose (Menu View>Show Symbol>Show All Characters).

If the row terminator is indeed {CR}{LF}, you should use xsi:type="CharTerm" along with a 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 on fixed field import in the following link: XML Format Files (SQL Server) # Importing fixed-length or fixed-width fields

TT.
  • 15,774
  • 6
  • 47
  • 88
  • Hi, the data does end with {CR}{LF} but I am getting the following error: Attribute "ROWTERMINATOR" could not be specified for this type. After, I changed the ROWTERMINATOR to TERMINATOR and it worked. Thanks alot. I really appreciate this forum and the people here. – Ben Mar 31 '16 at 14:13
  • @Ben Ah yes you are correct, the correct attribute is `TERMINATOR` (answer updated). Glad it helped =). – TT. Mar 31 '16 at 14:27