Import Data from Excel to SQL 2005 table

Hi Experts,

Based on the examples I have got through EE website, I am importing Data from Excel to SQL 2005 table. My data in excel sheet is like folowing and getting an error (please see the code part). Please let me know how to fix the error.

Row1
 A-100001-GA-00       7/5/06      25845      0      0      1605.00
1517.00      0.00      (88.00)      (88.00)

Row2
A-100021-GA-00            7/5/07      25847      0      0
1232.00      8484.00      0      (32.00)      (32.00)

--Code
CREATE TABLE #Temp (pnumber Varchar(150),pEffdate smalldatetime,ID int,NBPR float,NBPR1 float,
ENDPR float, ENDPR1 float, diff1 float,diff2 float,FinalDiff float)
 
BULK INSERT #Temp
   FROM 'C:\MyReports\book1.xls'
 WITH (ROWTERMINATOR = '\r')
 
--Error
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 2 (pEffdate).
 
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 2 (pEffdate).
 
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 1 (pnumber).

Select allOpen in new window