July 29, 2015 at 11:20 am
I wasn't sure where to put this topic so I put it here since I figured it is a question that would apply to virtually any version even though I am using SQL Server 2005.
We have a vendor that sends us a fixed width text file every day that needs to be imported to our database in 3 different tables. I am trying to import all of the data to a staging table and then plan on merging/inserting select data from the staging table to the 3 tables. The file has 77 columns of data and 20,000+ records. I created an XML format file which I sampled below:
<?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="RetNo" xsi:type="CharFixed" LENGTH="6"/>
<FIELD ID="TrName" xsi:type="CharFixed" LENGTH="30"/>
<FIELD ID="Add1" xsi:type="CharFixed" LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="RetNo" NAME="AGENT_NUMBER" xsi:type="SQLCHAR" LENGTH="6"/>
<COLUMN SOURCE="TrName" NAME="TRADE_NAME" xsi:type="SQLCHAR" LENGTH="30"/>
<COLUMN SOURCE="Add1" NAME="ACTUAL_NO_STREET" xsi:type="SQLCHAR" LENGTH="30"/>
<COLUMN SOURCE="Add2" NAME="ADDRESS2" xsi:type="SQLCHAR" LENGTH="30"/>
</ROW>
</BCPFORMAT>
The data file is a fixed width file with no column delimiters or row delimiters that I can tell. When I run the following insert statement I get the error below it.
BULK INSERT myStagingTable
FROM '\\...\...\...\myDataSource.txt'
WITH (
FORMATFILE = '\\...\...\...\myFormatFile.xml',
ERRORFILE = '\\...\...\...\errorlog.log'
);
Here is the error:
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)".
Any suggestions would be greatly appreciated. I would like to just ask the vendor for a different format but that would likely take too long.
August 3, 2015 at 8:56 am
You might well find that the data is not actually correctly formatted for all records. If a humang being was ever editing the file, you might well have problems such as a missing CR/LF at the end of a line because a human accidentally deleted it, or perhaps an extra one snuck in somehow. Try writing a VBScript to read the entire file and then use the Split function with vbCrLf as the string to split by, and then output all the resulting array values and see if you find anything wierd going on. You might end up needing a hex editor to find out that the file was initially created on a Unix system of some kind, and therefore, only has LF instead of CR/LF at the end of each line. In that case, you might need a utility to transform the file, or you might need to change the BCP format file.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply