PowerShell CSV Script Timing Out

Question

I have written a simple PowerShell script that reads data from a master CSV file and parses a URL column within.

It then checks the local file system to test if a binary file exists (in this case a WAV or AIFF file) within a subdirectory of the script root path, and if it does, appends the row meta data with a "Local File" column appended to a new CSV file.

The script works perfectly fine and executes in about 10 seconds with the master CSV file containing a sample of about 2000 rows, however a client in Korea is saying that the script slows down and eventually times out with a master CSV containing roughly 60000 rows.

A sample of the CSV I am parsing can be found here.

My gut feeling is that this is a memory issue caused by the $NewCSVObject however since this one of my first PS scripts, it very well could be just bad coding on my part.

function Get-ScriptDirectory {
    Split-Path -parent $PSCommandPath
}

function Resolve-Local-CSV {
    Write-Host "Generating Local CSV File ... Please wait ..."
    $currentPath = Get-ScriptDirectory
    $csvfile = "$currentPath{0}media_local_master.csv" -f [IO.Path]::DirectorySeparatorChar
    $masterCSV = Import-Csv "$csvfile" -header _id, active, track_title, Download_URL, artist_name, album_title, composer, duration, publisher, description, url_path, beats_per_minute, file_path_uncompressed, preview_url, genres, moods, styles, instruments, keywords, last_modified -delimiter ','
    $NewCSVObject = @() 
    $masterCSV | ForEach-Object {
        $downloadUrl = $_.Download_URL
        if ($downloadUrl -ne 'Download_URL') {
            $parsedurl = $downloadUrl.split("/")
            $artist = $parsedurl[4]
            $album = $parsedurl[5]
            $track = $parsedurl[6]
            $albumTarget = "$currentPath{0}media{0}$artist{0}$album{0}" -f [IO.Path]::DirectorySeparatorChar
            $trackTarget = "$albumTarget$track"
            If ((test-path $trackTarget)) {
                $localfile = "media{0}$artist{0}$album{0}$track" -f [IO.Path]::DirectorySeparatorChar
                $_ | Add-Member -MemberType NoteProperty -Name "Local_File" -Value $localfile
                $NewCSVObject += $_ 
            }
        }
        $newLocalMaster = "$currentPath{0}media_local_master_final.csv" -f [IO.Path]::DirectorySeparatorChar
        $NewCSVObject | Export-Csv "$newLocalMaster" -notype
    }
    Write-Host "Finished Generating Local CSV File ..."
}

Resolve-Local-CSV

AdminOfThings · Accepted Answer

If it is a memory issue then the syntax $NewCSVObject += $_ is doing you no favors at all. I would make at least the following modifications.

# Change the following
$NewCSVObject += $_ # Replace This Line
$_ # Replacement Code

$masterCSV | ForEach-Object { # Replace this Line
$NewCSVObject = $masterCSV | ForEach-Object { # Replacement Code

$NewCSVObject | Export-Csv "$newLocalMaster" -notype # Move this line to outside of the Foreach-Object {} script block.

Explanation:

Using += to effectively add elements to an array creates a new copy of the current array with all of its current elements and then adds the additional items to the output. As the array grows, this operation becomes increasingly costly.

When using a foreach loop or Foreach-Object {} script block, any output produced within those constructs can be stored into a variable by just assigning the variable to the loop or command. The variable will be a collection if there is more than one output. Below is sample syntax to illustrate the concept.

$variable = foreach ($item in $Collection) { 
    $item # $item is output but stored in $variable instead of being sent to stdout/console
}

$variable = $collection | Foreach-Object { 
    $_ # The current pipeline object is output but stored in $variable instead of being sent to stdout/console
}

You only need to output to your CSV file at the end of the loop activities provided other system constraints don't prevent you from doing so. If you output to CSV continuously, you need to use the -Append switch on Export-Csv to prevent overwriting of previous data.

PowerShell CSV Script Timing Out

Answers (2)

Related Questions