I was told that a Bulk Insert would be much faster. All the examples I've seen have a FIELDTERMINATOR - I don't have any FIELDTERMINATORs in my file. It's a fixed-width flatfile. SQL Server BULK INSERT fixed length char data. My data file contains fixed length char data with no field terminators in each line. So, a full line will be 34 characters long. My problem is field 4 and field 5 may not be present for each row. I may have 21 characters long line or 28 characters long line in that file. There is no case that field 5 exists and field 4 not. Bulk insert with text qualifier in sql server I think I got too late to reply this post but a more detailed answer should be accepted at any time Wednesday, June 12, 2013 6:29 AM.

Jul 29, 2015

Fixed Length File Example

Fixed Length Record

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 xsi:type='CharFixed' LENGTH='6'/>
[Code] ....
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)'.