UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> MS Query    
MS Query

Contents

Excel MS-Query

Using MS-Query to Import Data into an Excel Spreadsheet

MS-Query is a mechanism to import data from a data source into Excel. The data source can be an ODBC connection to an Oracle or other database, MS-Access, ASCII Delimited file or even a tab in another spreadsheet.

One of the main issues with MS-Query is that there are so many options to it and so many different ways to use it depending on the databases and queries you choose to use. This wiki will address the most generic way of using it which should work in almost every instance.

This wiki will start with a demonstration to import data from a table called Model Master. (OPS$RMPAUTH.RM_MODEL_MASTER) in an Oracle database called RPTPRD01. Other data sources can be set up similarly. Examples of these databases will be added as the wiki is expanded, however the Oracle example is fairly generic and should provide good information for the other data types as well.

To use MS-Query, the database must be one of the existing types. MS-Query comes with some built-in types such as Excel and MS-Access. In the case of an Oracle database, the database must be set up as an ODBC connection. All ODBC connections are recognized.

The generic example in this wiki uses VB code to import the data so it is important that the book be saved as a macro-enabled workbook (*.xlsm or *.xlsb).

Making the Connection

To start the process, go to Data -> From Other Sources -> From Microsoft Query.

This action brings up the Data Source screen.

image: Qq001.jpg

Turn off “Use the Query Wizard to create/edit queries.” It is a useful tool if you are working with a single table, but it gets in the way for more generic or complex queries.

Scroll down to the desired database and select it.

image: Qq002.jpg

Enter in your credentials and click OK.

This action brings up the list of tables. It is possible to use MS-Query to build the query from the user interface. The interface is similar to MS-Access. However this wiki presses on with a more generic approach.

Close the Add Tables dialog box without selecting a table.

image: Qq003.jpg

After closing the dialog box select SQL.

image: Qq004.jpg

The main interest at this point is to establish a SQL connection to the database. What gets queried is not important now, so a brief SQL statement that will execute quickly is all that is needed.

image: Qq005.jpg

Click OK and the query will run and show results. This query is simple, and can be displayed graphically as shown below. More complex queries might not be displayed graphically. This is OK. The only difference is that you can pass parameters directly to a graphically displayed query.

To pass a parameter to a non-graphically displayed query requires simple VB code. This is the most generic case and it will work all the time, so it is the method presented here.

Click on the Exit Door Icon to return the data to the sheet.

image: Qq006.jpg

Select the sheet and cell to which you want the data returned and click OK.

image: Qq007.jpg

The data will be returned to the selected sheet and cell in Excel Table Format.

image: Qq008.jpg

The data will come into a table named for the query. Change the table name to something meaningful like Table_Model_Master.

Once the data connection is established, it can be configured.

Select Data -> Connections

image: Qq009.jpg

This action brings up the Workbook Connections dialog box.

image: Qq010.jpg

  The Usage tab is selected by default. Consider clicking off Enable Background Refresh, particularly if the data refresh has to be complete before other actions are taken on the spreadsheet

image: Qq011.jpg

Note some of the other options that are available.

Next, select the Definition Tab.

image: Qq012.jpg

Two major items are displayed here: the Connection String and the Command Text.

There is an option to save the password. This will keep users from having to enter the credentials each time the query is run. There are two words of caution here if the workbook is to be shared.

  • The query will be run under the originator’s credentials
  • The password is visible in plain-text in the connection string (no encryption).

So make sure the workbook is shared only with trustworthy people.

Change the name of the query to something meaningful like qry_model_master. When changing the name of the query, the query will be rerun, which is why it is a good idea to make the initial query something that runs quickly.

You now have a generic workbook that can be used to run any query against the specified database.

Making the Query

The following is a method to develop SQL code “on the fly.”

Create a tab called “SQL.” In Cells A2:C2 add the following titles:

  • Raw SQL
  • Translated SQL
  • Check

Select the range and convert it into a table. Change the table name to Table_SQL.

Now it’s time for some VB “magic”

  • Press ALT F-11 to open the VB Editor.
  • Click on Insert -> Module.
  • Open the module and copy in the code at the end of this document.

Develop the SQL code you wish to use and troubleshoot it in a tool such as WinSQL. SQL code generated by MS-Access can be used, but MS-Access specific functions like NZ() cannot be used. In general, if the query runs in WinSQL, it will run in MS-Query.

After developing and testing the SQL, copy and paste the SQL code into Cell A3 on the SQL page. The table will expand to contain all of the SQL code. See the caveats section at the end of this document.

The objective of this example is to get the models whose launch date is within the past X days where X is a parameter to be passed at run time.

The code is as shown in the Raw SQL column below.

image: Qq013.jpg

Use the SUBSTITUTE or nested SUBSTITUTE commands if more than one parameter is passed. In the Translated SQL column formula: =SUBSTITUTE(A3,"&NUMDAYS",MyNumber) accomplishes this. MyNumber is a named range on the worksheet and the value is entered manually.

In some cases named ranges like Start_Date and End_Date can be used and populated with formulas like TODAY()-1 and TODAY()-31. These can be formatted with the TEXT formula to suit the date format expected by the SQL statement.

The Check Column is strictly for QA purposes. It checks where parameters (using an ampersand - &) exist to make sure the substitution is done correctly. The formula is: =ISNUMBER(FIND("&",A3)). A conditional format on the column shades the cell green when a substitution is needed.

