Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-06
5
Medium Priority
?
5,290 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 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

963 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