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"
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.
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.
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.
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!
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.
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!?)
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)
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)
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:)
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...
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?
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.
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 (;
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... :)
