Bulk insert data conversion error

[ndevriendt]

Hello,

I want to import a excel file into a table in SQL Server.
For these action I use the bulk insert command

My Excel file looks as follows (2 columns):

AAAAAA 105
ABADHE 106

I use the following code in SQL Server:

if exists (select * from sysobjects where id = object_id
(N'[dbo].[tmpAllocation2]')) drop table tmpAllocation2

CREATE TABLE [dbo].[tmpAllocation2] ( [Scoot_id] [char] (14) NOT NULL , [Sales_Code] [int] NOT NULL
) ON [PRIMARY]



set nocount on
declare @filename as varchar(250)
set @filename = '\\NDEVRIENDT\D$\Nico Devriendt\Excel
Documents\book1.xls'
execute ('BULK INSERT tmpAllocation2 FROM "' + @FileName
+ '"')


When I run this I get always the following error:

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

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 somebody help me ?

Thanks for your time and answer

Devriendt Nico

 

[arrowhouse]

I normally use csv files not excel & bcp not bulk insert - but the principles are similar.

You have not given any field/row terminators in your statement I think the statement needs a WITH FIELDTERMINATOR = '<whatever char your using>'
ROWTERMINATOR = '<whatever char your using>'

to match what an excel file uses (or file save as csv in excel)

The default is \t (tab) for the field & \n (newline) for the row.

See books on line for more details.