Search code examples
sql-servert-sqlbulkinsertbcpsql-server-2008r2-express

Simple SQL Bulk Insert not working

I'm trying to create a simple Bulk Insert command to import a fixed width text file into a table. Once I have this working I'll then expand on it to get my more complex import working.

I'm currently receiving the error...

Msg 4866, Level 16, State 7, Line 1
The bulk load failed. 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.

Obviously I have checked the terminator in the file. For test data I just typed a 3 line text file in Notepad. At this stage I'm just trying to import one column per line. I have padded the first two lines so each one is 18 characters long.

Test.txt

This is line one  
This is line two  
This is line three

When I view the file in Notepad++ and turn on all characaters I see CRLF on the end of each line and no blank lines at the end of the file.

This is the SQL I'm using:

USE [Strata]
GO
drop table VJR_Bulk_Staging

Create Table [dbo].[VJR_Bulk_Staging](
[rowid] int Identity(1,1) Primary Key,
[raw] [varchar](18) not null)

GO
Bulk Insert [VJR_Bulk_Staging] 
From 'c:\temp\aba\test.txt'
with (FormatFile='c:\temp\aba\test2.xml')

Here is the format XML file. I have tried several variations. This one was created using the BCP command.

bcp strata.dbo.vjr_bulk_staging format nul -f test2.xml -x -n -T -S Server\Instance

This created a record and a row entry for my rowid column which I thought was a problem as that is an identity field, so I removed it.

<?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="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="18" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="raw" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

I'm testing on SQL Server 2008 R2 Express.

Any ideas where I'm going wrong?

Solution

  • This works.

    Option 1: Non-XML Format File

    9.0  
1
1 SQLCHAR 0 18 "\r\n" 2 raw SQL_Latin1_General_CP1_CI_AS

    or simply

    9.0  
1
1 SQLCHAR "" "" "\r\n" 2 "" ""

    Option 2: XML Format File

    Ugly as hell work-around

    <?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="CharTerm" TERMINATOR="\r" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" xsi:type="SQLINT"/>
  <COLUMN SOURCE="1" xsi:type="SQLCHAR"/>  
 </ROW>
</BCPFORMAT>

    P.s.
    It seems to me that there is a bug in the design of the XML format file.
    Unlike the Non-XML format file, there is no option to indicate the position of the loaded column (and the names are just for the clarity of the scripts, they have no real meanning).
    The XML example in the documentation does not work
    Use a Format File to Skip a Table Column (SQL Server)