Overview
SQL+ is an AddIn for Microsoft Excel. It enables the use of SQL in Excel.
Usage
When you open Excel and open or create a workbook, you should see the “SQL+” ribbon in the menu bar. Click on it to use the functions of SQL+.
Ribbon
- Button to open the SQL Editor, the core functionality of the AddIn.
- Convenience feature of the AddIn to define the general format of the generated results.
- Convenience features of the AddIn to define the format of text, numbers and dates of the generated results.
- [...]
SQL Editor
- Press the "Input XLS" button to select a workbook as input. The currently open workbook is selected as default. To reselect this after having selected another one press "[This Workbook]". Press the "Input CSV" button to open the input csv dialog. Here you can select your parameters for your csv files. Press "Select Folder" to select a folder that contains the csv files. The folder acts like a workbook and the files like worksheets. When all parameters and your folder are selected press "Done".
- Here you can select a template from the dropdown menu.
- [...]
- Here you can enter your own SQL query in the field or edit manually a template. When you use a xls as input the input sheet must be entered with a "$" after the name and in square brackets. You can also define a specific range for your input sheet after the "$" (e.g. sheet name: Test | use in the SQL editor: [Test$A1:G100]). If you use a folder with csv's as input you have to enter full file name with ".csv" (without a "$") in square brackets (e.g. csv file name: Test | use in the SQL editor: [Test.csv]).
- Here you can either replace placeholders defined by the selected template with your own values or define your own replacements.
- Select your output sheet from the currently open workbook from the dropdown menu.
- Optionally, you can choose whether the header should be included, the output sheet should be cleared or the output should be transposed.
- To execute the SQL query with the selected parameters press "RUN".
- [...]
- [...]
Enable Trust Access to the VBA Project Object Model
This setting is required to allow the add-in's macros and VBA scripts to function properly by granting access to the VBA project programmatically.
Special Functions
COR
[...]
COV
[...]
CHINV
[...]
REG
[...]
SQL Patterns
In the SQL Template dropdown, you may select from a list of SQL design patterns. These patterns employ placeholders (values in square brackets) for table and column names and various parameters. To adapt a pattern to your particular cause, replace its placeholders with your own values using the right pane of the SQL editor. You may also define replacements for additional parameters or clauses and apply them to a selected template or a self-defined statement.
Data Quality
- DQ.UNI: This pattern checks whether a given key (column or list of columns) is unique and, if it is not, lists all duplicates.
- DQ.REF: This pattern checks referential integrity, i.e. whether a referenced table has all the values of a key that exist in the referencing table (e.g., a PRODUCT table listing all the PRODUCT CODES for which transactions are recorded in the SALES table). If violated, all keys missing in the referenced table are listed.
- DQ.SET: This pattern compares sets of objects in two tables (each defined by a key, i.e., a column or list of columns). It returns three sets: objects that exist in the first but not in the second table, objects that exist in the second but not in the first table, objects that exist in both (the intersection).
- DQ.DEF. This pattern compares an enumerative object property across two tables (e.g., object being “employee” and property being “address”) and lists all detected differences.
- DQ.VAL. This pattern compares a numeric object property across two tables (e.g. object being “financial asset” and property being “market value”) and lists all differences above a given absolute or relative threshold.
- DQ.OUTL: This pattern compares a numeric object property across two tables and identifies statistical outliers.
Time Series
- TS.RRET: This template calculates the relative change in value from one point in time to the next.
- TS.MAVG: This template calculates the average value over a defined time window.
Statistics
- ST.DESC: This template summarizes key statistics of a specified column, including counts, distinct values, and min/max values in descending order.
- ST.HIST: This template categorizes values into buckets, calculates the count of entries in each bucket, and computes the proportion of each bucket relative to the total count.