Optimizing Powershell for Exporting SQL Data to Excel

Data and Databases
1

My Powershell script efficiently exports small SQL tables, but it lags with large data. How can I optimize exporting multiple tables? Below is an alternative sample implementation:

# Define SQL connection parameters
$server = "newServer"
$dbName = "newDatabase"
$sqlStmt = @"
SELECT TOP 10 * FROM newDatabase.dbo.sampleTable
"@

# Establish SQL connection and command
$connObj = New-Object System.Data.SqlClient.SqlConnection "Server=$server;Database=$dbName;Integrated Security=True"
$cmdObj = New-Object System.Data.SqlClient.SqlCommand($sqlStmt, $connObj)

# Retrieve data into a dataset
$adapterObj = New-Object System.Data.SqlClient.SqlDataAdapter $cmdObj
$dataSetObj = New-Object System.Data.DataSet
$adapterObj.Fill($dataSetObj) | Out-Null
$dataTable = $dataSetObj.Tables[0]

# Initialize Excel application
$excelApp = New-Object -ComObject Excel.Application
$excelApp.Visible = $false
$workBook = $excelApp.Workbooks.Add()
$sheet = $workBook.Worksheets.Item(1)

# Write headers and rows
$colCounter = 1
foreach ($col in $dataTable.Columns) {
    $sheet.Cells(1, $colCounter).Value = $col.ColumnName
    $sheet.Cells(1, $colCounter).Font.Bold = $true
    $colCounter++
}
$rowCounter = 2
foreach ($row in $dataTable.Rows) {
    for ($i = 0; $i -lt $dataTable.Columns.Count; $i++) {
        $sheet.Cells($rowCounter, $i + 1).Value = $row[$i].ToString()
    }
    $rowCounter++
}
$sheet.UsedRange.EntireColumn.AutoFit()

# Save and close Excel file
$file = "C:\temp\newExport.xlsx"
if (Test-Path $file) { Remove-Item $file }
$workBook.SaveAs($file)
$excelApp.Quit()
2

hey silvia85, try using a direct csv export instead of com object excel calls. using bulk copy and multi-threading can cut down processing time and avoid overhead. give it a go and see if that speeds up exporting, cheers!