SQL extract data to Excel using Powershell

Question

I want to extract data from SQL server to a new excel file using powershell . For small data set my code works but some tables has more than 100.000 rows and this will take ages. The reason why I don't use the utility in SQl server is because I want to extract mutilple tables. Is there a way to optimize my script to export big tables to excel? or is there another way to do this?

I'm using the following script

## ---------- Working with SQL Server ---------- ##

## - Get SQL Server Table data:
$SQLServer = 'server';
$Database = 'database';
$SqlQuery = @'   Select top 10 *   from database.dbo.table   '@;

## - 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"];


## ---------- 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();

## ---------- Saving file and Terminating Excel Application ---------- ##

## - Saving Excel file - if the file exist do delete then save
$xlsFile = `
"C:\path\file.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 - ##

Try to construct a range in memory and put it in the excel object at once, write cell by cell is very slow. My comment is an advice, maybe you need to tune other things, but for sure don't write cell by cell. Here is a helpful link, code is in .net but you could adjust for powershell [Write array to excel](https://stackoverflow.com/questions/536636/write-array-to-excel-range) — Andro Font, Aug 18 '17 at 14:26
Do you mean to replace this part? `## - Populating columns: $xlsSh.Cells.Item($rowData, $colData) = `` $rec.$($Coln.ColumnName).ToString(); $ColData++; }; $rowData++; $ColData = 1; };` — MBurger, Aug 18 '17 at 14:29

score 2 · Accepted Answer · answered Aug 18 '17 at 18:17

There's some simple magic to make this a lot easier, and that's Copy/Paste. What you can do is convert your datatable to a tab delimited CSV, copy that to the clipboard, and paste it into Excel. I'll ignore your SQL part, since you seem to have that well in hand.

## ---------- 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);

## - Copy entire table to the clipboard as tab delimited CSV
$DataSetTable | ConvertTo-Csv -NoType -Del "`t" | Clip

## - Paste table to Excel
$xlsObj.ActiveCell.PasteSpecial() | Out-Null

## - Set columns to auto-fit width
$xlsObj.ActiveSheet.UsedRange.Columns|%{$_.AutoFit()|Out-Null}

SQL extract data to Excel using Powershell

1 Answers1

Linked