My favorite way to access Excel files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gaby Abed » My favorite way to access Excel files

15 posts, Page 2 of 2

««1 2

My favorite way to access Excel files

Rate Topic

Display Mode

Topic Options

Author

Message

gsc_dba

Posted Wednesday, May 20, 2009 5:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:48 AM
Points: 1,065, Visits: 1,328

(5/20/2009)

Please read this MS article: http://support.microsoft.com/kb/814398

I experienced this same error and the article helped solve the problem.

I also found this useful when querying a workbook with several worksheets:

EXECUTE SP_TABLES_EX 'your_linked_servername_goes_here'

which returns the names of the worksheets

^{_____________________________________________________________________________________}
gsc_dba

Post #720450

malleswarareddy_m

Posted Tuesday, October 06, 2009 4:26 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 1,852, Visits: 986

Hi,
I tested this but it is not worked for me.

when i use openrowset it showed executed.[color=#00ffff][/color]
but when i used opendatasource it shows an exception linked server is null.
and also even though i created linked server through Managementstudio.it created linked server
but does not show the table in it.
In my system IIS was not installed .is there is link between iis and linked server.Please send the details reply.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Post #798409

warrensb

Posted Thursday, December 31, 2009 1:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 11, 2012 8:59 PM
Points: 88, Visits: 33

Gaby, Jacob

We have used the ACE provider with Excel 2007 and Excel 2010 Beta files and itworks great with a couple of issues.

1 - no 64 bit as discussed previously in this post and

2 - if you convert the file back to an excel 2003 version and use the jet provider - it is about twice as fast.... not sure why this is but the ace driver is slow.

Post #840711

warrensb

Posted Thursday, December 31, 2009 1:50 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 11, 2012 8:59 PM
Points: 88, Visits: 33

one other thing that I forgot to mention - you need to install either Excel 2007 on the server that you are executing on or download the AccessDatabaseEngine driver from

http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

also remember that when you run these queries via Management studio, the source files must be on server themselves.

you cannot connect to a local instance of SQL and execute against files not on your local machine.

Post #840712

rmechaber

Posted Tuesday, March 22, 2011 6:58 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

I use OPENDATASOURCE, too. In addition to the gotchas mentioned above (64-bit, file must reside on server), you might also want to know:

- The filename must be hard-coded; you cannot pass a variable to it. You can use dynamic SQL as a work-around. Here's a write-up: http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

- This might be obvious, but you can't have the file open in Excel. This is annoying if you're massaging data in Excel and using SQL to import/QC the data.

Thanks,
Rich

Post #1081953

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions