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