4 ms·

Actually, I would still love to have a setup where Excel is the front end with a database it can query and manipulate.

by newman314 14y ago

tsewlliw 14y ago
I don't remember exactly how, but yeah, you can totally do that. Set up your DB as a data source or some such.
- pixelcloud 14y ago
  You can add many different data sources (any ODBC driver), access, sharepoint sites, etc. Its a pretty useful tool for people who are not programmers and rely on PivotTables and such. Its surprising how effective excel can be for non-programmers.
RossP 14y 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 14y ago
  You sir, are a godsend. This is wonderful. Thank you.
- newman314 14y ago
  Is it read only or can it be read/write?
  - RossP 14y 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 14y 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 14y 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 14y 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!
nsp 14y ago
For a quick and dirty approach you can use .iqy query files. It's a really basic format, just a text file with a URL to open and options defined by embedding arrays in the params string. e.g. POST Example.com/get_data?first_param=["parameter name","parameter description"] And then a few variables describing the format of the data to return - it'll parse an HTML table and csv's/tab separated values but that's about it, but it's better than the copy paste loop hell. You can then add the query file as an external data source and pass parameters to queries to a web interface through excel - cell values or user inputs. It's cross platform back to at least osx office 2003. They're a little clunky, but pretty painless to setup and the only mac compatible system I've found. Excel<->Web data integration is something I've been weirdly interested in for awhile - if anyone has questions feel free to email me (its in hn profile) Shameless plug: I wrote excel-data, a ruby gem that generates iqy files for excel, formats ruby hashes in a format it can interpret, and parses excels bizarre date format. It's pretty basic and my first OSS project, but gives you an idea of the format.

Actually, I would still love to have a setup where Excel is the front end with a database it can query and manipulate.

tsewlliw 14y ago

I don't remember exactly how, but yeah, you can totally do that. Set up your DB as a data source or some such.

pixelcloud 14y ago
You can add many different data sources (any ODBC driver), access, sharepoint sites, etc. Its a pretty useful tool for people who are not programmers and rely on PivotTables and such. Its surprising how effective excel can be for non-programmers.

RossP 14y 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 14y ago
You sir, are a godsend. This is wonderful. Thank you.
newman314 14y ago
Is it read only or can it be read/write?
- RossP 14y 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 14y 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 14y 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 14y 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!

nsp 14y ago

For a quick and dirty approach you can use .iqy query files. It's a really basic format, just a text file with a URL to open and options defined by embedding arrays in the params string. e.g. POST Example.com/get_data?first_param=["parameter name","parameter description"] And then a few variables describing the format of the data to return - it'll parse an HTML table and csv's/tab separated values but that's about it, but it's better than the copy paste loop hell. You can then add the query file as an external data source and pass parameters to queries to a web interface through excel - cell values or user inputs. It's cross platform back to at least osx office 2003. They're a little clunky, but pretty painless to setup and the only mac compatible system I've found. Excel<->Web data integration is something I've been weirdly interested in for awhile - if anyone has questions feel free to email me (its in hn profile) Shameless plug: I wrote excel-data, a ruby gem that generates iqy files for excel, formats ruby hashes in a format it can interpret, and parses excels bizarre date format. It's pretty basic and my first OSS project, but gives you an idea of the format.