Powershell script using Excel running slow

Question

So i have this script that i coded on my laptop that works just fine, the job is to combine two .csv-files into one .xls-file. And running the script with two .csv-files containing a couple of thousand rows takes a few seconds max.

But when i try to run it on the server where it should be located, it takes... hours. I haven't done a full run, but writing one line in the .xls-file takes maybe 2-3 seconds.

So what im wondering is what is causing the huge increase in runtime. I'm monitoring the CPU-load while the script is running, and it's at 50-60% load.

The server has loads of Ram, and two CPU-core. How can i speed this up?

The script looks like this:

$path = "C:\test\*"
$path2 = "C:\test"
$date = Get-Date -Format d
$csvs = Get-ChildItem $path -Include *.csv | Sort-Object LastAccessTime -Descending | Select-Object -First 2
$y = $csvs.Count

Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs) {
    Write-Host " "$csv.Name
}

$outputfilename = "regSCI " + $date
Write-Host Creating: $outputfilename

$excelapp = New-Object -ComObject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet = 1
$xlleft = -4131

foreach ($csv in $csvs) {
    $row = 1
    $column = 1
    $worksheet = $xlsx.Worksheets.Item($sheet)
    $worksheet.Name = $csv.Name
    $worksheet.Rows.HorizontalAlignment = $xlleft
    $file = (Get-Content $csv)
    Write-Host Worksheet created: $worksheet.Name

    foreach($line in $file) {
        Write-Host Writing Line
        $linecontents = $line -split ',(?!\s*\w+")'

        foreach($cell in $linecontents) {
            Write-Host Writing Cell
            $cell1 = $cell.Trim('"')
            $worksheet.Cells.Item($row, $column) = $cell1
            $column++
        }
        $column = 1
        $row++
        $WorkSheet.UsedRange.Columns.Autofit() | Out-Null
    }
    $sheet++
    $headerRange = $worksheet.Range("a1", "q1") 
    $headerRange.AutoFilter() | Out-Null 
}

$output = $path2 + "\" + $outputfilename
Write-Host $output
$xlsx.SaveAs($output)
$excelapp.Quit()

AdamL · Accepted Answer · 2016-06-20 09:39:59Z

2

To speed up your existing code, add these just after creating Excel object:

$excelapp.ScreenUpdating = $false
$excelapp.DisplayStatusBar = $false
$excelapp.EnableEvents = $false
$excelapp.Visible = $false

And these just before SaveAs:

$excelapp.ScreenUpdating = $true
$excelapp.DisplayStatusBar = $true
$excelapp.EnableEvents = $true

This causes excel not to render the worksheet in realtime and fire events every time you change the contets. Most probably DisplayStatusBar and ScreenUpdating doesn't matter if you make an application invisible, but I included it just in case.

Also, you're running Autofit() after every line. This certainly doesn't help with performance.

answered Jun 20, 2016 at 9:39

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

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

2 Comments

Anders Ekelund Over a year ago

Thanks for the reply, will definitely try this. Could you clarify on the Autofit()-part, since i've just stolen the excel-code, im not exactly sure what it all does

AdamL Over a year ago

@AndersEkelund AutoFit() adjusts column widths to cell contents. If you need it, do it once per sheet, not after inserting every row.

Collectives™ on Stack Overflow

Powershell script using Excel running slow

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related