Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Introducing SQL-to-Excel

SQL Server - Dave Mason - SQL to Excel

In 2018, I've found myself frequently running a series of static SQL Server queries and copying/pasting the output of each query into a separate sheet in a Microsoft Excel Worksheet file. They say if you perform a manual task X number of times or more, you should automate it. Great advice, right? There are a number of ways to export SQL Server query results to Excel, and an internet search readily turns up many options. I didn't find any that suited my needs, so I wrote my own utility, uncleverly named "SQL to Excel".

SQL to Excel is a modest command line utility. It iterates though a folder of *.sql script files, executing each script, capturing the script query output, and writing the results to a Microsoft Excel Worksheet file (one worksheet per script file). And yes, it's an actual Excel file, not a .csv file. The C# code for SQL to Excel is available as a GitHub repository.


Dependencies

There is a project reference on DocumentFormat.OpenXml: you'll notice the files "DocumentFormat.OpenXml.dll" and "DocumentFormat.OpenXml.xml" included with the release. These files originate from the Open XML SDK 2.5 for Office, which is used to create the .xlsx file. As long as all the files from the release are deployed together, Excel does *not* need to be installed to run the utility. The executable is compiled for version 4.6.1 of the .NET framework. Both the x86 and x64 OS architectures are supported.


Quick Start

Download the files from the latest release and copy one or more SQL scripts into the same folder. Edit the .bat file to specify the name of the SQL instance you want to connect to and the name of the database (db name may or may not be applicable, depending on how the scripts are written). Launch the .bat file, which runs SQLtoExcel.exe with the minimum command line parameters. The Microsoft Excel Worksheet file named "SQLtoExcel.xlsx" will be created in the same folder.


Other Notes

SQLtoExcel.exe attempts to ignore “GO” batch separators. If there are #temp tables that span batches, you may encounter errors. Run SQLtoExcel.exe with the /Help or /? command line switch to display help (pictured below).



SQL Server - Dave Mason - SQL to Excel

SQL Server - Dave Mason - SQL to Excel

SQL Server - Dave Mason - SQL to Excel

SQL Server - Dave Mason - SQL to Excel

SQL Server - Dave Mason - SQL to Excel

Comments

Post comment