Excel import/insert to existing SQL Server table

[dwfresh]

HI,
I am trying to import data from an excel spreadsheet into an existing SQL Server table. I have tried the import wizard with no luck. I then tried the BULK INSERT method as shown below:

BULK INSERT mytable FROM 'C:\mypath\test1.xls'
WITH (DATAFILETYPE = 'char')

the error I have received is:
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (Email).

My Excel file has the exact header names as the column names in my SQL table.
Here is the Design of my SQL table:

UserID int 4 (Identity Field)
Email varchar 50
FirstName varchar 50
LastName varchar 50
AddedDate datetime 8


The Header names on my Excel file are identical to SQL table, and the Datatypes match the SQL column data type.

can anyone help me!!

thanks!

Doug

 

[SQLSister]

First, are you sure that 50 characters is enough for the emails? MAybe the thing is failing becasue your data is longer than 50 characters.

 

[dwfresh]

I figured out part of my problem. The excel file had an empty USerID field because I thought the SQL table would insert the UserID automatically. I don't really have an UserID field or AddedDate field in the excel file. In my website, I use the follwing stored proc to insert each new record:

CREATE PROCEDURE sp_MLists_AddSubscriber
@Email varchar(255),
@FirstName varchar(30),
@LastName varchar(30),
@UserID int output
AS

INSERT INTO MLists_Users(Email, FirstName, LastName, AddedDate)
VALUES (@Email, @FirstName, @LastName, GETDATE())

SET @UserID=@@IDENTITY
RETURN 1
GO

So how can I use this stored proc while inserting the Excel file WITHOUT the UserID and AddedDate fields ??
make sense?

thanks!!
Doug