There is this database somewhere and for each field in the database I
needed to count how many of records had a non-null, non-blank value.
How to do
it using Excel's SQL Query engine
From my blank workbook, hit Data-->Get External Data-->...
From Other Data Sources-->From Microsoft Query...
Tell it From an MS Access Database
Point at your MDB file, point at the table (tblClients in this case),
and pick a couple of fields. In this case, I'm curious about how many
people gave me a contact email by filling in the ContEmail field. Note:
I'm not validating the email address, just curious how many people
filled in the field.
So here, lets just count how many non-nulls came in.
Hit Next, Next, then I'm going to look at the query in MS Query:
Here are the sample results opened in MS Query, except I hid the
actual email addresses:
And what the SQL Query looks like by hitting the SQL button:
All pretty simple, right? So lets get the count by changing the
query. Change the field name to instead count(field name):
and I get a result of 67:
And when I hit File->Return Data to Microsoft Excel:
I get a nice table with one value. This works well for a couple of
values - but I had 600 some-odd fields spread across 8 data tables and I
wanted to do this for every single one of those fields. Now there are
all kinds of ways to refresh this query, and I even wrote some VBA that
will auto-refresh every query in a workbook, but this was before that
pretty little "Refresh All" button existed.
So my first thought was copy / paste and edit the query to point to a
different field, like ContName (Contact Name)...
Highlight->Copy->Paste->Right click->Table->Edit Query, then change the
query to count ContName:
Ok, so I've got 67 contact emails and 74 contact names. I tried, but
found no way to reach into that SQL query and automate changing the SQL
statement. Plan B was to have the query engine read the query from a
cell in the table, which was also a dead-end.
I needed this same functionality for another
project a few years ago, built my own tool that does the queries, and
have been using and enhancing that tool ever since. What you are about
to see is a very simple application of this tool.
Here is what I really wanted to happen:
See the field and table names? I want to automatically populate the
next column with counts from a database.
With those two bits of data, I can concatenate up a SQL statement
that will give me the results like this:
I could have counted non-nulls, but there were some spaces in some
fields - so I ended up with the query you see above and listed here: select count(ID) from
LEN(TRIM(ID)) > 0. That SQL code is created by a few simple string
concatenation operations in Excel, so in this case I'm using Excel to
write my SQL queries for me! Way easier than doing that for 600 fields.
This formula can be copied down all 600 some-odd rows and just like
that I've got all my queries written and ready to execute. The Excel
formula automatically builds the correct SQL statements. What I needed
was code that would seek out all those queries and put the results
somewhere else in the Excel spreadsheet.
My Excel +
SQL tool does just that!
The user sets up as many
queries as you need in your spreadsheet. I picked the prefix "sql:" but
that is a configurable parameter. The code goes through all the
worksheets and all the rows / columns you tell it, and it goes around
looking for that prefix. Wherever that value is found, the tool runs the
query and takes the results and puts it back into the worksheet at an (x,y)
offset from where the query exists. The whole function runs from a macro
and when it is done all the numbers in your spreadsheet are updated!
So here are some sample results:
And here are some results from other tables, and again - all these
queries were auto-generated with some very simple Excel concatenations
which took all of 5 minutes to put together.
The tools has not been commercially released yet, but if you are
interested in this functionality please contact me
directly and we can discuss licensing the tool for your specific
purposes. Automating work like this saves many hours, and every year
after that I've used the same functionality just adds to the savings!
If you found this helpful or not, please send me a brief email -- one
line will more than do. If I saved you a bunch of time (and thus $$),
and you wanted to show appreciation, sending a little love via PayPal or
an Amazon gift card is also very much appreciated!
(Experiment: October 2013)
I'm playing with
integrating Facebook's like button - if you liked this article and have
a facebook account, please show some love and hit the button below.
I can be reached at:
das (at-sign) dascomputerconsultants (dot) com
(C) 2011 DAS Computer Consultants,
LTD. All Rights Reserved.