Looping through variable and replace text

Welcome Forums General PowerShell Q&A Looping through variable and replace text

Viewing 3 reply threads
  • Author
    Posts
    • #194606
      Participant
      Topics: 4
      Replies: 5
      Points: 84
      Rank: Member

      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

      part1|"part1-description"|160|160.49
      part2|"part2-description"|150|160.28

      I run the following and because price $updatesPending evaluates to True it runs the IF statement

      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
      }
      }

      The issue is the output in the file looks like this so i'm clearing doing very wrong, possibly the entirely wrong command?

      ||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||

       

      Any help will be appreciated.

       

      Regards,

      Jamie

    • #194627
      Participant
      Topics: 2
      Replies: 1693
      Points: 3,368
      Helping Hand
      Rank: Community Hero

      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?

    • #194636
      Participant
      Topics: 10
      Replies: 1381
      Points: 1,509
      Helping Hand
      Rank: Community Hero

      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   
      
    • #194675
      Participant
      Topics: 4
      Replies: 5
      Points: 84
      Rank: Member

      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.

Viewing 3 reply threads
  • You must be logged in to reply to this topic.