In Cell B1 enter the following formula: =SuperCat(Table_SQL[Translated SQL]). Select this cell and give it the name of MySQL. This cell contains the SQL code (Command Text) concatenated into a large string.

In the code below, the only thing you have to change is the Connection Name and Range(“MySQL”) should you have more than one query in a workbook.

To execute the code, you need to run SubSQL. Running SubSQL substitutes the command text in the connection definition with whatever is in the MySQL Cell.

In this case, it substitutes the simple SQL that was used to set up the connection with the SQL in the table. In other words, it re-writes the command string.

The implication of this is that this spreadsheet can be used as a template. Whenever a spreadsheet that runs any query against RPTPRD01 is needed, a copy of this template can be used. Just change the Raw SQL column to reflect the new query. Change the connection name, tab name and table name to reflect something meaningful.

Working With Returned Data

Once the data is presented on the target sheet, right clicking in it will bring up a number of menu options.

The table option has several choices relevant to the data.

image: Qq014.jpg

External Data Properties: brings up a dialog box

image: Qq015.jpg

Shown are the defaults. Note that Excel settings will override query settings, so Preserve column sort/filter/layout and Preserve cell formatting should be looked at.

Edit Query: brings up the user interface to edit the query.

Parameters: If the query can be displayed graphically and parameters are specified, then this menu selection is active. Clicking it brings up a dialog box with three choices:

  • Prompt for the parameter
  • Always use a specific value for the parameter
  • Use the information from a cell on the worksheet for a parameter

Unlink from Data Source: breaks the connection with the data source. The table is now static.

VB Code

CODE

Sub SubSQL()
Dim ConnectionName As String

ConnectionName = "qry_model_master"

ActiveWorkbook.Connections(ConnectionName).ODBCConnection.CommandText = Range("MySQL")

ActiveWorkbook.Connections(ConnectionName).Refresh

End Sub

Function SuperCat(MyRange As Range) As String
Dim cl As Range
Dim SuperString As String

Application.Volatile

SuperString = ""

For Each cl In MyRange
   SuperString = SuperString & cl.Value & " "
Next

SuperCat = SuperString

End Function

Graphical Queries

If the query can be displayed graphically, then it will appear like this:

image: Qq016.jpg

Graphical queries have the advantage that they can talk with Excel directly. There is no need to use VB code to rewrite the SQL statement.

The criteria selection is there because of the where clause in the SQL statement. If there is no parameter specified in the where clause then you can make the criteria selection visible by clicking on the eyeglass-and-funnel icon. You can then use the dropdown list for the criteria field, or drag and drop a field from the field list.

You can also drag and drop field names into the bar for display on the report.

To prompt for a parameter, enclose it in square brackets [] such as [Launch Date].

image: Qq017.jpg

Now when the query is run, it will prompt for the date.

image: Qq018.jpg

When the exit door is clicked the data is returned to the Excel page.

image: Qq019.jpg

Right clicking on the data and selecting table brings up the sub-menu with Parameters active. Clicking on this option brings up a dialog box.

image: Qq020.jpg

By default, this box comes up with “Prompt for value using the following string.” The second option allows to use a fixed value, but you might as well “hard code” that into the SQL.

It is the third option. “Get the value from the following cell” that has the most value. In this case, Cell E2 on the control panel sheet is selected. Cell E2 contains a formula and a format to make sure the date is in the correct format for the Query.

Control Panel
image: Qq021.jpg

Cell formulas are:
B2 =TODAY()-180
C2 (No formula)
D2 =IF(ISBLANK(C2),B2,C2)
E2 =TEXT(D2,"dd-mmm-yyyy")

This is a fairly typical setup that computes a default date for the query with a provision to override it. The parameter is formatted for the query.

Bugs and Other Issues

This method requires that the lines of SQL code are concatenated into a big SQL string, so if a line begins with a comment (--) the comment will show up in the middle of the string and “break” the code. Remove all lines beginning with a hyphen.

There is a bug that prevents aliases from working. Fixing it requires a registry change. http://support.microsoft.com/kb/298955.

The registry change works for all versions of Excel through 2010. It does not work for 2013. The work around is to allow MS-Query to write the connection string and the command string. Then go to the data ribbon, open the connection, copy the command string into notepad, put the aliases in there and copy and paste the notepad contents back into the command string. However, you can also try the technique in the following paragraph.

In some cases, particularly where case statements are used in the SQL code, aliases do not work and you will get the case statement as the column header. This isn’t very useful especially when you are trying to use the column header in a formula or pivot table. The work-around for this is simple: put all table names in parenthesis like so: FROM (OPS$RMPAUTH.RM_MODEL_MASTER) MM.

Finally, when using the VBA substitution program, the apostrophe (‘) AKA single-quote, at the very beginning of a cell in Excel means that what comes next in the cell is to be interpreted as a string. The apostrophe is dropped and this causes an error in the translated SQL. Fortunately, few lines of SQL code start with an apostrophe. However, it can happen and if it does, you’ll get a mismatched quote or some other error. Either put a space in front of the apostrophe or format the Raw SQL column as text.

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 49,426 times.  This page was last modified 21:14, 26 May 2016 by dflak. Contributions by Jack Leach  Disclaimers