[RossP]

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-impo...

[petepete]

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]

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!

[Someone]

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).

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.

[drakeandrews]

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

[newman314]

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

[RossP]

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.