Looking for help optimizing a Powershell script to parse through a CSV line by line

Question

I have a large csv file with 41000 rows of data. Within this I have to go through and inspect each row for a particular string PRCXX and if the row contains that in column 6 then I take the value of column 10 and replace the value in column 9 with it.

I have code that works however it takes a very long time to parse through line by line. I am looking for some help to try and optimize it. I have tried switching to a ForEach loop however I am not sure how exactly to get it to work with what I am trying to accomplish and haven't been able to find any examples to work from.

Here is the code that I have that currently is working just takes along time to complete.

Import-Csv $TransFile -Header 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 | ForEach-Object {
    if ($_.6 -match "PRCXX") {
    $_.9 = $_.10
    } 
$_ | Export-Csv test2.csv -NoTypeInformation -Append -Delimiter ","
}

Thanks for any help that you can provide.

The most time consumptive is a disk operation. Move the | Export-Csv test2.csv -NoTypeInformation -Append -Delimiter "," out from ForEach-Object loop body. — JosefZ
– JosefZ, Commented Aug 20, 2019 at 18:40
Wow that fixed it. Thanks for pointing that out can't believe I didn't catch that. — Jason B
– Jason B, Commented Aug 20, 2019 at 18:47

Jason B · Accepted Answer · 2019-08-20 18:48:38Z

2

Thanks to JosefZ this is fixed. Moved the Export to occur outside the loop and that cleared it up.

Import-Csv $TransFile -Header 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 | ForEach-Object {
    if ($_.6 -match "PRCXX") {
    $_.9 = $_.10
    } 
$_ 
} | Export-Csv test2.csv -NoTypeInformation -Append -Delimiter ","

answered Aug 20, 2019 at 18:48

Jason B

211 bronze badge

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

1 Comment

JosefZ Over a year ago

Please consider marking your own answer as accepted. See this page for an explanation of why this is important.

Collectives™ on Stack Overflow

Looking for help optimizing a Powershell script to parse through a CSV line by line

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related