Solved

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

Posted on 2008-10-06
5
5,181 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
  • 3
  • 2
5 Comments
 
LVL 42

Expert Comment

by:EugeneZ
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 42

Accepted Solution

by:
EugeneZ earned 125 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

828 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