Can't you already do this fairly easily with VBScript and ADO/DAO/whatever it's called? That said, it's probably not a good idea to (further) encourage the relational DB paradigm within a spreadsheet!
(Also: a closed source extension for a proprietary product on HN? Is it 1st April somewhere in the world!?)
There's no simple "this workbook" connection that I'm aware of. So you have to use external connections and Access flavoured SQL to a named file and it all becomes brittle & ugly.
In my view, if you're at the point where you need to use the relational model with your data, you shouldn't be using Excel anyway. I realise it's "easy" and the go-to software for such things for corporate-types the world over, but that doesn't make it right.
SQLite -- plus, if you need it, some open source GUI -- and you are done. No pain of bending Excel to do what you want; none of the horrors of Access and its cludgy-flavoured SQL. If you really need it ultimately in Excel, I'm sure there's an ODBC driver you can use to get a view on the data.
Sometimes you just want to manipulate data in your spreadsheed in place with the help of SQL but without having to go through the ceremony of importing and exporting to/from a database. For instance, with this plugin you can use regular expressions (replacex function) to transform data in a column, or you can do a join. If that's all you need to do, the overhead of importing/exporting is not worthit, and having convenient SQL+Regex can be a nice time saver. (disclaimer: I'm the author)
You can, but it's clumsy. You can't target excel tables (only sheets and named ranges), no autocomplete and syntax highlighting. This addin also provides additional .NET funcitonality (e.g. regex match/replace, xpath, various date functions etc...) exposed as SQL functions, allows tht user to call VBA functions from SQL and can connect to an existing database to import/export data to/from a database. (disclaimer: I'm the author)
I'm banging my head against Excel, wishing for SQL right now! But I could never go to my boss and ask for a purchase order for something called ThingieQuery. Give it a more bland gray name and make a massively expensive corporate site license available, and I think you're onto a winner.
Author here. I'm inclined to agree, the naming is poor, it didn't start out as a serious product, and I never changed the name. That said, you can at least use the trial licence (free for 30 days) and after that who knows, maybe I'll rename the plugin to Excel SQL Studio 3000 XT and add an Enterprise Ultimate Premier License for 1200$.
Personally I think the guy is a moron. If he wasn't banging his head so hard he might realize it's reasonably priced and he can save all the corporate hassle by purchasing it himself.
Enterprise doesn't work that way. There are a lot of situations where you aren't even allowed to use free-as-in-beer open source software due to security and/or other concerns, let alone software that you've paid for yourself. Besides, if the company is the one getting the benefit from the usage of the tool, why should he be the one paying for it?
The way where I work works (technically enterprise, a couple thousand full-time employees), if you want some software and think it's reasonably-priced, you can buy it and expense it. I've never heard of anyone getting in any trouble over this. You can also submit a helpdesk ticket to have purchasing buy something for you, which is what we'd do for e.g. Subscription licenses which require someone entering a corporate card and keeping track of it.
at $50 i just go to my boss and say "Hey Boss, can i get the company card to buy a $50 product so I can do the xyz task easier?" almost always he'll say "$50, yeah whatever"
So how about a tool that would open an Excel spreadsheet as a sqlite database ? By on-the-fly converting the tabs into tables. Then open this database in your favorite sqlite browser.
Maybe even open several spreadsheet files at once, each file in a separate database.
Neat idea, you could get pretty close to that experience right now by running http://databaseimportscriptor.com, then Alt+Tab-ing into MySQL Workbench.
Problem is, I can't really see a market for it. You're trying to write SQL on your database, so you're a developer, and how much would you really be willing to pay to have the experience streamlined down to a couple of clicks?
Nice. Though, from a finance perspective, my daily nightmare is in the sharing piece - not in the analysis piece - of my job. Sharing numbers in spreadsheets and consolidations is a deep un-solved pain in the finance world. But I do see this solution as a potential fix for the many pivot functionalities shortfalls.
Any chance you could expand on some of those pains? Is the problem with simply sharing the data itself or with being able to slice/dice/query/do smth with the shared data? I'm sure there are a bunch of people around here who'd love to hear some user pains/stories to improve their apps.
Well, what do you want to know? I have many horror stories, actually, I just had 1 today - it took me half day to upload 6 Income Statements (6 spreadsheets) to our Company official tool (HFM). This should take no more than a couple of minutes. Other story? Try to run an Opex (Operating Expenses) budget when you need engineering, cus service, mantainance, etc., departments to all give you costs estimates and then try to track those costs (BPC is one other terrible tool that is supposed to help you with this kind of need). I am talking +500 cost centers each one split in cost types, each cost type split in natural accounts. Getting data and dealing with data in those cases can be a nightmare.
But you are right, there is a reporting part too to it. It's just that I think the sharing and data handling piece is the real key. There are many decent reporting solutions out there, none that integrates a serious data sharing/integration solution (I am also referring to data feeds from other systems, other painful topic). One other big topic is integration, companies do not know what an API is, so getting data out of systems usually requires jumping through different hoops of terrible UIs and button clicking.
But hey, if the topic sounds interesting for you and/or others, feel free to send me an email.
I don't know if it's a regional thing or what, but the idiom is usually "color me interested" rather than "paint me interested." Maybe you care, maybe you don't. I've just never seen "paint me interested" like that and thought it was curious.
Even basic SQL can make a lot of things easier in Excel, and SQL is not that complex so it might be doable. And the plugin is perfect for teaching SQL to someone, you can immediately see the effects of queries on data. Just sayin... :)
This is what I've wanted to do in excel since forever: a worksheet function that takes a range to use as the table and the SQL query as params, something like:
=QUERYTABLE("SELECT * FROM TABLE", A1:G1000)
I actually wrote my own in VBA that wrapped the ADO/JET db engine but involved writing out the table as a .csv in the background so I could query it. Would love a "proper" solution.
Looks interesting, how does this work under the hood? Does it use something like sqlite, or mssql? Because surely the syntax for queries is different, the page simply says it supports all SQL, but SQL can vary a lot between databases (or is it your own kind, and in that case yet another variant?)
Yeah, it uses SQLite under the hood. SQLite has a mechanism it calls "virtual tables" which I used to implement an adapter that lets it see Excel tables as database tables. I also implemented hash indexes in the adapter so joins and lookups are crazy fast (searches by range not so much though). SQLite is very cool, it also lets developer hook additional functions into it, which is how I exposed some .NET functions and they can be used from SQL (replacex=Regex.Replace, ismatch=Regex.IsMatch, format=String.Format, etc...)
So what happens when I use this to create a spreedsheet, and I then share this spreadsheet with someone else who doesn't have this tool installed, or doesn't have a license?
Not much, the data will show up fine, by default ThingieQuery doesn't do anything magical with the workbook itself it just reads and if you want writes data to it.
You can however explicitly embed a query into a workbook (it gets stored in the CustomXMLParts of the workbook), and you can automate running this query from VBA. For this automation to work, the other computer would need to have ThingieQuery installed, but it wouldn't need a license. The license is only required when working with the ThingieQuery UI.
I'm currently working on the ability to automate the execution of embedded queries from the UI, since the VBA automation API is less user friendly. I'm a bit apprehensive about this, as it might encourage further misuse of Excel though.
Also, if you connect to an external database instead of using the built in SQLite engine, the dialect will be that of the database of course. It works a little differently in this mode though.
This is only the most AMAZING thing to happen to Excel! I got some Excel data from a friend to analyze and went through the usual PivotTable way for a while until I hit a roadblock and I needed something more advanced. I knew how to solve my problem with SQL but not with Excel, so I ended up downloading and installing SQL Server Express. This would have saved me from having to do that. Looking forward to more features.
How does the autocompletion work? Is is context sensitive to suggest only column names after the 'WHERE' clause or will it suggest 'tables and columns'?
Good question. Unfortunately no, not yet. It's not context sensitive, actually it's as dumb as a brick:) If you enter the name of a table followed by a dot, it will offer only the columns from the table. Other than that, it offers pretty much all objects and filters them as you type.
I've been wanting to implement a SQL parser (at least for SQLite) so I can offer meaningful autocomplete, and some other nice features, but it was a lower priority than other things. I've done some work with Antlr a few years ago, but I'm itchin' to roll my own since I'm already dusting off my compiler theory books...
Anyway, why not give it a try, the trial is free:)
I'd love to, but unfortunately I don't have Windows or Excel. :(
I was curious because I wrote pgcli (http://pgcli.com) which does context-sensitive auto-completion. So I was wondering if there was a better way to do it than what I'm doing right now.
It supports connecting to PostgreSQL and MSSQL currently. When you connect to an external engine it opens up a connection, and copies selected Excel tables as temp tables to the destination DB. This makes it easy to combine excel data with existing db data in your queries, and also to move data in easily in either direction.
It uses low level bulk copy operations when importing excel data so it's pretty fast. I did not yet implement support for Oracle because it doesn't have session level temp tables, only global temp tables... I'll see what I can do there.
I haven't used the MySQL Excel plugin so I can't comment on its features...
Finding this has just made my whole (not great) week better by a huge degree. I've played with it on a workbook with ~100k rows and the performance is fantastic. I'm really excited!
Nice project! I made a similar one but using NoSQL-like syntax and a Google Spreadsheet for fun and I learned a lot, but of course nothing so professional (;
Hey Pete, I just wanted to say thanks for posting this, it was a huge deal for my page views. Between reddit and hackernews, my webpage went from having about 100 visitors/day to having 10k in a single day! Thanks a bunch, and if you tell me where you live (maybe via comments on my page), I'll make sure you get beer to delivered to your doorstep:)
Can't you already do this fairly easily with VBScript and ADO/DAO/whatever it's called? That said, it's probably not a good idea to (further) encourage the relational DB paradigm within a spreadsheet!
(Also: a closed source extension for a proprietary product on HN? Is it 1st April somewhere in the world!?)
There's no simple "this workbook" connection that I'm aware of. So you have to use external connections and Access flavoured SQL to a named file and it all becomes brittle & ugly.
In my view, if you're at the point where you need to use the relational model with your data, you shouldn't be using Excel anyway. I realise it's "easy" and the go-to software for such things for corporate-types the world over, but that doesn't make it right.
SQLite -- plus, if you need it, some open source GUI -- and you are done. No pain of bending Excel to do what you want; none of the horrors of Access and its cludgy-flavoured SQL. If you really need it ultimately in Excel, I'm sure there's an ODBC driver you can use to get a view on the data.
Sometimes you just want to manipulate data in your spreadsheed in place with the help of SQL but without having to go through the ceremony of importing and exporting to/from a database. For instance, with this plugin you can use regular expressions (replacex function) to transform data in a column, or you can do a join. If that's all you need to do, the overhead of importing/exporting is not worthit, and having convenient SQL+Regex can be a nice time saver. (disclaimer: I'm the author)
You can, but it's clumsy. You can't target excel tables (only sheets and named ranges), no autocomplete and syntax highlighting. This addin also provides additional .NET funcitonality (e.g. regex match/replace, xpath, various date functions etc...) exposed as SQL functions, allows tht user to call VBA functions from SQL and can connect to an existing database to import/export data to/from a database. (disclaimer: I'm the author)
I'm banging my head against Excel, wishing for SQL right now! But I could never go to my boss and ask for a purchase order for something called ThingieQuery. Give it a more bland gray name and make a massively expensive corporate site license available, and I think you're onto a winner.
Author here. I'm inclined to agree, the naming is poor, it didn't start out as a serious product, and I never changed the name. That said, you can at least use the trial licence (free for 30 days) and after that who knows, maybe I'll rename the plugin to Excel SQL Studio 3000 XT and add an Enterprise Ultimate Premier License for 1200$.
Personally I think the guy is a moron. If he wasn't banging his head so hard he might realize it's reasonably priced and he can save all the corporate hassle by purchasing it himself.
You say that as if his company would just allow him to use software he purchased himself...
Enterprise doesn't work that way. There are a lot of situations where you aren't even allowed to use free-as-in-beer open source software due to security and/or other concerns, let alone software that you've paid for yourself. Besides, if the company is the one getting the benefit from the usage of the tool, why should he be the one paying for it?
The way where I work works (technically enterprise, a couple thousand full-time employees), if you want some software and think it's reasonably-priced, you can buy it and expense it. I've never heard of anyone getting in any trouble over this. You can also submit a helpdesk ticket to have purchasing buy something for you, which is what we'd do for e.g. Subscription licenses which require someone entering a corporate card and keeping track of it.
Haha - you have clearly never worked for a 'real' company :)
ExSQLence.com
completely agree about the name - would love to use this at the office.
+1 on the name...
at $50 i just go to my boss and say "Hey Boss, can i get the company card to buy a $50 product so I can do the xyz task easier?" almost always he'll say "$50, yeah whatever"
I think the approach of https://airtable.com/ is a better solution for these kind of problems, but not everybody is free to change tools.
A lot of data is already in spreadsheets. And using an existing spreadsheet in AirTable is not quick and easy because its only possible with csv import: https://support.airtable.com/hc/en-us/articles/203423579-Imp...
Strange they do not offer a more direct import. It would help the transition.
A bit late to the punch, but we also let you copy and paste from most other spreadsheet programs directly into Airtable
I think Excel just got developer friendly. Cool tool.
I have been wanting a tool like this for a few years. I can see a bazillion uses for it. Home-run!
It looks great. Too bad it's Windows only.
The preview of excel 2016 for mac is pretty good and some of our marketing staff are contemplating the idea of migrating to mac.
Congrats on the launch, I'm not an excel user myself but I've sent the link to some of my coworkers and they look excited :D
Glad to hear your coworkers are excited!:) I'd love to support mac but unless excel 2016 includes VSTO, it won't be doable.
So how about a tool that would open an Excel spreadsheet as a sqlite database ? By on-the-fly converting the tabs into tables. Then open this database in your favorite sqlite browser. Maybe even open several spreadsheet files at once, each file in a separate database.
ODBC does this on windows. You can connect to a spreadsheet and issue queries.
Neat idea, you could get pretty close to that experience right now by running http://databaseimportscriptor.com, then Alt+Tab-ing into MySQL Workbench.
Problem is, I can't really see a market for it. You're trying to write SQL on your database, so you're a developer, and how much would you really be willing to pay to have the experience streamlined down to a couple of clicks?
This could be awesome for teaching SQL
Yeah, I'm hoping it will be used that way also, since you can read and write data both by hand and using SQL and immediately see the effects.
Nice. Though, from a finance perspective, my daily nightmare is in the sharing piece - not in the analysis piece - of my job. Sharing numbers in spreadsheets and consolidations is a deep un-solved pain in the finance world. But I do see this solution as a potential fix for the many pivot functionalities shortfalls.
Any chance you could expand on some of those pains? Is the problem with simply sharing the data itself or with being able to slice/dice/query/do smth with the shared data? I'm sure there are a bunch of people around here who'd love to hear some user pains/stories to improve their apps.
Well, what do you want to know? I have many horror stories, actually, I just had 1 today - it took me half day to upload 6 Income Statements (6 spreadsheets) to our Company official tool (HFM). This should take no more than a couple of minutes. Other story? Try to run an Opex (Operating Expenses) budget when you need engineering, cus service, mantainance, etc., departments to all give you costs estimates and then try to track those costs (BPC is one other terrible tool that is supposed to help you with this kind of need). I am talking +500 cost centers each one split in cost types, each cost type split in natural accounts. Getting data and dealing with data in those cases can be a nightmare.
But you are right, there is a reporting part too to it. It's just that I think the sharing and data handling piece is the real key. There are many decent reporting solutions out there, none that integrates a serious data sharing/integration solution (I am also referring to data feeds from other systems, other painful topic). One other big topic is integration, companies do not know what an API is, so getting data out of systems usually requires jumping through different hoops of terrible UIs and button clicking.
But hey, if the topic sounds interesting for you and/or others, feel free to send me an email.
All the screenshots seem to be based on Excel 2013. Is it compatible with earlier versions of Excel?
Yes, it will work on Office 2007, 2010, 2013 on both platforms (x86/x64)
I don't know if it's a regional thing or what, but the idiom is usually "color me interested" rather than "paint me interested." Maybe you care, maybe you don't. I've just never seen "paint me interested" like that and thought it was curious.
Point taken:) I have no idea where I got the "paint me xyz" from. Thanks for pointing it out, I changed it to "color me interested"
Very nice tool. It might become very useful at work if I can comvert all the heavy excel users towards SQL queries.
Even basic SQL can make a lot of things easier in Excel, and SQL is not that complex so it might be doable. And the plugin is perfect for teaching SQL to someone, you can immediately see the effects of queries on data. Just sayin... :)
If SQL is hard for your users take a look at EasyMorph (http://easymorph.com). Disclaimer: I'm the author.
This is what I've wanted to do in excel since forever: a worksheet function that takes a range to use as the table and the SQL query as params, something like:
I actually wrote my own in VBA that wrapped the ADO/JET db engine but involved writing out the table as a .csv in the background so I could query it. Would love a "proper" solution.
Not what you want, but Google docs has this and it's very nice.
https://support.google.com/docs/answer/3093343?hl=en
Wow, I never knew about this! Something I'm sure to use. Thanks.
See also: textql - Execute SQL against structured text like CSV or TSV
GitHub: https://github.com/dinedal/textql
HN: https://news.ycombinator.com/item?id=7175830
Looks interesting, how does this work under the hood? Does it use something like sqlite, or mssql? Because surely the syntax for queries is different, the page simply says it supports all SQL, but SQL can vary a lot between databases (or is it your own kind, and in that case yet another variant?)
Yeah, it uses SQLite under the hood. SQLite has a mechanism it calls "virtual tables" which I used to implement an adapter that lets it see Excel tables as database tables. I also implemented hash indexes in the adapter so joins and lookups are crazy fast (searches by range not so much though). SQLite is very cool, it also lets developer hook additional functions into it, which is how I exposed some .NET functions and they can be used from SQL (replacex=Regex.Replace, ismatch=Regex.IsMatch, format=String.Format, etc...)
So what happens when I use this to create a spreedsheet, and I then share this spreadsheet with someone else who doesn't have this tool installed, or doesn't have a license?
Then they wouldn't have the functionality that the tool provides I guess?
Not much, the data will show up fine, by default ThingieQuery doesn't do anything magical with the workbook itself it just reads and if you want writes data to it.
You can however explicitly embed a query into a workbook (it gets stored in the CustomXMLParts of the workbook), and you can automate running this query from VBA. For this automation to work, the other computer would need to have ThingieQuery installed, but it wouldn't need a license. The license is only required when working with the ThingieQuery UI.
I'm currently working on the ability to automate the execution of embedded queries from the UI, since the VBA automation API is less user friendly. I'm a bit apprehensive about this, as it might encourage further misuse of Excel though.
Excellent, thank you for the reply.
You are very welcome!:)
Also, if you connect to an external database instead of using the built in SQLite engine, the dialect will be that of the database of course. It works a little differently in this mode though.
This is only the most AMAZING thing to happen to Excel! I got some Excel data from a friend to analyze and went through the usual PivotTable way for a while until I hit a roadblock and I needed something more advanced. I knew how to solve my problem with SQL but not with Excel, so I ended up downloading and installing SQL Server Express. This would have saved me from having to do that. Looking forward to more features.
That's exactly the kind of response I was aiming for:) Thanks for saying that, it's amazing to read!
How does the autocompletion work? Is is context sensitive to suggest only column names after the 'WHERE' clause or will it suggest 'tables and columns'?
Good question. Unfortunately no, not yet. It's not context sensitive, actually it's as dumb as a brick:) If you enter the name of a table followed by a dot, it will offer only the columns from the table. Other than that, it offers pretty much all objects and filters them as you type.
I've been wanting to implement a SQL parser (at least for SQLite) so I can offer meaningful autocomplete, and some other nice features, but it was a lower priority than other things. I've done some work with Antlr a few years ago, but I'm itchin' to roll my own since I'm already dusting off my compiler theory books...
Anyway, why not give it a try, the trial is free:)
I'd love to, but unfortunately I don't have Windows or Excel. :(
I was curious because I wrote pgcli (http://pgcli.com) which does context-sensitive auto-completion. So I was wondering if there was a better way to do it than what I'm doing right now.
https://github.com/dbcli/pgcli/blob/master/pgcli/packages/sq...
The demo videos look impressive. Congrats on the launch. :)
"SQL"? MSSQL? MySQL? Postgres? Oracle? DB2?
There is a the free MySQL Excel plugin (https://dev.mysql.com/downloads/windows/excel/ )and I think the free MS PowerPivot plugin supports SQL databases too.
It supports connecting to PostgreSQL and MSSQL currently. When you connect to an external engine it opens up a connection, and copies selected Excel tables as temp tables to the destination DB. This makes it easy to combine excel data with existing db data in your queries, and also to move data in easily in either direction.
It uses low level bulk copy operations when importing excel data so it's pretty fast. I did not yet implement support for Oracle because it doesn't have session level temp tables, only global temp tables... I'll see what I can do there.
I haven't used the MySQL Excel plugin so I can't comment on its features...
Is there anything similar for Google spreadsheets?
Google spreadsheets has a QUERY function which takes an SQL query
Finding this has just made my whole (not great) week better by a huge degree. I've played with it on a workbook with ~100k rows and the performance is fantastic. I'm really excited!
Thanks, I'm very glad to read it, and I'm excited as heck at all the positive feedback!
nice, playing with it now
Nice project! I made a similar one but using NoSQL-like syntax and a Google Spreadsheet for fun and I learned a lot, but of course nothing so professional (;
Hey Pete, I just wanted to say thanks for posting this, it was a huge deal for my page views. Between reddit and hackernews, my webpage went from having about 100 visitors/day to having 10k in a single day! Thanks a bunch, and if you tell me where you live (maybe via comments on my page), I'll make sure you get beer to delivered to your doorstep:)