[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Rowset  error importing Excel 2007 spreadsheet into SQL 2005

Posted on 2011-03-23
16
Medium Priority
?
2,414 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

649 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