Show HN: I created an Excel plugin for using SQL inside Excel | Hacker News Reader

Show HN: I created an Excel plugin for using SQL inside Excel

thingiequery.com

13 pointsanakic10 comments

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

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.

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

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?

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

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.

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?

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

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.

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.

Reply on news.ycombinator.com