?
Solved

Rowset  error importing Excel 2007 spreadsheet into SQL 2005

Posted on 2011-03-23
16
Medium Priority
?
2,393 Views
Last Modified: 2012-06-21
Having some trouble importing data from an Excel 2007 spreadsheet into SQL Server 2005 database - SQL 2005 db is at SP2 - using Import and Export Wizard in SSMS. As the Import and Export wizard only shows Excel 97-2005 under Excel Versions on Choose A Data Source, I eventually found the following guide on how to import an Excel 2007 spreadsheet into SQL 2005 :
http://www.sql-server-performance.com/articles/biz/How_to_Import_Data_From_Excel_2007_p1.aspx

So have followed the steps in that guide, involving :

 - specifying Microsoft Office 12.0 Access Database Engine OLE DB Provider as Data Source;
 - in Data Link Properties, supplying the full path of Excel 2007 spreadsheet in Data Source;
 - in Data Link Properties, supplying Excel 12.0 against Extended Properties;

Test Connection on Data Link Properties comes back 'Test connection succeeded'
Specified required SQL Server credentials on 'Choose a Destination' in SQL Server Import and Export Wizard, specifying SQL Native Client as destination.

Specificed 'copy data from one or more tables' on Specify Table Copy or Query;
On Select Source Tables and Views specify Sheet1$;
Preview shows data from spreadsheet OK;

But  the final step of the wizard gives an error :

Opening a rowset for Sheet1$ failed - check that the object exists in the database

On the 'How To Guide' link referred to earlier, the Comments section shows that a couple of other people have hit exactly the same problem, but no solution has been supplied.

So can anyone here help me resolve this, please ?

0
Comment
Question by:raymurphy
[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
  • 6
  • 5
  • 5
16 Comments
 
LVL 1

Expert Comment

by:stephenlecomptejr
ID: 35201516
Would it help to save it down as a comma delimited file with no formatting and then try importing or do you have to have it as an import of an Excel file?
0
 

Author Comment

by:raymurphy
ID: 35201951
Unfortunately the user requirement is that it needs to be imported as Excel file. As a fallback position I've established that we can save it as a csv file and import it that way, but users are insistent they need to import Excel 2007 file (as Excel 2007 overcomes Excel's previous limits of around 65,000 rows and increases possible no. of rows to over 1 million).
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35202390
I bet you don't have "Sheet1$" table in your target SQL database and you will need to map each xlsx sheet into a different table (eventualy you can create them before the import with same structure as your xlsx sheet) then use those intermediate tables to agregate it into permanent SQL tables.
0
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!

 
LVL 1

Expert Comment

by:stephenlecomptejr
ID: 35204355
0
 
LVL 1

Expert Comment

by:stephenlecomptejr
ID: 35204410
At this post - Kathleen found an answer to a similiar question:

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

I needed to change the DelayValidation property of the Import task to True as well as the one in the Connection Manager
0
 
LVL 1

Expert Comment

by:stephenlecomptejr
ID: 35204442
I think there was a similiar error at this page and it also stated to change the DelayValidation property of the Import task to True.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24257618.html

0
 

Author Comment

by:raymurphy
ID: 35206390
Thanks for the replies ...

lcohan - I shouldn't need a Sheet1$ table in the target database : as I'm using the Import and Export Wizard in SSMS, the 'Complete the Wizard' step specifically states that 'The new target table will be created'.

stephenlecomptejr - although those links look useful, they all seem to refer specifically to SSIS packages, whereas I'm attempting the import MANUALLY using the Import and Export Wizard in SSMS.

Some further background points which might help explain my scenario ..
The SQL Server box has the 2007 Office System Driver Data Coonectivity Components installed, so that box has the ACEODBC.DLL fiole which  provides the Microsoft Office 12.0 Access Database Engine OLE DB Provider.
That OLEDB Driver shows up in SSMS under Server Objects-->Linked Servers-->Providers as
Microsoft.ACE.OLEDB.12.0 .
On the SQL Server box, sp_configure has been used to enable Adhoc Distributed Queries.

As the error reported was  an 'Opening a rowset ' error I thought I would try an OPENROWSET query :

    SELECT *  
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\MyExcel2007File.xlsx',
    'SELECT * FROM [Sheet1$]');
But that fails with an error of

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

It's almost as though SQL Server doen't think this OLE DB provider is available,

One final point - the SQL Server box being used is actually an SQL Service Cluster, so I don't know if that has any significance ...    

0
 
LVL 40

Expert Comment

by:lcohan
ID: 35206759
I believe the statement should be like below considering the space you have in the 'Excel 12.0' sring and please replace \\server\folder with the actual strings:

SELECT *  
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '"Excel 12.0;HDR=YES";Database=\\server\folder\MyExcel2007File.xlsx',
    'SELECT * FROM [Sheet1$]');
0
 

Author Comment

by:raymurphy
ID: 35206875
No lucl, lcohan, just gave the same result of :

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35206996
Try add IMEX=1 as well and change the following settings besides ad hoc queries but if possible do not do it directly in a production server:

1.EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
2.GO
3.EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
4.GO
0
 

Author Comment

by:raymurphy
ID: 35207193
IMEX=1 makes no difference - still getting same error (AllIInProcess and DynamicParameters were already set anyway) ....
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35207578
Wow...is like OPENROWSET is looking for the linked server with that provider name...
Did you tried it with OPENDATASOURCE instead? Something like below:

--OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled.

SELECT * FROM OPENDATASOURCE
(
'Microsoft.ACE.OLEDB.12.0',
'Data Source=\\server\folder\file.xlsx;Extended Properties=''Excel 12.0;HDR=YES''')...[Sheet1$]

0
 

Author Comment

by:raymurphy
ID: 35207909
No joy with OPENDATASOURCE either - that just throws back the 'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0"' error .....

I'm just wondering whether the fact that the SQL Server box is an SQL Server Cluster has something to do with it. I'm not familiar with SQL Servwer Clustering, but I'm thinking that perhaps the action of installing 2007 Office System Driver Data Coonectivity Components (which makes the ACEODBC.DLL file and  Microsoft Office 12.0 Access Database Engine OLE DB Provider available) needs to be carried out on all machines in the cluster, so I'm going to investigate this with our DBA Team (unfortunately remote, and difficult to contact).

In the meantime, I've gone through this whole scenario on a standalone SQL Server 2005 box, and that works fine on that box - i.e. SQL Server 2005 can happily import the Excel 2007 spreadsheet, and the OPENROWSET queries also work OK.  

 
0
 
LVL 40

Accepted Solution

by:
lcohan earned 1000 total points
ID: 35208074
Wow - of course that the OLEDB driver must be installed on all nodes of the cluster and the file to exist on a drive\folder attached to the active node at the time - sorry I missed that to check when you mentioned above that this is on a sql cluster...
0
 
LVL 1

Assisted Solution

by:stephenlecomptejr
stephenlecomptejr earned 1000 total points
ID: 35208461
I think lcohan is correct in this manner and if it is installed on all nodes perhaps one needs to be reregistered according to this URL:
http://www.sqlservercentral.com/Forums/Topic764585-146-1.aspx

Ten Centuries, claims there is an Excel one that works above it but I think you've probably already tried that.  Unfortunately, I do not have SQL Server 2005 to try and attempt myself.

Here are some more guesses as to the cause of this on Microsoft's part that deals with SSMS instead of SSIS.

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/83361d17-ec93-4c4b-9d05-a39d53232f8a

Must be so frustrating to feel you are so close to solving this and to only hit a wall that shouldn't be there!
0
 

Author Comment

by:raymurphy
ID: 35229871
Have now contacted our DBA Team, and have asked them to check the clustering aspects of this.

I'll post a comment once they've done that, but in the meantime I'll split the points between stephenlecomptejr and lcohan as the comments from you both have been very helpful - so thanks to you both for your help ....
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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