SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gaby Abed / My favorite way to access Excel files / Latest Posts

RE: My favorite way to access Excel files

rmechaber — Tue, 22 Mar 2011 06:58:34 GMT

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: [url=http://www.sommarskog.se/dynamic_sql.html#OPENQUERY]http://www.sommarskog.se/dynamic_sql.html#OPENQUERY[/url] - 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

RE: My favorite way to access Excel files

warrensb — Thu, 31 Dec 2009 01:50:31 GMT

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=enalso 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.

RE: My favorite way to access Excel files

warrensb — Thu, 31 Dec 2009 01:45:49 GMT

Gaby, JacobWe 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 and2 - 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.

RE: My favorite way to access Excel files

malleswarareddy_m — Tue, 06 Oct 2009 04:26:54 GMT

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.

RE: My favorite way to access Excel files

gsc_dba — Wed, 20 May 2009 05:39:12 GMT

[quote][b](5/20/2009)[/b][hr]Please read this MS article: [url]http://support.microsoft.com/kb/814398[/url]I experienced this same error and the article helped solve the problem.[/quote]I also found this useful when querying a workbook with several worksheets:[code]EXECUTE SP_TABLES_EX 'your_linked_servername_goes_here'[/code]which returns the names of the worksheets

RE: My favorite way to access Excel files

gsc_dba — Wed, 20 May 2009 05:30:23 GMT

Please read this MS article: [url]http://support.microsoft.com/kb/814398[/url]I experienced this same error and the article helped solve the problem.

RE: My favorite way to access Excel files

Manie Verster — Thu, 09 Apr 2009 08:31:09 GMT

Gaby, this would be a very nice feature if only I can use it. I am on a computer with Vista Home Basic, Office 2007 and SQL Server 2008 and I get the following error:[code]The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.[/code]can anyone please help?

RE: My favorite way to access Excel files

GabyYYZ — Fri, 20 Feb 2009 06:51:09 GMT

[quote][b]Jacob Luebbers (2/19/2009)[/b][hr]Gaby, You may also want to amend the article to include the Excel 2007 methodology. Eg. using the Microsoft.ACE.OLEDB.12.0 provider with the [url=http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en]Office 2007 System Driver[/url] installed.[/quote]Thanks Jacob...haven't used Excel 2007 much yet but will have to try that. Anyone out there tried it with 2007 yet out of curiousity and, except for the driver modification, did it behave?

RE: My favorite way to access Excel files

Jacob Luebbers — Thu, 19 Feb 2009 17:22:10 GMT

Gaby, You may also want to amend the article to include the Excel 2007 methodology. Eg. using the Microsoft.ACE.OLEDB.12.0 provider with the [url=http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en]Office 2007 System Driver[/url] installed.And re: the 64-bit platform question, there is no neat way to use Jet of any version on an x64/ia64 instance. Either do the import from application code using the same OLEDB driver running on an x86 box or a seperate SQL Server instance on an x86 box. In a pinch you could probably run 32-bit application code on the x64 SQL server as it has access to 32-bit OLEDB drivers.Regards,Jacob

RE: My favorite way to access Excel files

clou-951938 — Thu, 05 Feb 2009 07:30:08 GMT

If the uploaded raw files can be transferred to .csv or .psv files, it should be a good solution. I use SSIS to handle many complicated .csv and .psv uploading procedures very successfully. Yes, in many cases, you can use 32 bit version DTExec.exe from 64 bit environment to handle the simple Excel spreadsheets (a pure tabulate format) by using SSIS. But it's not easy to handle a multi-sheets and free form format (for example, it contails titles, header or some formulated cells) Excel files. You need to access the data in a specific sheet and range in that .xls files. I think the CLR and sp_OA series OLEDB automation system stored procedures could be the ultimate solutions, if you need to work on the Excel file in very details such as sheet by sheet, range by range and cell by cell.

RE: My favorite way to access Excel files

BrianAltmann — Thu, 05 Feb 2009 05:11:22 GMT

We recently ran into this problem too.We reviewed some posts suggesting you can develop SSIS packages and run them in a 32 bit instance of the SSIS engine in the 64-bit platform (also installing the 32-bit Jet 4.0 engine, of course).But given the policy constraints on our production servers we went with the simpler solution of exporting to .csv and bulk inserting the data. In our case, we were reading the data into temporary tables at the beginning of each procedure so this was not such a big change.

RE: My favorite way to access Excel files

clou-951938 — Wed, 04 Feb 2009 13:32:03 GMT

That's right. The 64 bit system won't support Jet 4.0. I am quite sufferring from that problem too. So far, I couldn't any reliable solution to work around it yet. You can try a group of system stored procedures such as sp_OACreate, sp_OAMethod, sp_OAGetProperty and sp_OASetProperty. But, trust me it's difficult to use if you are not very familiar with VBA programming.

RE: My favorite way to access Excel files

GabyYYZ — Wed, 04 Feb 2009 06:41:28 GMT

[quote][b]m.schmidbauer (2/4/2009)[/b][hr]Dear Gaby,i know this really comfortable feature and i use it a lot.Good to know that it also works with SQL 2008 since we are actually using SQL2005.But as far is i know it does not work under 64 Bit Environments because MSDASQL is not available there.Is this true ?Do you know a workaround so that spreedsheets still can be queried directly with Transact SQL under 64-Bit?Best RegardsMarkus[/quote]To be honest, I'm not sure. We don't yet have any production servers in SQL 2008 yet, was mainly using the management studio portion. Anyone out there know the details on this?

RE: My favorite way to access Excel files

m.schmidbauer — Wed, 04 Feb 2009 03:14:12 GMT

Dear Gaby,i know this really comfortable feature and i use it a lot.Good to know that it also works with SQL 2008 since we are actually using SQL2005.But as far is i know it does not work under 64 Bit Environments because MSDASQL is not available there.Is this true ?Do you know a workaround so that spreedsheets still can be queried directly with Transact SQL under 64-Bit?Best RegardsMarkus

My favorite way to access Excel files

GabyYYZ — Sat, 31 Jan 2009 17:34:26 GMT

Comments posted to this topic are about the item [B]My favorite way to access Excel files[/B]