May 1, 2012 at 9:59 am
How do you create a connection to an Excel file (either Excel 2010 or 97-2003), from a SQL database (SQL Server 2012) for the purpose of writing a SELECT statement against it?
On the following website I see some instructions for doing this however it is not enough.
http://www.etl-tools.com/etl-tools/visual-importer-etl-news/running-sql-against-excel-file.html
For example, either of these connection strings followed by the select statement:
---ODBC Connection string for Excel 2007
DRIVER={Microsoft Excel Driver (*.xls)};IMEX=1;MaxScanRows=16;DBQ=C:\Invoice.xls;
---OLE DB Connection string for Excel 2007
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
---select statement to run, once the connection is working
SELECT * FROM [Sheet1$]
Can someone point me to a good resource for accomplishing this task?
Thanks.
--Quote me
May 1, 2012 at 11:08 am
32-bit or 64-bit?
How does selecting data from an Excel Workbook fit into your overall process, i.e. what are you trying to accomplish?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 2, 2012 at 12:50 pm
Thanks OPC.three.
Overall process.
I have to introduce a column, into my reporting query, which doesn't exist in the datawarehouse and isn't going to. I have been advised to create a temp table to contain the values for that column, but that is pages and pages of typing. The excel source from which I will get this column value contains the mapping to known columns. However, it will grow over time as not all the mapping are currently known, so I will be revisiting the temp table many many times.
I want to eliminate the tedious work of updating temp table by hand. I want my query to select directly from the Excel source file. Since the excel file source's columns will remain static, it seems reasonable to hope that once I get a query to work, that's that, and I can dust my hands off.
64 bit.
--Quote me
May 2, 2012 at 1:50 pm
Do you need to query it repeatedly or just once? If repeatedly, will it be the same file each time, or will it be a different file different times.
If it's repeatedly, and it's the same file, then the best way is to set up a Linked Server. Go to Server Objects under your server name in Management Studio, right-click Linked Server and follow the directions for setting up a new linked server.
If it's one-time only, then I recommend importing the data into SQL Server using the Import/Export wizard, and going from there.
If it will be a dynamic file, then that gets complicated, but can still be done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2012 at 2:02 pm
No, it will be ongoing accessing of the Excel source file. I will look into those Linked Server directions.
--Quote me
May 5, 2012 at 5:15 am
I am following directions at this link:
http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx
per instructions: "In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server."
Is this ""Server Objects" that I am supposed to double-click a button a menu item or ...? Can't find where I am to double click to start this process.
Will permissions possibly be an issue? Any particular permissions must I have to be able to make a connection to an Excel file?
--Quote me
May 6, 2012 at 3:49 pm
additionally, this will not work because the excel file that needs to be queried is not on the same server as the instance of SQL Server that needs to query it.
from msdn:
For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.
--Quote me
May 6, 2012 at 4:17 pm
In the final analysis I cannot (due to permission restrictions) create a new table in SQL Server and I can't store the excel datasource on the same server as the SQL Server instance (therefore, linked server option isn't viable) and I do not have bulkload permissions on SQL server so can't bulkload date from .csv file and I do not have permissions to reconfigure the instance for ad hoc queries (therefore Select * from OPENDATASOURCE is also not viable).
Does anyone know if it is possible for a *query based* excel report to join on a table defined exclusively in PowerPivot Excel workspace?
--Quote me
May 7, 2012 at 12:20 am
The solution has been to convert the excel file into a .csv file and import data into Excel with Microsoft Query. Moreoever, since I am using powerpivot, I can establish relationship between imported data and existing query based report.
--Quote me
May 7, 2012 at 1:32 pm
polkadot (5/6/2012)
additionally, this will not work because the excel file that needs to be queried is not on the same server as the instance of SQL Server that needs to query it.from msdn:
For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.
The article is saying the OLE DB provider DLL must reside on the same instance as SQL Server, not necessarily the file the Linked Server points to. As long as the security context the Linked Server runs under (part of the Linked Server setup) can access the Excel file it can reside on a remote UNC share.
If you download and install the newer ACE drivers you can do something like this:
EXEC sp_addLinkedServer
@server = 'XLLink',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = '\\servername\sharename\filename.xlsx',
@provstr = 'Excel 12.0;HDR=Yes';
GO
SELECT *
FROM XLLink...[Sheet1$]
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 8, 2012 at 11:54 am
polkadot (5/6/2012)
In the final analysis I cannot (due to permission restrictions) create a new table in SQL Server and I can't store the excel datasource on the same server as the SQL Server instance (therefore, linked server option isn't viable) and I do not have bulkload permissions on SQL server so can't bulkload date from .csv file and I do not have permissions to reconfigure the instance for ad hoc queries (therefore Select * from OPENDATASOURCE is also not viable).Does anyone know if it is possible for a *query based* excel report to join on a table defined exclusively in PowerPivot Excel workspace?
That should be possible.
Both Excel and SQL server can be data sources for a PowerPivot workbook.
And they can be joined as long as each table has a similar key to join on.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply