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

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

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
kinga02
Asked:
kinga02
  • 3
  • 2
1 Solution
 
Eugene ZCommented:
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
 
kinga02Author Commented:
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
 
kinga02Author Commented:
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
 
Eugene ZCommented:
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
 
kinga02Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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