Introduction / Quick start guide
This is the place to start using SQLinExcel. Below you can find:
- An overview of the Excel sheet,
- instructions to get started,
- simple examples of data being transferred from an SQL server to Excel and
- a brief introduction to other relevant feautures.
SQLinExcel is simple and flexible at the same time. You can get started with the basic functionality within minutes and explore the advanced functionalities as you need them.
The core functionality of SQLinExcel is the easy and flexible way to transfer data from an SQL server to Excel. To retrieve/update data simply press the “Run SQL” button. This will execute the SQL against the specified server and retrieve data stored in any temporary tables starting with “#_X_”, where X is a letter indicating how the data is written to Excel. For example the data stored in a temporary table named #_T_DemoTable will be transferred to a plain spreadsheet named “DemoTable”. When the SQL is run again, the data in Excel will be updated. Another option is to write data to a pivot table, for example #_P_PivotDemo. Subsequent runs of the SQL will refresh the pivot table content. The pivot table allows importing more than the approximately 1 million rows available in a regular spread sheet.
- Download SQLinExcel.xlsm and open the file in Excel (remember to enable macros)
- Fill in the relevant server name
- Insert a valid SQL statement in the text box (including for instance "into #_T_DemoTable" in the select statement" (see examples below)
- Press "Run SQL"
Pivottables - #_P_xxx
The following statement (containing “into #_P_DemoPivot”) creates a new sheet containing a pivottable with the data from the temporary table #_P_DemoPivot on the server. The pivot table has been arranged to show its content.
The pivot can of course contain much more data – more than a million rows, depending on the available memory. Running the SQL again will update the content of the pivottable, while maintaining the selections and filters. You can choose any name for the Excel sheet (excluding certain characters), as long as the name of the temporary table starts with “#_P_”.
Tables - #_T_xxx
The following statement (with “into #_T_DemoTable”) creates a new sheet containing the data:
Running the SQL again will update the content of the table. If the number of rows in the table changes, it will automatically delete the rows with old data.
By default, the table data is from cell A2 and onwards, and headers are written in the row above.
As shown below, it is also possible to transfer multiple data sets to one sheet, to specify the first cell to write data in, and to specify whether or not to write headers:
Omitting the “_H” from “#_T_DemoTable_D2_H” will result in no headers:
SQLinExcel offers many more features than those above. You can find more information from the user guide menu or by following the links below:
In addition to copying data from an SQL server to Excel tables or pivots as indicated above, it also provides the option to append data to an Excel table or to write data into a message box. You also have the option to search and replace strings in the SQL code to affect data selection or any other functionality of the SQL.
You can use information already in your Excel workbook in a server query by referencing the cell range from your SQL code by using a "#_R_" prefix followed by a sheetname and a starting cell.
You can duplicate a sheet before or after running the SQL (e.g. using another sheet as template) or you can save selected sheets in separate Excel workbooks (e.g. for backup or data distribution).
You can copy and/or update all or selected charts - as well as specified cell ranges - from a workbook to Powerpoint. Even if the Powerpoint is edited and charts are moved and resized a subsequent update of the presentation will preserve the chosen size and position.
After running the SQL, you may automatically create an email with predefined text and recipient(s) with as many of the created files you may wish, including the SQLinExcel workbook, Powerpoints, created Excel workbooks and/or created text files.
In the same way, that data sets can be sent from the SQL server to a table og pivot table, it is possible to copy data sets to text files using a "#_F_" prefix. For each text, the filename, column delimiter, handling of NULL-values, character and other parameters can be set get the desired result.
Additional settings relate to the server connection, timeout, forcing sort order on rows i #_T_ and #_F_ data sets, and the option to run an executable file before or after running the SQL.