Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import Excel 2007 (.xlsx) into SQL 2000 using OpenRowSet

Posted on 2008-10-06
5
Medium Priority
?
5,261 Views
Last Modified: 2012-05-05
How do I go about importing Excel 2007 (.xslx) data into SQL 2000?
We are able to import data from Excel 2003 into SQL 2000 using the following:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Excel2003.xls', 'SELECT * FROM [Sheet1$]')

But this does not work when the source file is Excel 2007.
Do I need to install a driver? I have tried installing AccessDatabaseEngine.exe from Microsoft and updating the OPENROWSET arguements accordingly, but to no avail.
0
Comment
Question by:kinga02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 22651789
What an error did you get?
can you import the Excel with DTS?
 
check:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',...
 
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1942419&SiteID=17 
 
0
 

Author Comment

by:kinga02
ID: 22657640
Here are the queries that I have tried executing and the respective errors returned....

QUERY:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Excel2003.xlsx', 'SELECT * FROM [Sheet1$]')


ERROR:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

QUERY:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=c:\Excel2003.xlsx',
'select * from Sheet1$')


ERROR:
Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB provider 'Microsoft.ACE.OLEDB.12.0'.
OLE DB error trace [Non-interface error: Provider not registered.].

0
 

Author Comment

by:kinga02
ID: 22657659
I'm not sure if this is of any relevance....I do not have Office 2007 installed on the machine that I am trying to run the query on.
Surely this doesn't matter as it is using ODBC to access the data in the spreadsheet?
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 375 total points
ID: 22657881
you need to install this package:
 
 2007 Office System Driver: Data Connectivity Components  
 http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en  
 

0
 

Author Comment

by:kinga02
ID: 22658024
Hmmm, I tried installing AccessDatabaseEngine.exe yesterday and it didn't work.
So I tried again, this time I  closed down all apps, removed AccessDatabaseEngine.exe and then re-installed and hey presto - it works.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question