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).

Open in new window

Tpaul_10Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dro_lawCommented:
Sounds like the data in the spreadsheet doesn't match up with the data type of small date time and varchar. Look at the peffdate and pnumber columns in the spreadsheet using a data filter (that will show you unique values quickly) and see if you spot anything unusual.

Have you tried using SSIS instead? I believe it comes free with SQL server standard edition and it allows you to easily convert values before you insert them.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve HoggITCommented:
First, use SSIS - no doubts. You cannot import an Excel file using Bulk Insert, only csv files. But, if you have to use the Bulk Insert and you convert to csv then don't try to import directly into a specific data type column, bring it allin as either NTEXT or  NVARCHAR(255). Get it into the db and then manipulate into your correct data types.
Here is some examples you might find useful if you cant use SSIS and it has to stay an .xls file. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
 
0
Mark WillsTopic AdvisorCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Split: HoggZilla {http:#22901265} & dro_law {http:#22901265}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

mark_wills
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.