Bulk Insert Questions

I'm using Bulk Insert for the first time and have a question.

I'm getting an error message about a field being truncated:
Bulk insert data conversion error (truncation) for row 2, column 12
(Depleted)

The data type for the "Depleted" column is Char(1). Looking at the
file, there is indeed only one character in the column. I'm not sure
how to fix this, or what I can do about it. Any suggestions would be
appreciated.

Thanks!
Jennifer

The table:

CREATE TABLE [dbo].[parSalesDetailTemp] (
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[Before] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[After] [int] NOT NULL ,
[Promo] [money] NOT NULL ,
[PromoBefore] [money] NOT NULL ,
[ItemPrice] [money] NOT NULL ,
[PromoAfter] [money] NOT NULL ,
[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitNumber] [int] NOT NULL ,
[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON
[PRIMARY]
GO

The SQL:

BULK INSERT parSalesDetailTemp
FROM '\\wbhq.com\dfsdv\iDataInt\TLDFiles\Extract\SalesD tl.csv'
WITH (FIELDTERMINATOR =',')

The file contents (1st few rows):

8032753,37312006,0,1,0,0,0,4.39,0,"WB-ML",2,N
8032753,37312007,0,1,0,0,0,4.39,0,"WB-ML",2,N
8032753,37312008,0,2,0,0,0,.00,0,"ML-M-COK",2,NJennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

I'm using Bulk Insert for the first time and have a question.
>
I'm getting an error message about a field being truncated:
Bulk insert data conversion error (truncation) for row 2, column 12
(Depleted)
>
The data type for the "Depleted" column is Char(1). Looking at the
file, there is indeed only one character in the column. I'm not sure
how to fix this, or what I can do about it. Any suggestions would be
appreciated.

I was able to successfully insert the sample rows you posted.

I can think of two things:

1) There are trailing blanks.
2) The line terminator is not CR-LF, but only CR or only LF.

Since it was the second line that failed, the first seems more likely to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

bulk insert question? Another way?

hi guys,
I have a text file contains about 1 million rows, I tried to import to a
table but I received data conversion errors for some rows, however, the row
number is like 504500 for example, I want to troubleshoot this row but the
only way I know is to open the text file (300MB) which will take forever to
open on notepad, I'm not sure if there is an easy way to troubleshoot such
a problem. Another thing is, I set Batchsize=10000, so I guess if I
received errors for 20 rows, it would mean that 200000 rows (20 *10000)
didn't get inserted into table, correct?
ThanksKevin,
Get a copy of TextPad or one of the many shareware or freeware text editors
that can handle large files with ease.
Another alternative is to import the file into a one-column staging table on
your SQL Server, and then scrub the data before inserting it into the target
table.
Steve Kass
Drew University
kevin wrote:

>hi guys,
> I have a text file contains about 1 million rows, I tried to import to
a
>table but I received data conversion errors for some rows, however, the ro
w
>number is like 504500 for example, I want to troubleshoot this row but the
>only way I know is to open the text file (300MB) which will take forever to
>open on notepad, I'm not sure if there is an easy way to troubleshoot such
>a problem. Another thing is, I set Batchsize=10000, so I guess if I
>received errors for 20 rows, it would mean that 200000 rows (20 *10000)
>didn't get inserted into table, correct?
>Thanks
>
>
>

Bulk insert data conversion error (truncation) for row 1

Hi i have a problem with MS SQL server 2000 and hope to seek for some advise.

i have the following samples
aa|0|abcdefg|
b|0|abcdefg|

i used the bulk insert in the query analyser..

BULK INSERT adl_ntid from 'C:/abc.unl with
(

FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

but they prompt me

Bulk insert data conversion error (truncation) for row 1, column 3 (NAME).

when i open in notepad, there is a square icon seperating my data. and i think this is the reason for the unsucessful insertion into my database.

i had looked into UNICODE DATA. but could not solve the problem

hope i can get some helpMy first piece of advice would be to analyze the text file using a hex editor to try to determine what character is getting put there.

My first thought is that the file was saved on a Unix system and that you may have incompatibilities with the different style line breaks.|||hI
thnx for your help. i had tried my textfile using the hex editor and it shows that my rows terminator is a linefeed.
i can insert the data using DTS import export file but when i tried implmenting in query analyser it still prompt me the same error

BULK INSERT abc FROM 'C:/abc.unl'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR =' "+CHAR(10)+" '
)

BULK INSERT abc FROM 'C:/abc.unl'
WITH
(
FIELDTERMINATOR='|',
ROWTERMINATOR =' {LF}'
)

once again thnx for your help|||I overlooked the error message...What is the data type for the field that is giving the problem?|||Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 3 (name).

the data types for the field names i am using are vchar.
thnx!|||

Quote:

Originally Posted by girl

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

the data types for the field names i am using are vchar.
thnx!

What size? This is a truncation error. That typically means that the data you are trying to insert will not fit in the field you are putting it in: you may have a varchar(4) and be inserting 8 characters.|||when i insert with the help of DTS, my information went in successfully. i used between CHAR with length 50 and VARCHAR length 50 and it prompt this same truncation error.|||

Quote:

Originally Posted by girl

when i insert with the help of DTS, my information went in successfully. i used between CHAR with length 50 and VARCHAR length 50 and it prompt this same truncation error.

What settings have you enabled for you DTS package? Perhaps you could run the SQL Profiler and see what the DTS package is calling for the INSERT?

BULK INSERT

Hi,

Is there anyway to see the insert statements that are generated by a BULK INSERT statement?
I am getting an error

Bulk load data conversion error (truncation)

But the data is fine it is all characters and I checked it in a hex editor and see no issues.
Thus I want to see the actual inserts to see what it is trying to do?

Thanks in advance!

You could use OPENROWSET function with BULK options. And you could see and check loaded data before inserting:

Code Snippet

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

|||

You could also add the ERRORFILE = 'file_name' parameter in order to see which rows it's complaining about, and then take it from there.

/Kenneth