

Show HN: I created an Excel plugin for using SQL inside Excel - anakic
http://thingiequery.com/

======
anakic
Hi all!

So as a side project I developed a plugin that lets you do SQL inside Excel.
It's called ThingieQuery (www.thingiequery.com).

When you install ThingieQuery, you get a SQL IDE window inside Excel. It uses
an embedded SQLite engine to process the queries and uses the data from excel
tables. Basically you get full SQL support inside Excel.

In case you want to query your excel tables together with the tables on an
external db server you can do that as well. In this case the data from Excel
tables will be copied in the form of temp tables to the external server. If
you wish, you can write the results of your queries back to Excel.

It has syntax highlighting, code completion (currently rudimentary tho), and
can do some neat tricks.

I've also made two tutorials so far:

[https://www.youtube.com/watch?v=Ld-
mbyAGsow](https://www.youtube.com/watch?v=Ld-mbyAGsow)

[https://www.youtube.com/watch?v=1vjlEd2-bJQ](https://www.youtube.com/watch?v=1vjlEd2-bJQ)

I could be happier with the tutorials, but they do illustrate the point of the
plugin.

I created the licensing infrastructure and the website, and am trying to
launch it as a product so I'd really appreciate feedback.

If you do a lot of data manipulation in Excel and you're good with SQL do give
it a try.

Thanks! Antonio

------
baaron
I just downloaded it, and I have to say... this plugin is really cool and
extremely powerful.

Two things:

1) It needs to be more clear that your data needs to be in a "table" for the
plugin to work. Unless I'm mistaken, the only place that is stated (except for
the tutorials) is embedded within the screenshot on the homepage. Add a help
or FAQ page, and highlight this point!

2) I'm not a fan of the personal license having restricted use in a domain
environment. I have a domain controller in my house, and many of my IT-
professional friends do too. My house is not a commercial space. I like the
software, and I would definitely pay $28 for a license, but I think $50/year
is too steep to be able to use this at home.

~~~
anakic
Hi baaron!

I hear you, on both points. Point #1 - that's true, I never mention that the
data needs to be in a table on the site, and it's a really good point which I
will address ASAP.

Point #2 - I didn't think many people would have domains at home, so I planned
on dealing with this issue on a case by case basis. My plan was mostly to
concentrate on commercial users and have a relatively cheap license for home
use, the domain was just a handy way to try to distinguish between the two.
I've already had some complaints on this, so I might change this policy (any
suggestion on an alternative?).

In any case, this shouldn't be a problem, please contact me
(antonio@thingiequery.com, or via the contact form) before buying a private
licence, and we'll sort it out:)

And thanks for the kind words, I agree about it being a cool and powerful
tool:) There will be more cool features coming up, but the core is in pretty
good shape already I think, I'm happy with it, and it's a really satisfying
project to work on.

Antonio

------
jpau
Hi Anakic, this looks great!

I've been having to use SQL in Excel a lot lately, through ADO (VBA). I've
been using it to create prepared operations; users click a button and, for
example, Excel will generate a list of clients that are in two different
Workbooks.

Am I right in saying that the advantage that ThingieQuery has over what's
already in Excel is that a REPL-esque nature allows you to run these commands
on the fly? Can you maybe expand a bit more on the comparison?

~~~
anakic
Yeah, that's a big one. Being able to use it in a REPL-esque manner without
much ceremony is one nice thing. It also has other features you would expect
from a SQL IDE:

\- syntax highlighting

\- code completion

\- partial query execution (select part of the query to execute only that
part)

It focuses on tables instead of sheets, so you don't have to clean up content
around your data, since your data is clearly marked inside tables.

You can combine your Excel data with data from an external database, and
actually use the external database to process the data. If needed you can
easily insert the excel data into permanent tables in the external database.
And you can write query results anywhere in Excel.

It integrates really well with Excel, you can update existing tables from a
result and it will match the table and the results by headers, and only update
what it matches. It won't overwrite any calculated columns or formatting. It
will push content up or down to make room for new data instead of overwriting
it.

Also the SQLite engine that procesess the data is augmented with many .NET
functions as well ("format" function is basically String.Format, "replacex" is
Regex.Replace, and there are other added functions), and in the future I will
be opening it up for the user to add their own .NET functions (imagine using
functions for ldap querying, statistics, math, other specific fucntions from
your SQL commands).

There is a lot of fun stuff for really advanced users, and I'll be making
tutorials and opening it up for customization as much as I can.

Well, I've already written a wall of text so I'll stop here:)

Antonio

------
anakic
Oh wow, comments and upvotes, yay!:) The thread got no attention for two days,
so I gave up and forgot about it, just saw the comments and the upvotes last
night, nice.

------
nbevans
Out of interest, and I've done no research into this yet, how is your SQL
editor/code complete working? Does it use SQLite's engine itself to get the
AST or some such?

~~~
anakic
The SQL editor is a really cool open source library called AvalonEdit. It has
it's own lexer (for syntax highlighting) and you just describe the terminals
via regex and tell it what formatting to apply to each terminal.

As for code completion, it's quite rudimentary currently, it doesn't know
anything about SQL, it just offers all schemas/tables/columns and filters them
as you type. The SQLite dialect of SQL is really well documented and I'm
currently working on implementing an LL(*) parser for it's grammar, so I can
improve autocomplete and the editor with support for:

\- keywords

\- aliases (while handing alias scopes)

\- sub-query columns

\- only applicable columns in the select list, only tables in the from list,
etc...

\- error squigglies

Also, the parser would make it easy to implement auto-formatting.

It's quite a fun feature to implement, but alas it's currently low priority so
I don't expect to have it working in the next month or two.

Antonio

------
fiatjaf
I've watched the videos and this is the most incredible thing I've ever seen
(ok, probably not, but it is amazing). It is really strange it didn't get more
upvotes.

~~~
anakic
Thank you for the kind words, and I agree that it's pretty amazing:) Perhaps I
should have posted text instead of a link, or a link to the tutorials (they're
not on the website yet). It didn't get any attention for a couple of days, and
I had already forgotten about it, just saw the comments last night.

