# Looping through variable and replace text **URL:** \ **Category:** PowerShell Help\ **Created:** [December 20, 2019, 6:03am UTC](https://forums.powershell.org/t/looping-through-variable-and-replace-text/13574 "2019-12-20T06:03:45Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![jdobbsy1987](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/jdobbsy1987/32/23_2.png) [@jdobbsy1987](https://forums.powershell.org/u/jdobbsy1987)\ **Post date:** [December 20, 2019, 6:03am UTC](https://forums.powershell.org/t/looping-through-variable-and-replace-text/13574/1 "2019-12-20T06:03:45Z")
Hi, I’m trying to loop through a variable, split each line by | (giving 4 tokens), then search for “a|b|c” in a file and replace with “a|b|d” Essentially i have a csv file that contains a load of parts and prices and while it’s not ideal to be in csv many people use it so we need to continue using it like this, i want to check each part every day to see if any prices in the csv sheet have changed compared to an API feed. If there is a change in price i want to update the file with the new price, so… The variable data looks like this Token 3 being the old price and 4 being the new price [pre]part1|“part1-description”|160|160.49 part2|“part2-description”|150|160.28[/pre] I run the following and because price $updatesPending evaluates to True it runs the IF statement [pre]If ($updatesPending -eq $true) { # Write updates to file Foreach ($item in $($priceUpdates -Split(“`n”))) { $a,$b,$c,$d = $item.Split(“|”) ((Get-Content -path UNC$fileName.csv) -replace “$a|$b|$c”,“$a|$b|$d”) | Set-Content -Path unc$fileName.csv } }[/pre] The issue is the output in the file looks like this so i’m clearing doing very wrong, possibly the entirely wrong command? [pre]||p||a||r||t||1|||||“||p||a||r||t||1||-||d||e||s||c||r||i||p||t||i||o||n||”|||||1||6||0||.||4||9|||||p||a||r||t||1|||||“||p||a||r||t||1||-||d||e||s||c||r||i||p||t||i||o||n||”|||||1||6||0||.||4||9|||||p||a||r||t||1|||||“||p||a||r||t||1||-||d||e||s||c||r||i||p||t||i||o||n||”|||||1||6||0||.||4||9|| ||p||a||r||t||2|||||“||p||a||r||t||2||-||d||e||s||c||r||i||p||t||i||o||n||”|||||1||6||0||.||2||8|||||p||a||r||t||2|||||“||p||a||r||t||2||-||d||e||s||c||r||i||p||t||i||o||n||”|||||1||6||0||.||2||8|||||p||a||r||t||2|||||“||p||a||r||t||2||-||d||e||s||c||r||i||p||t||i||o||n||”|||||1||6||0||.||2||8||[/pre]   Any help will be appreciated.   Regards, Jamie ---
**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\ **Post date:** [December 20, 2019, 9:11am UTC](https://forums.powershell.org/t/looping-through-variable-and-replace-text/13574/2 "2019-12-20T09:11:06Z")
The -replace statement uses regex for the search pattern and in regex the “ **|** ” represents a logical “ **or** ” - that’s why you fail at the moment. 😉 If you have a valid csv file why don’t you treat it as such and use Import-CSV instead of get content? ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [December 20, 2019, 10:12am UTC](https://forums.powershell.org/t/looping-through-variable-and-replace-text/13574/3 "2019-12-20T10:12:02Z")
Here’s a bit of a different approach. Powershell is strongest when you are leveraging object. While you can do text manipulation, the other file is in a CSV format, so it will be more predictable leveraging it as an object. This approach would take an update file and consider it autho: ``` $file1 = @" part1|"part1-description"|160|160.49 part2|"part2-description"|150|160.28 part5|"part3-description"|20.80|20.50 "@ | ConvertFrom-Csv -Delimiter "|" -Header Id,Desc,CurrentPrice, NewPrice $file2 = @" part1|"part1-description"|160 part2|"part2-description"|150 part3|"part3-description"|110 part4|"part4-description"|800 part5|"part5-description"|20.80 "@ | ConvertFrom-Csv -Delimiter "|" -Header Id,Desc,CurrentPrice #Create a new object from File 1 and make the NewPrice, the Current Price $updates = $file1 | Select Id, Desc, @{Name='CurrentPrice';Expression={$_.NewPrice}}, @{Name='Rank';Expression={'0'}} $master = $file2 | Select Id, Desc, CurrentPrice, @{Name='Rank';Expression={'1'}} # Take the new file and file you want do replaces in and put them in a single object $joined = @() $joined += $updates $joined += $master #Another method to join the object #$joined = Compare-Object -ReferenceObject $master -DifferenceObject $updates -Property CurrentPrice -PassThru -IncludeEqual #Using the part Id as a key, group them together $grouped = $joined | Group-Object -Property Id #Count Name Group #----- ---- ----- # 2 part1 {@{Id=part1; Desc=part1-description; CurrentPrice=160.49; Rank=0}, @{Id=part1; Desc=part1-description; CurrentPrice=160; Rank=1}} # 2 part2 {@{Id=part2; Desc=part2-description; CurrentPrice=160.28; Rank=0}, @{Id=part2; Desc=part2-description; CurrentPrice=150; Rank=1}} # 2 part5 {@{Id=part5; Desc=part3-description; CurrentPrice=20.50; Rank=0}, @{Id=part5; Desc=part5-description; CurrentPrice=20.80; Rank=1}} # 1 part3 {@{Id=part3; Desc=part3-description; CurrentPrice=110; Rank=1}} # 1 part4 {@{Id=part4; Desc=part4-description; CurrentPrice=800; Rank=1}} #You can see that Part1, 2 and 5 have 2 items in the group, so now we can loop thru #each of the groups, Sort the price and Select the highest $results = foreach ($grp in $Grouped) { $grp.Group | Sort-Object -Property Rank | Select -First 1 } #Now you have the Parts with the highest price $results #| #Select-Object -Property Id, Desc, CurrentPrice | #Export-CSV -Path C:\file2.csv -NoTypeInformation ``` The output can be Sorted by PartNumber if you want, but basically if you un-remark the lines under $results you would overwrite the file. You can also make backups of everything along the way. **Output:** ``` Id Desc CurrentPrice Rank -- ---- ------------ ---- part1 part1-description 160.49 0 part2 part2-description 160.28 0 part5 part3-description 20.50 0 part3 part3-description 110 1 part4 part4-description 800 1 ``` ---
**Author:** ![jdobbsy1987](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/jdobbsy1987/32/23_2.png) [@jdobbsy1987](https://forums.powershell.org/u/jdobbsy1987)\ **Post date:** [December 20, 2019, 11:05am UTC](https://forums.powershell.org/t/looping-through-variable-and-replace-text/13574/4 "2019-12-20T11:05:03Z")
> [@](#): > > The -replace statement uses regex for the search pattern and in regex the “ **|** ” represents a logical “ **or** ” – that’s why you fail at the moment. 😉 If you have a valid csv file why don’t you treat it as such and use Import-CSV instead of get content? Hi Olaf, Thank you for highlighting that -replace uses regex, i should have picked up on this but it explains the issue i was having. With regards to why not using Import-Csv… I wasn’t actually aware of it, I’m new to Powershell, I have always used batch but trying to break that habit (finally). After tinkering with Import-Csv I have now managed to get the values to update as intended so thank you very much for that pointer. Being able to reference specific rows / columns using an array is awesome, having used batch files for so long i have been super limited and feel like an idiot for not jumping ship years ago :). > [@](#): > > Here’s a bit of a different approach. Powershell is strongest when you are leveraging object. While you can do text manipulation, the other file is in a CSV format, so it will be more predictable leveraging it as an object. This approach would take an update file and consider it autho: Hi Rob, Thank you for your input again, I hadn’t actually seen your reply until now sorry but will certainly play with this approach too.   Thank you both. ---
**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\ **Post date:** [May 16, 2024, 8:30pm UTC](https://forums.powershell.org/t/looping-through-variable-and-replace-text/13574/5 "2024-05-16T20:30:56Z")