Solved

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

Posted on 2008-10-06
5
5,131 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel VBA - open a DataValidation dropdown 6 22
Gantt chart 2 19
How toselect unique values 3 10
DATETIMEOFFSET feature 1 0
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now