SQLinExcel - the simplest way to query data with SQL and make it available in Excel
SQLinExcel is just the right tool for you when
- you work with large amounts of data on SQL servers and
- you would like to use the data in Excel - but don't want to spend your time on the tedious tasks that often come with modifying queries and updating data.
How does SQLinExcel work?
SQLinExcel is an Excel workbook that greatly simplifies data extraction from Microsoft SQL (T-SQL) and helps automate most common tasks in ad hoc data analysis and Excel reporting. It is easy to setup a new work flow based on an SQL staments, transfer relevant data to Excel and use the many options for delivering or presenting data. SQLinExcel allows the user to run SQL queries from Excel and immediately create or update one or more tables or pivot tables in 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 with the prefix “#_X_”, where X is a letter indicating how the data is transferred to Excel. For example the data stored in a temporary table named #_P_DemoTable will be transferred to pivot tabel on a spread with the same name. When "Run SQL" is pressed again, the data query will be run again, and the data in Excel will be updated.
The only requirements to get started are Microsoft Excel and a Microsoft SQL Server. The workbook is fully functional with a free registration key.
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 is efficient data transfer from from an SQL server to Excel. The ease of use makes SQLinExcel equally relevant for ad hoc analytical work and regular data processing.
To get started you simply need to:
- Download SQLinExcel.xlsm here 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 a select statement"). See example in the user guide.
- Press "Run SQL" – and enjoy powerful analytics made simple!
SQLinExcel at a glance
In addition to easy data transfer from an SQL server, SQLinExcel offers: Transferring data to the SQL server, reading SQL from files, copying sheets, running executable files, exporting data to text files, exporting charts and cell ranges to Powerpoint, creating an email in Outlook and creating new workbooks. In other words: SQLinExcel facilitates most of the tasks that data analysts need, and does so in a single, intuitive and fully documented workflow.
Benefits of SQLinExcel
SQLinExcel saves time and reduces the operational risk associated with manual tasks. At the same time the entire workflow is documented in one Excel sheet that can easily be reviewed, modified and/or rerun. Once a workflow has been designed, it is easy to use by anybody regardless of their knowledge of SQL.
SQLinExcel - benefits and options:
- Retain the complete documentation of the data processing in the Excel sheet.
- Use any SQL functionality on the server, including stored procedures, views, inserts, updates etc.
- Transfer data from SQL Server to Excel efficiently.
- Highlight and set relevant parameters for the SQL by offering search and replace in the query.
- Automate most common tasks for handling data:
- Insert and update pivot tables in Excel – that can hold more than a million rows.
- Insert and update tables in Excel sheets.
- Swiftly modify the “data model” (the SQL) and re-run .
- Combine data in Excel with the data on an SQL-server.
- Create text files in various formats.
- Create directories for output files.
- Copy sheets and create new excel workbooks.
- Copy and/or update diagrams and cell ranges to Powerpoint presentations.
- Create predefined email and attach relevant files.
- Run executable files (exe, vbs etc.) before or after running the SQL-statement.
- Multiply the main sheet to run multiple processing sequences in one work flow.
- Handle many datasets in one work flow.
- Store the SQL and the resulting data in the same file.
- Allow the next user to easily rerun the entire workflow.
- Format the SQL similar to that of Server Management Studio to supports readability.
- Export the SQL with Excel data and replacements to clipboard for quick troubleshooting in Server Management Studio.
- Run workflow with a task scheduler.
SQLinExcel is designed to be simple and flexible to use. In general, the functionalities use default settings (for instance for creating Powerpoint presentations or text files) to get you started quickly. Don't forget that many settings are available to yield a different result.
To use SQLinExcel you need:
- Microsoft Excel. The program has been tested with Microsoft Office 2010 and 2016, but may well work with other versions.
- Microsoft SQL Server. The program has been tested with Server 2008, 2012, 2014 and 2016, but may well work with other versions.
- Basic understanding of SQL – Structured Query Language.