Overview
SQL+ is an AddIn for Microsoft Excel. It enables the use of SQL in Excel.
Installation & Removal
Installation
- Make sure that Excel is closed.
- If you have already installed a version of SQL+, this must be uninstalled first.
- Download the SQL+ add-in from our Website.
- Enter your email address and optionally your name and company and press “Submit”. Then check your mailbox and, if necessary, your spam folder. Finally, click on the download link.
- After the download is complete, click on the folder icon next to the downloaded file. Depending on which browser you use, this view may differ. Alternatively, open explorer and then the download folder or, if you have created a user-defined folder for your downloads, this folder.
- Right click on the zip folder and then click on "Extract All".
- Click on "Extract".
- Open the newly created folder with a double click.
- Execute the file with the file type "vsto" with a double click.
- Press "Install".
- Once the installation is complete, click "close".
- Now open Excel. When you open a workbook, you should see a new ribbon labeled "SQL+". Click on it to use SQL+.
Removal
- Click on the search button on the taskbar and search for "add or remove programs". Then click on the first search result.
- Search for "SQL", click on the three dots and then press uninstall.
- Click again on "Uninstall".
- Click on "OK".
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. If you use a folder with csv's as input you have to enter full file name with ".csv" in square brackets.
- 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".
- [...]
- [...]
Special Functions
[...]
[...]
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.