Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1093
  • 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.

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

0
Tpaul_10
Asked:
Tpaul_10
2 Solutions
 
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
 
HoggZillaCommented:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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