5 ms·

Excel does this with it's built-in data sources. Typically it can connect to any ODBC data source, which includes pretty much any SQL database and most propriet

by RossP 13y ago

Excel does this with it's built-in data sources. Typically it can connect to any ODBC data source, which includes pretty much any SQL database and most proprietary databases (although in some cases you'll need to buy ODBC drivers. Yuck) Then, bingo! You've got real, structured, data available in Excel so you can run Pivot Tables, build charts, and filter data to your hearts content. I use it frequently to build read-only data views for people who want to analyse their data in ways they don't know how to do using more native tools. This microsoft post is for Office 2007, but applies equally in newer versions: http://office.microsoft.com/en-au/excel-help/connect-to-import-external-data-HP010089898.aspx http://office.microsoft.com/en-au/excel-help/connect-to-impo...

drakeandrews 13y ago
You sir, are a godsend. This is wonderful. Thank you.
newman314 13y ago
Is it read only or can it be read/write?
- RossP 13y ago
  Depends on the data source, I believe. I think some can be RW however I never use it - allowing users to update data from Excel isn't something I'm keen on, other than possibly as a sysadmin task.
Someone 13y ago
By the way: in MS SQL Server, one can also do the reverse: use Excel spreadsheets as the backing store for SQL Server queries (http://msdn.microsoft.com/en-us/library/ms141683.aspx http://msdn.microsoft.com/en-us/library/ms141683.aspx). It is not easy to set up, but way easier than using csv for getting raw data from Excel into SQL Server, or for running a clean-up query on data in Excel.
petepete 13y ago
I used to work for a hospital that used this strategy for almost all of its internal reporting (I was contracted to ease the transition to Business Objects); it can get out of hand quickly. Every morning between 8:45 and 9AM, doctors and administrators would come in, fire up Excel and refresh their data, the slowdown on the network was noticeable (pretty much a 'select *' on a massive de-normalised reporting tables hundreds of times throughout the site). The flexibility that Excel offered some (not-too-technical) power users in those circumstances was fantastic, though, as much as I hate to admit it.
- RossP 13y ago
  Yep, it's got it's limits - I would only use this in production with carefully crafted queries to try too counter this problem. My usual use is against MS CRM data, which puts a 10k line limit on by default. First instinct for many people is to disable the limit, but in reality it's a good fail safe!

by RossP 13y ago

drakeandrews 13y ago

You sir, are a godsend. This is wonderful. Thank you.

newman314 13y ago

Is it read only or can it be read/write?

RossP 13y ago
Depends on the data source, I believe. I think some can be RW however I never use it - allowing users to update data from Excel isn't something I'm keen on, other than possibly as a sysadmin task.

Someone 13y ago

By the way: in MS SQL Server, one can also do the reverse: use Excel spreadsheets as the backing store for SQL Server queries (http://msdn.microsoft.com/en-us/library/ms141683.aspx http://msdn.microsoft.com/en-us/library/ms141683.aspx). It is not easy to set up, but way easier than using csv for getting raw data from Excel into SQL Server, or for running a clean-up query on data in Excel.

petepete 13y ago

I used to work for a hospital that used this strategy for almost all of its internal reporting (I was contracted to ease the transition to Business Objects); it can get out of hand quickly. Every morning between 8:45 and 9AM, doctors and administrators would come in, fire up Excel and refresh their data, the slowdown on the network was noticeable (pretty much a 'select *' on a massive de-normalised reporting tables hundreds of times throughout the site). The flexibility that Excel offered some (not-too-technical) power users in those circumstances was fantastic, though, as much as I hate to admit it.

RossP 13y ago
Yep, it's got it's limits - I would only use this in production with carefully crafted queries to try too counter this problem. My usual use is against MS CRM data, which puts a 10k line limit on by default. First instinct for many people is to disable the limit, but in reality it's a good fail safe!