Analytics Coding Education STEM

Complementary post to: Pop Quiz [And Solutions With Low Code!]

This post is a continuation of or complementary post to about using Excel and SQL for pop-quizes. Please read that first to get proper context…otherwise, this wouldn’t make much sense.

As discussed in the post linked above, the SQL queries can be saved from Microsoft Query Editor to an external file. It’s really convenient because you can run the same query anytime for different reporting purposes. However, if the source datafile is moved to a different location, running the query will fail epicly!

The data in the query is coming at run-time from the specified external file located in a specific location which is actually embedded in the query file (.dqy) at creation time. If the source file gets moved, you’ll get an ugly message as this:

It basically means that the original datasource is not found. So how do we fix that?

Inspecting the dqy file, I see that it just contains textual information. Open it in a text editor and you’ll see the content something like this:

DSN=Excel Files;DBQ=C:\dir1\dir2\sourcefile.xlsx;DefaultDir= C:\dir1\dir2\sourcefile.xlsx ;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
SELECT states$.StateAbb, states$.StateName FROM C:\dir1\dir2\sourcefile.xlsx .states$ states$ WHERE (states$.StateName Like ‘%ss%’)

StateAbb StateName

As you can see it contains a the full path and source data file name for DBQ parameter, and path to that file, followed by the actual SQL statement that’ll get pulled into the Query Editor if opened for editing. The last line are the names of the columns…so we can tell there are 2 columns named “StateAbb” and “StateName” respectively in the source file.

If these paths and file name specified in this dqy file are not found, it fails. But don’t throw the baby out with bath water! The query can still be salvaged even after you’ve moved the source file. Let’s assume the sourcefile.xlsx is now called differently or in a different location. So make the SQL statements (i.e. the query) work again with the new location, just open in a text editor and change the DBQ value to the new path with file name. And DefaultDir value to the new path. The value for FROM in SELECT statement is optional but it’s a good idea to also update it.

Save the changes into a text file but with .dqy extension. This dqy file can be anywhere and run from anywhere, it doesn’t matter. Rerun the query with the changes, and you’ll be golden again.

Remember to read the related post first to make sense of this post here.

Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino!

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top