Introducing SQL-to-Excel

2018-11-25 10 Comments

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

10 comments:

  1. Ooo, really cool! What license do you want to use for the repo? If it's going to be open source, I'd recommend the MIT License - it's what we use for the First Responder Kit, and what DBAtools uses. That lets people use it inside their companies pretty easily. My gut hunch is that it's going to be really useful for consultants who want to give it to their clients along with a list of scripts to run, and then get info back about their servers. (That's how we use a similar app that Richie wrote inside our own company.)

    ReplyDelete
    Replies
    1. Hey Brent, thanks for the input. After a few milliseconds of soul searching, I went with the MIT license. 😎

      Delete
  2. I forgot to add - if you want help picking a license, check this out: https://choosealicense.com/ And then if you want help getting it set up in Github, let me know and I can do a pull request.

    ReplyDelete
  3. Hello,
    There is a bug in the tool i had fixed, when writing to the excel it points to Debug/SqltoExcelexe/ this is because of declaring sourcefolder as static in populate Dataset method and also there was a bug in CreateExcel Spreadsheet. I had made few changes by storing the directory path in the App.config file, tested and working file. Please find the latest version of the code in my github https://github.com/Dharmagee/myRepos

    ReplyDelete
  4. I have written exactly the same thing (like literally the same exact thing) about 4 years ago. https://github.com/rgelb/Database-to-Excel

    ReplyDelete
  5. Hi Dave, This is cool.. Check out the powershell version as well, we think its easier to check multiple SQL instances at once..

    https://github.com/g-kannan/One-Last-Time-Protocol-OLTPDBA/blob/master/PS_FUNCTIONS/Export-SQLServerInfo.ps1

    ReplyDelete
  6. Have you tried using the reports server SSRS, that comes with an SQL license? It will do the same thing and format the output a bit? I think it has been working and easy to install for at least 12 years.

    ReplyDelete
    Replies
    1. Yep. That's a nice feature of SSRS. Different use case, though.

      Delete
  7. Just now I said "I wish I had something to do this exact thing. Wait, I read a blog post about this last week."

    Thanks, I love it.

    ReplyDelete