Looping through variable and replace text

PowerShell Help
1

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

2

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. :wink: If you have a valid csv file why don’t you treat it as such and use Import-CSV instead of get content?

3

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
4

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 :).

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.