Solved

Rowset  error importing Excel 2007 spreadsheet into SQL 2005

Posted on 2011-03-23
16
2,328 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
  • 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 39

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
 
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 39

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 39

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 39

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 39

Accepted Solution

by:
lcohan earned 250 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 250 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 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

20 Experts available now in Live!

Get 1:1 Help Now