Bulk Insert Truncation

[seekwall]

I have a text file in a fixed field format. Out of the 400 or so fields within the table, I need to provide only 4.

The first 3 fields make up a unique identifer for each record.

In order to do this, I have worked out the byte position for each field needed within the text file, and have created the following table

create table build_master
(
house_number char(12),
surname_number char(6),
person_number char(3),
filler varchar(1154),
product_photo char(1),
filler2 varchar(200)
)

As you can see, I have used 'fillers' to make up the rest of the data, to save specifing each field name in turn ( product_photo is the fourth field needed )

From this, I need to insert the text file, so have opted to use the 'Bulk Insert' function, as follows:

bulk insert <database>.dbo.build_master
from 'd:\<directory>\mast1.txt'
with
(
fieldterminator = ' ',
rowterminator = '|\n',
rows_per_batch = 50000
)

However, I keep getting this error message:

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 1 (house_number).

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

Can anyone advise me as to where I'm going wrong ?

 

[carrr]

Do you have &quot; 's around your data? If so, this can jam up the process when you use bulk insert via the QA as the &quot;'s get seen as characters. You can avoid the hassle by using Enterprise Manager and importing data into your table via DTS. DTS allows you to accomodate for the data format more readily.

 

[tlbroadbent]

I'd also recommend using DTS. However, you can use Bulk Insert and not import the unneeded data. Create a FMT file that defines the columns in the fixed length text file and how they map to the table columns. See BCP in SQL Books Online (BOL) for information about format files. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.