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: stackoverflow.com/questions/536636/write-array-to-excel-range — Robin Mackenzie
– Robin Mackenzie, Commented Jul 5, 2017 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
– G42, Commented Jul 5, 2017 at 14:02
@gms0ulman export-csv did the trick, only thing is file size is huge — VSMK
– VSMK, Commented Jul 11, 2017 at 10:59

AdamL · Accepted Answer · 2017-07-05 14:42:41Z

1

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'

answered Jul 5, 2017 at 14:42

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris A · Accepted Answer · 2022-01-01 02:05:19Z

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.

Collectives™ on Stack Overflow

Performance increase for PowerShell dataset to Excel

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related