Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

ThingieQuery: a SQL plugin for Excel

thingiequery.com

Post reply on HN
Print view View original

31–40 of 65 posts

Re: ThingieQuery: a SQL plugin for Excel

#31

Post by anakic »

orf wrote: post #28

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

Re: ThingieQuery: a SQL plugin for Excel

#32

Post by anakic »

orf wrote: post #28

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

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.

Re: ThingieQuery: a SQL plugin for Excel

#33

Post by tokenizerrr »

anakic wrote: post #31
orf wrote: post #28

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 an…

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?

Re: ThingieQuery: a SQL plugin for Excel

#34

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

Re: ThingieQuery: a SQL plugin for Excel

#35

Post by orf »

tokenizerrr wrote: post #33
anakic wrote: post #31

Earlier quoted context omitted.

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 an…

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?

Then they wouldn't have the functionality that the tool provides I guess?

Re: ThingieQuery: a SQL plugin for Excel

#36

Post by calgaryeng »

degenerate wrote: post #20
anakic wrote: post #6

Earlier quoted context omitted.

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.

Haha - you have clearly never worked for a 'real' company :)

Re: ThingieQuery: a SQL plugin for Excel

#37

Post by anakic »

tokenizerrr wrote: post #33
anakic wrote: post #31

Earlier quoted context omitted.

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 an…

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.

Re: ThingieQuery: a SQL plugin for Excel

#38

Post by anakic »

ergest wrote: post #34

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 feature…

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

Re: ThingieQuery: a SQL plugin for Excel

#40

Post by tokenizerrr »

anakic wrote: post #37
tokenizerrr wrote: post #33

Earlier quoted context omitted.

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, bu…

Excellent, thank you for the reply.
Post reply on HN