How do I troubleshoot when executing the SQL fails?
If running the SQL fails, troubleshooting is best done by pressing the “SQL to clipboard” button and pasting the clipboard content into Server Management Studio or similar. If the SQL-statement does not run in Server Management Studio, it will not be possible to run it from Excel either. Note that inserting data into a table will require all columns in a table to have unique names.
Also remember to include the “USE abc” statement in the beginning of the SQL, where "abc" is the database name, that you would choose in Server Management Studio before running the SQL.
Can I adjust the sort order of a table in case the SQL “order by” statement doesn’t work?
In some cases an “order by“ statement used with an “insert into” does not ensure the correct sort order when reading the table content into Excel. In that case the sort order can be specified in the “Other settings” option:
Can I use other authentication methods than Windows Authentication?
You can specify the authentication method for the server under the “Other settings” option.
Features and limitations
What are the system requirements to use SQL in Excel?
To benefit from all the features in SQL in Excel you should use Microsoft Office 2010 or newer and Microsoft SQL Server 2012 or newer. Most features will also work with earlier versions of Microsoft SQL server. In order to send emails, an installation of Outlook is required. In order to export charts and tables to Powerpoint, an installation of Powerpoint is required.
Can I handle large datasets using SQL in Excel?
The processing on the SQL-server depends on the capacity of the server. Excel only receives the data once it is processed. Due to the size of Excel sheets it is typically not feasible to transfer much more than 1 million rows to a table. Pivot tables, however, do not rely on the excel sheets to store data (whereby the data takes up much less space in the file) and it may exceed 1 million rows.
The total number of rows that can be transferred to a pivot table depends on the memory available. Textfiles (csv-files) generated by the program may exceed 10 million rows or more.
Can I use data from Excel in an SQL query?
Simply refer to the range in Excel by inserting a temporary table in the SQL starting with “#_R_” followed to the sheet name and the starting cell. This could be #_R_DemoTable_D2. This will generate a temporary table in the SQL-server memory containing the specified data. The program will “guess” if the data is text or numbers. If a more specific data type is needed or other column names are needed, it is possible to specify under the “Read from sheet (#_R_)” - settings.
Can I export data to a text file?
Simply type “into #_F_FileName” in the relevant SQL-statement to mark it to be exported to a text file. More settings for creating text files are available under the “create csv-file (#_F_)” – settings. Fill out the SQL-reference and any other setting you wish to set for the creation of the text file.
Can I generate multiple files in one workflow?
It is possible to generate a larger number of files. Simply include all relevant SQL-statements and fill in details for all files to be generated under the relevant settings. Once the details for the first file has been filled out, simply fill out the details for the next file to the right of the first file. This may be repeated as many times as needed. The program will keep on looking further to the right, as long as valid inputs are provided.
Can I duplicate sheets and create new workbooks as part of the workflow?
You may create a new Excel file (containing copies of specific worksheets) and/or copy worksheets within the existing worksheet. Select the "Create Excel file/sheet" option to adjust the settings.
Can I run multiple workflows sequentially?
You may duplicate the SQL-sheet in the same sheet and use separate SQL sheets for separate processes, for instance if you have to use more than one server. After having done that, you can change the settings under “Run another SQL-sheet” to run the content of each sheet sequentially.
Can I create an email as part of the workflow?
Simply adjust the "Create email" settings. The email will be created according to the template and include any files specified to be attached, ready for you to review and send. Note, that creating an email requires an email account setup with Outlook.
Can I export diagrams and cell ranges to powerpoint?
Adjust the "Export to Powerpoint" – settings. By default all diagrams will be exported to the specified Powerpoint presentation. It is also possible to specify one or more ranges to be exported. Ranges are specified according to the Excel standard. For instance “DemoTable!A1:C3” refers to Cell A1 to C3 on the sheet named DemoTable. Multiple ranges are separated with commas (“,”).
It is possible to export all chart to the powerpoint. The order and size of all charts and cell ranges can be rearranged, and charts that are not needed in a specific presentation may be deleted. If “Create new diagrams” is set to “No”, only existing diagrams will be updated. When existing diagrams and cell ranges are updated, the size will be maintained. It is also possible to put more charts or cell ranges on one slide in the presentation.
Can I edit SQL in Server Management Studio and run the SQL in Excel without copying it to Excel?
Adjust the "Read SQL from file" – setting to refer to one or more sql-files. When you push “Run SQL”, the program will update the SQL according to the latest version of the file(s), before running.
Can I call an executable file as part of the workflow?
It is possible to run an executable file (vbs, bat, exe etc.) both before and after running the SQL. Expand "Other settings" and insert a command line in the "Run executable before SQL" and/or "Run executable after SQL".
Tips and tricks
How can I simplify the use of SQL in Excel for other users?
Establishing a simple workflow is already a good start. For most users (and i particular those without knowledge of SQL) it is beneficial to use the search and replace functionality.
For instance, an SQL statement containing @@NumberOfMonths@@ can be replaced with a user input by typing "@@NumberOfMonths@@" in the "search" field and insert a value or a formula in the "replace" field. This makes it possible to highlight relevant user inputs for the SQL statement.
It is also possible to use an Excel formula in the "replace" field to automatically adjusts the behavior of the SQL.
Can I run an SQL from another Excel sheet?
Simply create a button in an Excel sheet and assign the macro 'Run_SQL_Sheetname("SQL")' to the button. Include the leading and trailing >'< in the text field in the Assign Macro form.
How do I get a registration key?
Go to the free registration key section to receive a free, time limited registration key by email. SQL in Excel is fully functional with a free registration key, but may show a message box reminding the user that the registration key is about to expire. Once the registration key has expired, the SQL will not run until the registration key has been renewed. It is free to renew the registration key.