Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server Export to Excel with Powershell

15 May,2013 by Jack Vamvas

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 .

 

#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 
    $cn.Close()
      
    $dataTable = new-object "System.Data.DataTable" "Principals"
    $dataTable = $dataSet.Tables[0]
  
  #iterate through every DataTable line item and insert to the Excel worksheet
    $x=1
    $dataTable | FOREACH-OBJECT{
          
        $sheet1.cells.item($x, 1) =  $_.principal_id
    $sheet1.cells.item($x, 2) =  $_.principal_name
    $x++
          
    }
   
    

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

 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: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


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