0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
David P
  • 411
  • 7
  • 21

3 Answers3

1

I think the problem is with your prefix being 2 bytes long:

xsi:type="CharPrefix" PREFIX_LENGTH="2"

From what you have posted you don't have a prefix in your data file. Set the PREFIX_LENGTH to 0 in your format file, or provide the proper prefix in your data file.

You can find more information about prefix datatypes and what the prefix is about in the documentation: Specify Prefix Length in Data Files by Using bcp (SQL Server).

I think what you really wanted is type CharTerm with a proper TERMINATOR (/r/n in your case).

TT.
  • 15,774
  • 6
  • 47
  • 88
  • It didn't like PREFIX_LENGTH="0" and returned this error. Msg 4858, Level 16, State 1, Line 1 Line 4 in format file "c:\temp\aba\test2.xml": bad value 0 for attribute "PREFIX_LENGTH". I only used this because it was what the bcp command generated. From your link I now see what PreFixLength is for and I certainly don't need it. My data file doesn't have this. Now that I think about it my file does have a fixed line length so even the CR LF is redundant. I just have to allow for it. I'll look at some of the other suggestions here. Thanks. – David P Nov 23 '16 at 23:00
  • @DavidPollard You're welcome =). `CharFixed` is another option: fixed length and no terminator. I thought already that `CharPrefix` didn't apply to what you had. Good luck! – TT. Nov 24 '16 at 07:12
0

Could you please try the following command and check if the BULK insert is happening.please note I have added the last line mentioning the delimiter.

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 ( FIELDTERMINATOR ='\t', ROWTERMINATOR ='\n',FIRSTROW=1 )
Santhoshkumar LM
  • 109
  • 2
  • 6
  • Thanks for your efforts. I tried the other solutions above of which one worked. Your suggestion produced errors. 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)". – David P Nov 23 '16 at 23:25
0

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)

Community
  • 1
  • 1
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • I tried the non xml format. aka "non ugly as hell" :) I found a good description of this file here https://msdn.microsoft.com/en-us/library/ms191479.aspx – David P Nov 23 '16 at 23:15
  • It came up with this odd error. Msg 4862, Level 16, State 1, Line 1 Cannot bulk load because the file "c:\temp\aba\format.fmt" could not be read. Operating system error code (null). even though Everyone has full permissions on the file. It turned out I needed a blank line on the end of the FMT file. Now I need to decide to use a staging table or try and import direct into my finished table. Thanks DUDU. Yours is the simplest answer. – David P Nov 23 '16 at 23:21