QueryCell – SQL in Excel


QueryCell is an Excel add-in from Oak Focus Software that gives Excel users the ability to use SQL in Excel. Version 1.4 was released this month and lead developer Sam Howley gave me the chance to take a test drive, so I downloaded it and tried it out.

Before I get into what I thought of it, I know that Dick Kusleika at Daily Dose of Excel reviewed QueryCell last year. A number of questions were asked in comments then around what QueryCell provides that isn’t already available in Excel. It’s a fair question – after all, SQL in Excel is not a new concept – either using VBA and ADO, or MS Query. Those are all great tools for querying data from and in Excel, after you get to know how to apply them. After spending some time using QueryCell, I think the standout difference can be summed up with two pairs of hyphenated words: single-click and user-friendly. From the worksheet to the SQL editor: one click. No need to select the data source, specify the driver or write a connection string. One click and QueryCell opens up, auto-detects all data regions in the active workbook(s) and has the SQL editor ready to go.

Also, QueryCell provides two features that really sold me: Table DDL and the SQL Insert Generator. The Table DDL window displays a CREATE TABLE statement for the selected table:

Checking the ‘Use Custom Table DDL’ checkbox allows you to edit the statement, which is handy: if a column in the table has a reserved word (like Date or Case) as a title, QueryCell will auto-assign a generated name to replace it in the DDL. This is necessary in order to allow the Firebird database that QueryCell uses at the backend to accept the data. Also, the data type assigned to the columns is automatically set to VARCHAR(512), unless the columns have suffixes which identify them as another type, so these may need to be changed.

The SQL Insert Generator window is available on right-clicking inside a table on the worksheet, and generates an INSERT statement for each row in the table:

The ‘View Generator SQL’ button allows you to see the select statement that’s used to generate the INSERTs, which can be modified and re-run if required. The statements can then be copied to the clipboard, or saved to a script file.

What is it that makes these features so useful? Both the Table DDL and the Insert Generator appeal to me as tools to assist with migrating data from Excel to a database, which is a battle I’ve had to fight on more than one occasion.

Some other cool features: both the SQL Editor in the main window and the Insert Generator feature a ‘Format SQL’ button, which is nice. Column names are auto-completed in the SQL editor – also very nice. There’s a test data generator, which is cool for getting development going without needing to have data in the right shape beforehand, and a well-developed SQL/QueryCell tutorial.

Overall impressions: I’m really taken with QueryCell. It delivers some great functionality which I see as hugely useful in the context of what I do with Excel day-to-day. The UI has a very polished and responsive feel, and makes good use of space. It’s also very intuitive and easy to use, which as I said earlier is one of the key differences that sell it to me over existing alternatives. It’s modestly priced at $45 US, and offers a fully-functional trial download which is good for 30 queries before expiry. Well worth giving a whirl, which I thoroughly recommend.

Special Offer: Sam has generously provided me with 4 free licences to give away. The first four to email me (address at About Me) with “QueryCell” in the subject line will get a licence key sent back to them – please note I won’t be able to reply straightaway, but you can expect a reply within 24 hours.

Edit: Thanks for all the emails, people – the four licences have now all gone to good homes. I’d encourage you to download the trial anyway and see what you think…

Advertisements

3 thoughts on “QueryCell – SQL in Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s