
ThingieQuery: a SQL plugin for Excel - petepete
http://thingiequery.com/
======
emailgregn
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.

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

~~~
artmageddon
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?

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

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

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

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

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

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

~~~
anakic
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"

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

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

~~~
tokenizerrr
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?

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

~~~
tokenizerrr
Excellent, thank you for the reply.

~~~
anakic
You are very welcome!:)

------
bentronic
I think the approach of [https://airtable.com/](https://airtable.com/) is a
better solution for these kind of problems, but not everybody is free to
change tools.

~~~
Gys
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...](https://support.airtable.com/hc/en-
us/articles/203423579-Importing-a-spreadsheet-from-Excel-or-Google-Sheets)

Strange they do not offer a more direct import. It would help the transition.

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

------
pmx
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!

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

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

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

[https://support.google.com/docs/answer/3093343?hl=en](https://support.google.com/docs/answer/3093343?hl=en)

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

------
_jomo
See also: textql - Execute SQL against structured text like CSV or TSV

GitHub: [https://github.com/dinedal/textql](https://github.com/dinedal/textql)

HN:
[https://news.ycombinator.com/item?id=7175830](https://news.ycombinator.com/item?id=7175830)

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

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

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

~~~
anakic
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)

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

------
frik
"SQL"? MSSQL? MySQL? Postgres? Oracle? DB2?

There is a the free MySQL Excel plugin
([https://dev.mysql.com/downloads/windows/excel/](https://dev.mysql.com/downloads/windows/excel/)
)and I think the free MS PowerPivot plugin supports SQL databases too.

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

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

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

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

~~~
d4nt
Neat idea, you could get pretty close to that experience right now by running
[http://databaseimportscriptor.com](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?

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

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

~~~
amjith
I'd love to, but unfortunately I don't have Windows or Excel. :(

I was curious because I wrote pgcli ([http://pgcli.com](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...](https://github.com/dbcli/pgcli/blob/master/pgcli/packages/sqlcompletion.py)

The demo videos look impressive. Congrats on the launch. :)

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

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

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

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

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

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

------
rajadigopula
I think Excel just got developer friendly. Cool tool.

------
Nikolas0
Is there anything similar for Google spreadsheets?

~~~
mkehrt
Google spreadsheets has a QUERY function which takes an SQL query

------
zongitsrinzler
This could be awesome for teaching SQL

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

------
nelzya
nice, playing with it now

