question

kpskumar27 avatar image
kpskumar27 asked

Export Sql server data to Excel in .xlsx format with powershell

Hi All, I am a SQL database administrator. We are using SQL Server 2012. There is a requirement from my team to export the SQL server data to excel. Condition follows 1) A stored procedure result set need to be exported to excel. 2) The excel format must be .xlsx or .xls 3) Column headers should also be included in the excel 4) No., of Columns returned by the stored procedure may vary based input parameters. 5) No of records returned by the SP is around 2 million(approximately 1.5GB) 6) Powershell can be used Thanks in advance, Regards, K.P.Senthil Kumar
sql server 2012excelpowershellexport-data
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why not use SSIS or does it have to be a stored procedure?
0 Likes 0 ·
Also, is this a one time thing or are you looking to have it repeated?
0 Likes 0 ·

1 Answer

·
Shawn_Melton avatar image
Shawn_Melton answered
With 1.5GB of data I would not try to write a PowerShell script to write directly to Excel because wherever that script is going to be run would require you to have Excel installed. [e.g. if you are running this through a SQL Agent job then you install Excel on the server...not advised]. If you are talking about running this from a desktop machine or something similar to that setup you should first just export the data using PowerShell's native commands like `Export-Csv`. You then turn around and can easily script it to dump into an Excel file. In doing this I would suggest looking at the `QueryTables` method under `Worksheets` for the Excel COM object. It is equivalent to using the import method in Excel itself. I have found this to be fairly quick in moving data into Excel. Another option if this is a one-time thing or something a user is going to perform, use Excel to query the procedure and pull it in. You can execute T-SQL in Excel via the Data tab to let it pull the information in. I would not thing this is going to perform all that well with 1.5GB of data but you could test it out.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.