ThingieQuery: a SQL plugin for Excel

by petepete • 11 years ago

93 64 11 years ago

Xophmeister2• 11 years ago

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!?)

emailgregn2• 11 years ago

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.

Xophmeister2• 11 years ago

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.

anakic17• 11 years ago

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)

emailgregn2• 11 years ago

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.

anakic17• 11 years ago

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

degenerate• 11 years ago

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.

samstave• 11 years ago

ExSQLence.com

aphrax• 11 years ago

completely agree about the name - would love to use this at the office.

blumkvist• 11 years ago

+1 on the name...

swalsh• 11 years ago

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"

bentronic• 11 years ago

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.

Gys2• 11 years ago

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.

howsta• 11 years ago

A bit late to the punch, but we also let you copy and paste from most other spreadsheet programs directly into Airtable

rajadigopula• 11 years ago

I think Excel just got developer friendly. Cool tool.

SQL2219• 11 years ago

I have been wanting a tool like this for a few years. I can see a bazillion uses for it. Home-run!

mickeyben• 11 years ago

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

anakic17• 11 years ago

Glad to hear your coworkers are excited!:) I'd love to support mac but unless excel 2016 includes VSTO, it won't be doable.

Gys2• 11 years ago

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.

batou• 11 years ago

ODBC does this on windows. You can connect to a spreadsheet and issue queries.

d4nt• 11 years ago

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?

zongitsrinzler• 11 years ago

This could be awesome for teaching SQL

anakic17• 11 years ago

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.

kfk2• 11 years ago

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.

paulasmuth• 11 years ago

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.

kfk2• 11 years ago

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.

cm2187• 11 years ago

All the screenshots seem to be based on Excel 2013. Is it compatible with earlier versions of Excel?

anakic17• 11 years ago

Yes, it will work on Office 2007, 2010, 2013 on both platforms (x86/x64)

FroshKiller• 11 years ago

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.

anakic17• 11 years ago

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"

Globz• 11 years ago

Very nice tool. It might become very useful at work if I can comvert all the heavy excel users towards SQL queries.

anakic17• 11 years ago

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

dgudkov• 11 years ago

If SQL is hard for your users take a look at EasyMorph (http://easymorph.com). Disclaimer: I'm the author.

retube• 11 years ago

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.

tokenizerrr3• 11 years ago

Not what you want, but Google docs has this and it's very nice.

https://support.google.com/docs/answer/3093343?hl=en

wanderingstan• 11 years ago

Wow, I never knew about this! Something I'm sure to use. Thanks.

_jomo• 11 years ago

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

orf2• 11 years ago

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

anakic17• 11 years ago

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

tokenizerrr3• 11 years ago

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?

anakic17• 11 years ago

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.

ergest• 11 years ago

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.

anakic17• 11 years ago

That's exactly the kind of response I was aiming for:) Thanks for saying that, it's amazing to read!

amjith2• 11 years ago

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'?

anakic17• 11 years ago

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

amjith2• 11 years ago

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

frik• 11 years ago

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

anakic17• 11 years ago

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

Nikolas0• 11 years ago

Is there anything similar for Google spreadsheets?

mkehrt• 11 years ago

Google spreadsheets has a QUERY function which takes an SQL query

pmx• 11 years ago

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!

anakic17• 11 years ago

Thanks, I'm very glad to read it, and I'm excited as heck at all the positive feedback!

nelzya• 11 years ago

nice, playing with it now

franciscop• 11 years ago

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 (;

anakic17• 11 years ago

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

ThingieQuery: a SQL plugin for Excel

Related Stories