Is this the best way to replace text in all of an object's properties in powershell?

Question

I have a large CSV file in which some fields have a new line embedded. Excel 2016 produces errors when importing a CSV with rows which have fields with a new line embedded.

Based on this post, I wrote code to replace any new line in any field with a space. Below is a code block that duplicates the functionality and issue. Option 1 works. Option 2, which is commented out, casts my object to a string. I was hoping Option 2 might run faster.

Question: Is there a better way to do this to optimize for performance processing very large files?

$array = @([PSCustomObject]@{"ID"="1"; "Name"="Joe`nSmith"},
           [PSCustomObject]@{"ID"="2"; "Name"="Jasmine Baker"})

$array = $array | ForEach-Object {
    #Option 1: produces an Object, but is code optimized?
    foreach ($n in $_.PSObject.Properties.Name) {
        $_.PSObject.Properties[$n].Value = ` 
             $_.PSObject.Properties[$n].Value -replace "`n"," "
    }

    #Option 2: produces a string, not an object
    #$_ = $_ -replace "`n"," " 

    $_
 }

Keep in mind that in my real-world use case, each row has > 15 fields and any combination of them may have one or more new lines embedded.

I read somewhere that ForEach is faster than ForEach-Object. You could do some testing of both scenarios you mention with the stopwatch class. Utilize it like so: $resultstime = new-timespan -Minutes 1 $resultstime = [diagnostics.stopwatch]::StartNew() $resultstime.Elapsed Remember to stop it :) — Martin Sandgaard Rasmussen
– Martin Sandgaard Rasmussen, Commented May 29, 2017 at 14:39
Thanks Martin. I'll use the stopwatch class and time my original code compared with the suggestions from @wOxxOm. — D England
– D England, Commented May 29, 2017 at 21:57
Using my data set which is about 16.5 MB and 25,000 rows, the ForEach-Object loop ran in 36 seconds. The foreach loop ran in 11. — D England
– D England, Commented May 30, 2017 at 13:12

woxxom · Accepted Answer · 2017-05-29 14:39:31Z

2

Use the fast TextFieldParser to read, process, and build the CSV from the file (PowerShell 3+):

[Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') >$null
$parser = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser 'r:\1.csv'
$parser.SetDelimiters(',')
$header = $parser.ReadFields()

$CSV = while (!$parser.EndOfData) {
    $i = 0
    $row = [ordered]@{}
    foreach ($field in $parser.ReadFields()) {
        $row[$header[$i++]] = $field.replace("`n", ' ')
    }
    [PSCustomObject]$row
}

Or modify each field in-place in an already existing CSV array:

foreach ($row in $CSV) {
    foreach ($field in $row.PSObject.Properties) {
        $field.value = $field.value.replace("`n", ' ')
    }
}

Notes:

foreach statement is much faster than piping to ForEach-Object (also aliased as foreach)
$stringVariable.replace() is faster then -replace operator

answered May 29, 2017 at 14:39

woxxom

75.2k15 gold badges161 silver badges164 bronze badges

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

4 Comments

D England Over a year ago

Thanks for the detailed reply. I'll try both of these approaches and see which is faster.

D England Over a year ago

foreach is definitely faster than Foreach-Object. the replace loop takes 36 seconds with ForEach-Object and only 11 seconds with a foreach loop.

D England Over a year ago

I also give up on trying to use -replace on an object. It seems to only turn the result into a string. 11 seconds is plenty fast for a twice weekly script.

zett42 Over a year ago

Just to link questions, I've written a similar answer for a different question, where I wrapped TextFieldParser in a reusable function.

Collectives™ on Stack Overflow

Is this the best way to replace text in all of an object's properties in powershell?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related