Table of Contents

Description

The SQLforExcel-Add-In permits the user to execute read-only SQL statements on Excel workbooks, CSV files, MS Access, and from the common database systems Oracle, SQL Server, Sybase, and PosgreSQL. Depending on the outcome of a query, either a single value or a table is returned.

To allow for a flexible handling of multiple data sources, a dedicated Data Source Manager is included (accessed via the SQL menu). Also, for convenience, an easySQL editor is provided, featuring syntax highlighting and a tree view of the data objects (tables and columns) available in the selected data source.

The execution of the SQL query is realized by calling the spreadsheet function SQL(query,(optional) useHeader,(optional) ClearContentsOrFormats,(optional) datasource)
either from the easySQL editor or from a cell in the Excel Workbook.

Important Notes

  1. If the statement returns more than one entry, the SQL function technically returns only one value into the calling cell and triggers a macro which returns the resulting entries. Be aware that this action clears the Excel undo-stack as it is the usual case for the execution of a macro. Hence, it is not possible to undo the execution or any operation that was performed on the workbook before.
  2. If you use the local workbook as data source, you have to save the workbook before using the SQL function on it.
  3. The result will be written into the cell where the SQL function is placed and to the right and to the bottom of that cell if more than one column or more than one row is returned. Please make sure, that these cells do not contain important information since the SQL will possibly overwrite them.
  4. In case of automatically triggering of the SQL function only the cell from where the function is called is updated, but the actualization of the further entries is prevented and happens only by manually execution of the SQL function.
  5. It is not possible to select more than one data source in one SQL Statement. Nevertheless, it is possible to select data from more than one data source within one workbook (see the description to p4 in SQL function parameters)

General Usage

  1. Within any given worksheet, use the Add-In function SQL like you would use any built-in Excel function (such as SUM, MIN, MAX, ...).
  2. The result will be written into the cell where the SQL function is placed (the same way as for any built-in function) and to the right and to the bottom of that cell if more than one column or more than one row is returned.
  3. Open the SQL ribbon and click on the “Data Source Manager” icon to select a data source. The default source is the active workbook itself (see below).
  4. Click on the easySQL icon to exploit the features of the easySQL editor (see below) and perform a SQL query on the actual selected Data Source.

Data Source Manager

Open the SQL Ribbon and click the “Data Source Manager” icon. If you would like to define a new data source, go through the following steps using the dialog items in the right panel:

  1. Select a “Connection Type” (ExcelWorkbook, CSVfile, Access, Oracle, SQLServer, Sybase, PostgreSQL).
  2. For the Connection Types ExcelWorkbook, CSVfile, or Access, simply select the desired data file. In all other cases (Oracle, SQL Server, …), use the (DSN) of a corresponding ODBC connection you have defined using the Windows ODBC administrator or directly type in an appropriate connection string.
  3. If you want to connect to a database through its DSN, please enter your username and password. To save the username, activate the respective checkbox.
  4. If you want to connect to a csv-file, the Add-In will ask for the “separator” and the “decimal symbol”. These are required to correctly process the data in the csv-file.
  5. To save the new connection for later use, specify a Connection Name on the top of the right side (limited to 20 characters, must not contain a comma).

When a connection has been named and saved, it will be displayed in the left panel of the Data Source Manager. If you want to use a saved connection, simply double-click on it. The connection will then load and its properties will be displayed on the right side of the panel.

Your Saved Connections are stored in a .txt file, which is located in the same folder as the .xll of the Add-In itself. If you transfer your Add-In to another folder and wish to keep the connections, please make sure that the .txt-File is transferred as well.

To test a defined Data Source connection, press the corresponding button.

To activate a selected Data Source, press the OK-button. That will also close the Data Source Manager.

If you use a DSN data source requiring a password, this password will be stored as long as the Data Source is selected or, if it is a Saved Connections for the duration of the current Excel session.

You can remove the currently highlighted connection from the Saved Connections using the “Delete Connection” button.

easySQL

In some cases, it will be more convenient to use the easySQL dialog rather than using the SQL function directly within a spreadsheet.

To use easySQL, open the SQL menu, then click on the easSQL icon. A dialog will open.

