The free SQL to Excel Export tool

Earlier this month I released a new, free tool to export SQL Server query data to Excel files without needing Excel. It also installs stored procedures and runs them, if that’s what you desire. Go ahead and play with it. I’ll wait.

There was some positive interest on Twitter, for which I am very grateful, and demand from at least two people to put it on GitHub as soon as possible.

“Why not PowerShell?”

However, there were some perplexed voices asking why I didn’t “just” use PowerShell. One particular person who I am not naming because I did not appreciate their tone, proceeded to create a PowerShell script in a reply tweet, demonstrating just how “easy” it is.

Here’s the thing about PowerShell: it’s not easy to use. I say that as a software developer from the age of 6, network administrator since I started gainful employment in 1997, and all-round technology nerd. PowerShell is not easy to use.

Neither is any command-line tool for that matter. Who wants to type things in?

Look, I get it. Scripting tools are extremely powerful. In fact, I am getting on a plane in a few hours to present command-line goodness to people at the first of two SQL Saturdays.

That does not change the undeniable fact that PowerShell is not easy to use.

So, even though I have previously written about PowerShell and use it frequently, and continue to present on command-line tools, I still find myself having to read the documentation and curse the unhelpful error messages when what I need to do can be done, in my mind, a lot simpler.

The sql2xls origin story

The name of my tool is temporarily called sql2xls. There is an abandoned Java-based tool with the same name, but I had to call the project something, and I’ve been in the habit of giving projects extremely long names. Until I come up with a better name, this is the name of the tool and it’ll remain like that for the time being.

In 2012 I founded Born SQL as a going concern, with a tax number and a bank account and a service offering now colloquially called dbSnitch.

Over time, that very manual process was automated into a toolset to gather data from customer environments into a format I could easily parse for later processing. I decided on JSON, because it is clear text and highly compressible. Even large customer instances will have their diagnostics compressed down to a handful of megabytes.

This compressed file is uploaded to a secure server, where another process decompresses it, interprets the results into an easy-to-read and searchable PDF document. As a side-effect, the process also creates Excel spreadsheets for the customer to view the raw data in a more accessible way.

Why not PowerShell indeed

My customers download a single, stand-alone .NET executable, a configuration file, and three series of scripts to execute against their instance. The code detects which version of SQL Server they’re running, and then figures out which scripts to run based on that. Could I have done that in PowerShell? You bet! I could also have done it in Ruby, or Python, or (so help me) sqlcmd. In fact, the very first attempt at automation was a Windows batch file running sqlcmd commands and getting the results.

I worked with C++, with PowerShell, and then eventually settled on .NET because of its flexibility and near-universal compatibility. The executable itself runs on both .NET 2.0 and .NET 4.x (thanks, Andrew Cook!), and can run on any machine that can connect to SQL Server port 1433. I don’t need to run it myself, and in fact I encourage customers to set up the connection file themselves so that I never have to see any credentials.

Over the years I have experienced some pretty weird edge cases with this tool. I’ve seen error messages that no one else has seen. You know how when you search for an error and not even a single result comes up? People use software in the wild in ways that would frustrate and confound even the most seasoned experts.

So when I’m asked why I didn’t just use PowerShell, it’s because of this. End users don’t use PowerShell. They want to point a tool at something and let it do its thing. Technical experts who feel that their area of expertise is simple and easy are mistaken. There’s a reason why PowerShell experts exist, and that’s because it’s not easy to use.

My gift to the community

I’ve taken two pieces of the dbSnitch tool and combined them into a single tool where you put scripts into one or more files, save those in the sql2xls folder, and run it. A few seconds later, it makes an Excel file with the results. No need for convoluted PowerShell mental gymnastics. No need to look for command line switches online.

Anyone who can open and save a T-SQL script can use this tool. It doesn’t need a third-party PowerShell module to be installed. Since it’s written in .NET, it needs a client with the .NET runtime. Soon it will work on macOS and Linux. And one day, I plan to make a SQL Server Management Studio plugin for it.

If you’re a PowerShell power user, this is not for you, and that’s ok. Keep doing your mad skillz.

For everyone else, check out this cool thing I made, then please leave your comments below.