• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1096
  • Last Modified:

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.

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

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

CREATE TABLE #Temp (pnumber Varchar(150),pEffdate smalldatetime,ID int,NBPR float,NBPR1 float,
ENDPR float, ENDPR1 float, diff1 float,diff2 float,FinalDiff float)
   FROM 'C:\MyReports\book1.xls'
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

2 Solutions
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.
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
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.

EE Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now