In the left panel, you will find information on the schemata, tables and columns of the selected data source (defined in the Data Source Manager). Depending on the selected data source (particularly for larger databases), it may take a couple of seconds to open the easySQL editor.

In the “SQL Editor” (right panel), you can enter or amend your SQL. Please start directly with “select …” and not with “ =SQL(…

Depending on the type of your defined Data Source, please make sure to properly adapt your Select statement. The tables can be accessed by their names listed in the tree view on the right side, however, in case of queries on Excel, the suffix “$” must be appended to the table name and the name an suffix must be enclosed in square brackets. Examples are provided in the chapter "Data Source Specifics".

The third part of the easySQL on the bottom of the right side contains the specification of the output properties and the following four buttons:

  1. Refresh: Reloads the information about the Data Source on the right side and actualizes the available output sheets in the current excel workbook.
  2. Execute SQL: Takes the input parameter (SQL query as written in the editor, columnHeader property if checked in the corresponding checkbox, clear contents and formatting from the further checkboxes, Data Source as displayed on the right side) of the SQL-function and pastes the formula to the selected output cell where it is executed.
  3. If you want to connect to a database through its DSN, please enter your username and password. To save the username, activate the respective checkbox.
  4. Cancel: Closes the easySQL.
  5. OK: Combination of “Execute SQL” and “Cancel”.

SQL function parameters

The new SQL function contains 4 parameters and is called from an Excel cell by =SQL(p1; p2; p3; p4)

p1: This is the SQL-Statement. The statement has to be double quoted. For string values inside your statement use single quotes (e.g. WHERE name = ‘Miller’), otherwise an error will occur. For further information and some examples see the chapter “Data Source Specifics”.

p2: This is an optional parameter to show the column names of the table returned by the SQL (0=no, 1=yes, default is 0).

p3: This is an optional parameter to clear contents and/ or formatting in the cell where the SQL function is placed as well as to the right and to the bottom of that cell (0= keep old content, 1= clear contents and formatting, 2 = clear contents only, 3 = clear formatting only, default is 0). p4: This is an optional parameter to use a Data Source different from the one selected in the Data Source Manager. This allows you to select data from multiple Data Sources in one Excel sheet.

Data Sources contained in the list of Saved Connections in the Data Source Manager may be used. The name of such a defined data source has to be enclosed by “ ”. For instance

=SQL(p1; p2; p3; “ORADB”)
where “ORADB” is the name of a data source defined in the Data Source Manager.

Furthermore, the full path of an Excel-, csv- or Access-file may be used
=SQL(p1; p2; p3; “C:\Users\Documents\myExcel.xlsx”).

The third option is to use “this” as keyword for the active Workbook =SQL(p1; p2; p3; “this”)

Data Source Specifics

The specific syntax of SQL Statements depends on the Data Source where the statement is executed. In general, standard ANSI SQL may be used across all supported database systems. In addition, you may exploit the specific functions of the selected data source type (e.g. Oracle analytic functions). This is because the SQL is executed on the selected database system, not locally within Excel.

However, particularly for Excel and CSV Files, the syntax for accessing the tables deviates slightly from the common known SQL syntax which might lead to unnecessary errors and is therefore adressed below.

Excel files:
Elementary SQL syntax for Excel tables:
SELECT column1, column2 FROM [Sheet$]
where Sheet is the name of the worksheet against which the SQL is to be run.

  • Please use doublequotes ““ to enclose the SQL string
  • Please use “$” as suffix to the worksheet name and enclose the name and the suffix in square brackets
  • Example 1: =SQL("SELECT Count * As N FROM [Data$]")
  • Example 2: =SQL("SELECT Name, Street from [Address$]")
Note that you may use the full power of the standard SQL language for read-only statements, including inner and outer joins and aggregate functions.

To select data from a defined range within a worksheet (e.g. from the first two columns and the first four rows or a named range), append the range (name of the range) to the sheet name like in the following example:
=SQL("SELECT * FROM [Data$A1:B4]").

CSV files:
Elementary SQL syntax for CSV files:
SELECT column1, column2 FROM CSV-file
where CSV-file is the name of the CSV file including the suffix.
Example: =SQL("SELECT * FROM example.csv")

Note that the reason for this syntax is that the CSV-file itself corresponds to a single table.