TSQL export to Excel (.xlsx)

Question

TSQL export to Excel (.xlsx)

asked

SSinhg 246

Hi,

Using tSQl is it possible to export to .xlsx format (Excel)... I'm looking to export from a table or view.

I have googled and read a few blogs on similar lines but they seam to work for .xls format only...

Any help or pointers will be highly appreciated.

Answer 1

answered

Michael Taylor 37,456

Not directly in T-SQL. Normally export is handled using SSIS or similar tool.

XSLX follows the Office OpenXML specification and ultimately is a ZIP file. T-SQL can generate text files but it isn't going to be able to generate an OpenXML document.

SSinhg 246 Reputation points

2021-11-22T17:37:46.527+00:00

@Michael Taylor will it be same for powershell? Can we do it using powershell.
Michael Taylor 37,456 Reputation points

2021-11-22T17:54:50.6+00:00
Yes but it isn't trivial compared to SSIS. If you are going to be querying data then you'll definitely want the SQL Server cmdlets installed. Since you'll likely need to manipulate the Excel app you'll also need Office installed. If that isn't an option then you could probably get away with just using the Excel data source (that still needs to be installed) but you'll need that either way.

Ultimately you'll need to do something along these lines:

Open a connection to the SQL database. Refer to Invoke-SqlCmd for one approach.

Run a query to load the resultset into memory

Open a connection to Excel using COM. Refer to this blog article on using Powershell to interact with Excel. Note that if you cannot install Excel then you'll have to use the OLEDB driver instead and it gets harder.

If this is a new spreadsheet then you'll likely need to create and configure the worksheet as well.

For each row in the resultset, write to the worksheet in the file. The earlier blog article shows how to do this.

Once you are done, clean everything up so you don't leave files locked.

I should also mention that if you have R installed in SQL then it supposedly can do it as well but I haven't tried it. Here's a link to an article about it.

TSQL export to Excel (.xlsx)

0 additional answers

Activity