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
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.
20 People are following this question.
