Importing Data from Excel to SQL Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Importing Data from Excel to SQL Table

Author

Topic

viol-8-r
Starting Member

4 Posts

Posted - 2011-12-08 : 14:15:07

UPDATE I
SET I.ITEMDESC = T.itemdesc
FROM iv00101 I
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\desc.xls;',
'SELECT itemnmbr, itemdesc
FROM [sheet1$]') T
ON I.itemnmbr = T.itemnmbr where I.ITEMNMBR = '01-03-0136'

This script runs fine on my Sandbox but produces an error when I use this on production box
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I have checked the driver in my ODBC setting and it does exists.

Any Help

viol-8-r

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-08 : 14:29:02

don't know the problem, but in any case, I wouldn't do that.

I would save the excel as a delimited file, bcp it in to a staging table..THEN I would use T-SQL to do whatever I need to do

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

viol-8-r
Starting Member

4 Posts

Posted - 2011-12-08 : 15:05:09

Thank you very much for the kind & quick reply but here is my dilemma

I have one of my user copied and pasted same item description to about 5,000 items which I need to correct. So, I have an excel file where I have item and its (correct) description.

My destination tabale has 50 columns and 20,000 items but I need to update only 5,000 items out of 20,000; and update only description and nothing else. What would you recommond?

viol-8-r

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-12-08 : 15:29:13

I'd recommend exactly what Brett said. Create a staging table. Save the excel sheet as a delimited file, BCP it to your new staging table and run the update from there.

BTW, the problem is likely that the provider doesn't exist on the production server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources