Performance increase for PowerShell dataset to Excel

Question

I have a PowerShell script which pulls data from DB and pushes it to a excel sheet. I am facing slowness (45 mins approx) while copying the records in the dataset to the excel sheet as the number of records exceed 200K. And I am trying to loop them one by one using the below snippet, which takes more time. Is there a way in which I can transfer the data from dataset to excel more efficiently?

   $cells=$Worksheet.Cells
   $row=1
   foreach ($rec in $dataset.Tables[0].Rows)
        {
            $row++
            $col=1
            $cells.item($Row,$col)=$USR.ID
            $col++
            $cells.item($Row,$col)=$USR.Name
            $col++
            $cells.item($Row,$col)=$USR.Age
            $col++
        }

Possibly useful: https://stackoverflow.com/questions/536636/write-array-to-excel-range — Robin Mackenzie, Jul 05 '17 at 14:00
Yes. For example, you could use `Export-CSV`. And if you need to use Excel functions, open the file in Excel. — G42, Jul 05 '17 at 14:02
@gms0ulman export-csv did the trick, only thing is file size is huge — VSMK, Jul 11 '17 at 10:59

score 1 · Answer 1 · answered Jul 05 '17 at 14:42

You shoud try PSExcel module. There's no need to create COM object and even have Excel installed. Your example would look like this and be lightning fast:

$dataset.Tables[0] | 
Select-Object ID,Name,Age | 
Export-XLSX -Path $FullName -AutoFit -WorksheetName 'MyData'

score 0 · Answer 2 · answered Jan 01 '22 at 02:05

A nice little workaround I saw sometime ago was to format the rows as a CSV string and simply paste them in. For the environment I was using, this proved to be more efficient than creating a file using Export-CSV, then loading it in Excel.

#Row data joined with tabs
$data = @("[A1]", "[A2]", "[A3]", "[A4]", "[A5]", "[A6]") -join "`t"

#Multiple rows joined with new lines
$dataToPaste = "{0}`n{1}`n{2}" -f $data, $data.replace("A", "B"), $data.replace("A", "C")

$excel = New-Object -ComObject Excel.Application
$book = $excel.Workbooks.Add()
$sheet = $book.Worksheets.Add()

#Activate where to put data
$sheet.Range("B2").Activate() | Out-Null

#Copy data to clipboard and paste into sheet.
$dataToPaste | Clip
$sheet.Paste() 

$excel.Visible = $true

#Cleanup
[Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
$excel = $null

I did find that, very rarely, the Paste method throws an error, which was fixed by retrying a second time if it failed:

try{
    $sheet.Paste() 
}catch{
    $sheet.Paste() 
}

This may not be a preferred option if you are running something on a PC being used by someone, as the user could copy something to the clipboard after the script does (but before $sheet.Paste()) and invalidate your data.

Performance increase for PowerShell dataset to Excel

2 Answers2