PowerShell – Export SQL Data to Excel with Powershell

SQL Server  Export  to Excel with Powershell is very useful for all sorts of activities – ETL and reporting are some examples.
This post is to Export SQL Data to Excel with Powershell.In this example, a connection is made to a SQL server Instance using DSN and then SQL output is written to a Excel worksheet.
You can also do a Auto refresh of data, Open the Excel Sheet, Go to Data and click Refresh any new or updated data will reflected in the excel sheet.Pre-requisites

  1. PowerShell 2.0
  2. Excel Component
  3. Setup ODBC – User DSN –  SQL Server Driver – Source Server

Code:- Save the below content in a file ExportSQLDataToExcel.PS1 and Edit the first set of required parameters

or Download ExportSQLDataToExcel

#Just change the below parameters 

$DirectoryToSaveTo='C:\' 
$Filename='DatabaseDetails' 
$From ='pjayaram@test.com' 
$to = 'pjayaram@test.com' 
$SMTP= 'abcd.defg.com' 
$DSN='<DSNName>' 

# constants. 
$xlCenter=-4108 
$xlTop=-4160 
$xlOpenXMLWorkbook=[int]51 
# and we put the queries in here 

# You can replace the SQL 

$SQL=@" 
USE MASTER  
SELECT @@SERVERNAME Servername,  
CONVERT(VARCHAR(25), DB.name) AS dbName,  
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],  
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,  
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],  
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,  
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],  
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,  
convert(sysname,DatabasePropertyEx(name,'Updateability'))  Updateability,  
convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess ,  
convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,  
convert(sysname,DatabasePropertyEx(name,'Version')) Version ,  
CASE cmptlevel  
WHEN 60 THEN '60 (SQL Server 6.0)'  
WHEN 65 THEN '65 (SQL Server 6.5)'  
WHEN 70 THEN '70 (SQL Server 7.0)'  
WHEN 80 THEN '80 (SQL Server 2000)'  
WHEN 90 THEN '90 (SQL Server 2005)'  
WHEN 100 THEN '100 (SQL Server 2008)'  
END AS [compatibility level],  
CONVERT(VARCHAR(20), crdate, 103) + ' ' + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date],  
ISNULL((SELECT TOP 1  
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +  
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +  
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +  
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +  
' (' + CAST(DATEDIFF(second, BK.backup_start_date,  
BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'  
FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup]  
FROM sysdatabases DB  
ORDER BY dbName, [Last backup] DESC, NAME 
"@ 

#Create a Excel file to save the data 
# if the directory doesn't exist, then create it 

if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing 
  { 
  New-Item "$DirectoryToSave" -type directory | out-null 
  } 

$excel = New-Object -Com Excel.Application #open a new instance of Excel 
$excel.Visible = $True #make it visible (for debugging more than anything) 
$wb = $Excel.Workbooks.Add() #create a workbook 
$currentWorksheet=1 #there are three open worksheets you can fill up 
      if ($currentWorksheet-lt 4)  
      { 
        $ws = $wb.Worksheets.Item($currentWorksheet) 
      } 
      else   
      { 
        $ws = $wb.Worksheets.Add() 
      } #add if it doesn't exist 
      $currentWorksheet += 1 #keep a tally 

  # You can refresh it 

      $qt = $ws.QueryTables.Add("ODBC;DSN=$DSN", $ws.Range("A1"), $SQL) 
      # and execute it 
      if ($qt.Refresh()) #if the routine works OK 
            { 
            $ws.Activate() 
            $ws.Select() 
            $excel.Rows.Item(1).HorizontalAlignment = $xlCenter 
            $excel.Rows.Item(1).VerticalAlignment = $xlTop 
            $excel.Rows.Item("1:1").Font.Name = "Calibri" 
            $excel.Rows.Item("1:1").Font.Size = 11 
            $excel.Rows.Item("1:1").Font.Bold = $true 
            $Excel.Columns.Item(1).Font.Bold = $true 
            } 

$filename = "$DirectoryToSaveTo$filename.xlsx" #save it according to its title 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$wb.Saved = $True #flag it as being saved 
$wb.Close() #close the document 
$Excel.Quit() #and the instance of Excel 
$wb = $Null #set all variables that point to Excel objects to null 
$ws = $Null #makes sure Excel deflates 
$Excel=$Null #let the air out 

#Function to send email with an attachment 

Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) 
{ 
#initate message 
$email = New-Object System.Net.Mail.MailMessage  
$email.From = $emailFrom 
$email.To.Add($emailTo) 
$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment  
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
$email.Attachments.Add($emailAttach)  
#initiate sending email  
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$smtp.Send($email) 
} 

#Call Function  
sendEmail -emailFrom $from -emailTo $to  -subject "Database Details" -body "Database Information" -smtpServer $SMTP -filePath $filename 

##################################################################

Output:-
For Testing, You can add a test database in the source server and follow the steps by opening Excel sheet. After refresh you can see the newly added database will be reflected in the worksheet.

Reference:-

https://www.simple-talk.com/content/article.aspx?article=1431

http://www.sqlserver-dba.com/2013/05/sql-server-export-to-excel-with-powershell.html

About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in T-SQL and tagged , , , , . Bookmark the permalink.

5 Responses to PowerShell – Export SQL Data to Excel with Powershell

  1. Phil Factor says:

    See also ‘How to Document and Configure SQL Server Instance Settings’ at https://www.simple-talk.com/content/article.aspx?article=1431 by Brad McGehee from 30 January 2012 which shows this technique for using powershell to export data to excel via SQL

    • Phil, Thank you very much. I was searching for this link. This was a request from one of OP in the forum. I’ve been using this technique for a while. Very big fan of you and Brad. Happy to see you commented on my blog.
      I’ll update the reference section.
      Thanks Again!!!!

  2. bcafferky says:

    Nice job! This will come in useful. Thanks.

  3. Tamela says:

    I absolutely love your blog and find almost all of your post’s to be exactly what I’m looking for.
    Does one offer guest writers to write content for you?
    I wouldn’t mind publishing a post or elaborating on many of the subjects you write in
    relation to here. Again, awesome blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s