SQL Server Export to Excel with Powershell

15 May,2013 by Tom Collins

SQL Server  Export  to Excel with Powershell is useful for all sorts of activities – ETL, management data and reporting are some examples.  This post is a follow up to Export Excel data to SQL Server with Powershell .

In this example, a connection is made to a SQL server Instance , a recordset is returned , placed into a DataSet and then written to a new Excel worksheet.

Using Export-CSV Powershell - SQL Server DBA  produces similar results, but doesn’t give you the granular control  you may require when exporting to Excel.

If you have any questions contact me or leave a comment on this post .

Note: I've made an adjustment to this script to support column names. For this script - lines 32-34 and line 38.   Basically line 1 of the Excel worksheet will be populated with the column names. 


#some variables 
$serverName = "myserver\sqlserver1"; 
$databaseName = "master"; 
#the save location for the new Excel file
$filepath = "C:\projects\excelexport.xls"; 

    #create excel object
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.add()
    $worksheetA = $workbook.Worksheets.Add() 
    #create byUser worksheet 
    $sheet1 = $workbook.worksheets.Item(1)
    $sheet1.name = "server_principals"
    #create a Dataset to store the DataTable  
    $dataSet = new-object "System.Data.DataSet" "Server_Principals"
    #create a Connection to the SQL Server database
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
    $query= "select principal_id,[name] as principal_name from sys.server_principals;"
    #Create a SQL Data Adapter to place the resultset into the DataSet
    $dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $cn) 
        $dataAdapter.Fill($dataSet) | Out-Null
    #close the connection 
    $dataTable = new-object "System.Data.DataTable" "Principals"
    $dataTable = $dataSet.Tables[0]

  #assign  column names
   $sheet1.cells.item(1, 1) =  "Principal_ID"
   $sheet1.cells.item(1, 2) =   "Principal_Name" 
  #iterate through every DataTable line item and insert to the Excel worksheet
  ##Note: starts at 2 as 1 is the column headers
    $dataTable | FOREACH-OBJECT{
        $sheet1.cells.item($x, 1) =  $_.principal_id
    $sheet1.cells.item($x, 2) =  $_.principal_name

   $range1 = $sheet1.UsedRange
   #save excel worksbook
   $excel.ActiveWorkbook.SaveAs("$filepath ")

 Read More

SQL Server – Powershell Excel to HTML - SQL Server DBA

Export Excel data to SQL Server with Powershell - SQL Server DBA

Powershell - run script on all sql servers - SQL Server DBA

Export-CSV Powershell - SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server Export to Excel with Powershell

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer