Skip to main content Skip to footer

Taming the SQL Dragon: Easy-Peasy Ways to Export SQL Data to Excel

Ever had a staring contest with SQL Server, willing it to magically spit out a neat Excel sheet? If you've blinked first (and let’s face it, SQL Server's got an uncanny ability to hold a stare), then buddy, this one's for you.

1. When Wizards Aren't Just in Harry Potter: The SSMS Export Magician:

Hey there, Muggle! 🧙‍♂️ SQL Server Management Studio (SSMS) isn't just for... well, managing SQL servers. Tucked away in its troves is a nifty little export wizard!

Steps to Unlock this Sorcery:

  • Fire up SSMS and connect with your mystical SQL realm.
  • Right-click the database that’s been giving you the stink-eye.
  • Voila! Navigate to Tasks > Export Data.
  • Let the wizard guide you through a magical tour, from the land of databases to the shiny shores of Excel.

But, fair warning! If your data is draped in the finest HTML robes, SSMS will, unfortunately, rip them off and present it in plain text. Talk about a party pooper!

2. EPPlus: When .NET Goes to the Gym:

Flexing C# muscles? 💪 Ever heard of EPPlus? No, it’s not a new vitamin supplement. This library turns .NET into .NET on Steroids, enabling it to pump out Excel files like they’re going out of fashion.

And the best part? You get to control the formatting! And trust me, control in a developer’s hands feels like Thor’s hammer. Well, almost.

using OfficeOpenXml;
using System.Data.SqlClient;


var connectionString = "your_connection_string";
var query = "SELECT Column1, Column2 FROM YourTable";

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(query, connection))

    var dataTable = new DataTable();

    using (var excelPackage = new ExcelPackage())
        var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);

        // Additional formatting if necessary
        // ...

        excelPackage.SaveAs(new FileInfo("output.xlsx"));

Want a sneak peek? Check out the code above! It’s like the protein shake recipe for your data.

3. PowerShell: Not Just for the System Admins!:

If you thought PowerShell was the domain of bearded system admins and magical IT elves, think again. Pair it up with the ImportExcel module, and bam, you've got yourself a data-manipulating, Excel-exporting sorcerer! I mean, who needs a magic wand when you have a command-line?

Install-Module -Name ImportExcel -Scope CurrentUser
$connectionString = "your_connection_string"
$query = "SELECT Column1, Column2 FROM YourTable"
$data = Invoke-Sqlcmd -ConnectionString $connectionString -Query $query
$data | Export-Excel -Path "output.xlsx"

Super simple and if you want to repeat what you are doing or combine with other stuff its top stuff.

4. Third-Party Tools: Because, Why Reinvent the Wheel?:

Remember the feeling when you found out about food delivery apps? That’s how you’ll feel about tools like DBeaver and SQLyog. You get all the deliciousness without sweating over the stove! Likewise, these tools dish out your data on an Excel platter, saving you the culinary ordeal.

In the grand world of data, SQL Server and Excel are like Batman and Robin. Sure, sometimes they need a little nudge to play nice. But armed with these tricks up your sleeve, you're not just a mere mortal anymore. You're the Data Whisperer. Now, go forth, and may your exports be ever in your favor! And remember, when in doubt, CTRL+Z. 😉

About the author

Richard Brisley

I'm a multi-award winning Sitecore developer. Currently working for Sagittarius Marketing as a solutions architect to understand customer needs and produce multi-national high-performance websites.

About Me

As a Tech Lead for Sagittarius marketing who I have been with for the last twelve years. I oversee a team of seven working pods, including numerous developers and contractors in multiple global locations. This involves supporting the developers with coding issues, meetings and phone calls with their clients and going out of pitches with potential new clients.

I have extensive experience building and supporting Sitecore websites from Sitecore 6+ including Helix pattern, I scored 100% in the Sitecore 7 certification exam. Experience managing and maintaining SQL Server, integration with numerous third parties such as Salesforce, AppDynamics, New Relic, Dynamics CRM and many payment gateways.

The first Sitecore website I developed was Skiweekends which was architected and developed by me. It won the Sitecore Experience Award the main award during the Sitecore Experience awards ceremony and the Sitecore Best Travel & Tourism award. I also was lucky enough to perform the first Sitecore 8 upgrade within the United Kingdom for Liberon.

Personally I have had the honour of being recognised in several award ceremonies. Including the BIMA 100 awards in 2019 in the Tech Trailblazers category and previously in the Dev's and Makers category. I’ve been highly commended twice in the Wirehive 100 Techie of the Year awards. Due to my involvement in many aspects of Sagittarius work, many of the awards for their clients I've also been involved in.