SQL Shortcut: How to use Excel to Create Custom SQL Queries

NoShowSelectedIs there anything more boring than the “gruntwork” of selecting individual records from the list view in Aspen so that you can “Show Selected?” Have you ever clicked 50 records and lost your work when you viewed a student detail page to make sure you selected the right record? A light bulb went off in my head the day it occurred to me that an Excel function could concatenate fields in a spreadsheet to create a SQL query from a list of any length.  Since then, I have used this method to create queries for as few as 5 records, and as many as 4500, based on such elements as local/state ID, course IDs and conduct incident numbers.  This has been particularly useful when troubleshooting state reports. To adjust the length of the query, all that is needed is to adjust the number of rows in the Excel sheet.

Download the Excel workbook at the end of this entry, and then follow the steps below to utilize an Excel sheet as a “Query Builder” to create custom SQL statements. I have included sheets for LASID, SASID, STD NAMEVIEW, STAFF LOCAL ID, and STAFF STATE ID, but you can expand this to include other data elements following the same model.

excel sheet

Example:  Use a validation error report to select multiple students from a list by state ID.

  1. Open your error report or list of sasids in Notepad++.
  2. Use “column mode” to isolate the state ID for all students in the report. (To access column mode, in Notepad++ hold down <Alt> key and then click and drag the cursor to highlight the entries in the column that you wish to select.)
  3. Click < Ctrl >< C > to copy the column of highlighted student IDs.
  4. Open the Query sheet in Excel and Paste the state ID into column B.
  5. Copy the SQL from column C.
    excel query
  6. Paste the SQL from column C into the Options > Query > >Advanced SQL box in Aspen and click “Run” to run the query and display your results.

advanced sql

Such queries, which identify particular records according to a specified data element, can also be used to create a snapshot in areas of the program where queries are enabled, but snapshots are not. To create a “snapshot” from the query in locations where snapshots are not available, simply save your query as you would any other, named in a way that is meaningful and indicates that it contains static info. If you share this out to other users and add it to the filter menu, they can use it to isolate the records that they need to validate.

You can test this out by copying and pasting a couple of your own SASIDs in the SASID sheet. Let me know if these directions are not clear… Hope you agree that this is a time  saver! Click here to download the Excel workbook for the  Query Builder.

One thought on “SQL Shortcut: How to use Excel to Create Custom SQL Queries

  1. Lynn Rowan Post author

    Name: Jim Dillon
    Email: jdillon@cpsed.net
    Comment: THIS IS BRILLIANT!

    Thank you so much for sharing this! I’ve been doing exactly as you describe! I try to make it easier by sorting by LASID or SASID, but after a bunch I get a little sloppy and lose some or all of the list, plus it is madly time consuming!

    Great idea, thanks again, and whoever is responsible, this is a terrific idea for an Aspen sharing center!

    Reply to Jim:
    Thanks, Jim. I am posting your comment to the page because I messed up… I just fixed the entry to allow comments to publish. I am new to WordPress and don’t quite have the details down. Wanted to make sure Brian Ciccolo saw your kudos for the site!

    As a side-note, I generally sort my error list by error and then create multiple queries named and grouped by error message. Really speeds up the work of troubleshooting.
    Lynn

Leave a Reply