Max Trinidad – The PowerShell Front

I recently helped someone with providing a solution using PowerShell to extract data from SQL Server into an Excel file. We all know that  we could use SSIS to provide the means to do this but there are some situations you may want to use scripting instead. So, found one script I did back in 2009 that will do such a thing. The funny thing is, when I looked at it, I realized that for the Excel part I had a  lot of unnecessary extra code and it could be improved greatly. So, here’s the updated version.

This *script will do the following steps:
1. Connect to SQL Server and get the SQL Server data.
2. Build the Excel file with columns heading and data.
3. Save the Excel file and Close/Terminate Excel process.

*Note: This script is PowerShell Version 2.0 compatible.

Getting you SQL Data

This script uses the ‘System.Data.SqlClient.SqlConnection’ which you can run on any machines without SQL Server installed. In this example the connection string is a trusted ‘Windows Authentication’. Then, I’m using the Here-String @”..”@ to insert the T-SQL script I want to execute against SQL Server. Keep in mind, this connection string can be change to use SQL Server Authentication.

[sourcecode language=”powershell”]
## ———- Working with SQL Server ———- ##

## – Get SQL Server Table data:
$SQLServer = ‘SQLServer01\MSSQLInstance01’;
$Database = ‘Database1′;
$SqlQuery = @’
Select top 10
[Field1]
,[Field2]
,[Field3]
,[Field4]
,[Field5]
from Database1.dbo.Table1
where [Field4] = ‘X001’
‘@;

## – Connect to SQL Server using non-SMO class ‘System.Data’:
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = `
"Server = $SQLServer; Database = $Database; Integrated Security = True";

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;

## – Extract and build the SQL data object ‘$DataSetTable’:
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$DataSetTable = $DataSet.Tables["Table"];

[/sourcecode]

Buidling the Excel file

This block of code will generate the Excel file consuming the SQL data object ‘$DataSetTable’. The result of the SQL DataSet will be use to automatically create the columns heading and data rows. This is the heart of the script where the magic happen.

[sourcecode language=”powershell”]
## ———- Working with Excel ———- ##

## – Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## – Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

## – Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

## – Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};

## – Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;

foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## – Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";

## – Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = `
$rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};

## – Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit();

[/sourcecode]

Saving and Terminating Excel

Now that I’ve build the Excel sheet, I need to save the file, quit and terminate Excel. And, Yes! It’s needed to terminate/kill the Excel process because this process will remain active even if when exiting/closing the PowerShell session.

[sourcecode language=”powershell”]
## ———- Saving file and Terminating Excel Application ———- ##

## – Saving Excel file – if the file exist do delete then save
$xlsFile = `
"C:\Temp\NewExceldbResults_$((Get-Date).ToString("yyyyMMdd_hhmmss")).xls";

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

## – End of Script – ##

[/sourcecode]

Additional Note

One thing to understand, this process will work with small datasets. So, the more data you extract the slower it may take to build the Excel file. This is why is important to test the script(s) and look at other best possible solution. Maybe it’s better to use SSIS (SQL
Server Integration Services) but it doesn’t hurt try other technologies.