CODEWITHSUNDEEP
excelcsvpowershellcomautomation
Anders Ekelund
Anders Ekelund

Reputation: 183

Powershell script using Excel running slow

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

Upvotes: 3

Views: 2094

Answers (1)

AdamL
AdamL

Reputation: 13141

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.

Upvotes: 2

Related Questions