Replace string several csv files

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 5 months, 2 weeks ago.

  • Author
    Posts
  • #44568
    Profile photo of Ricardo Bragança
    Ricardo Bragança
    Participant

    Good afternoon,

    I wanted to replace the word "FE" 'for "01" in several csv files that are on board. I have this what I'm doing wrong ??

    $files = Get-ChildItem C:\Users\ricardo.braganca\Desktop\NEW\ -Filter *.txt -Recurse
    $CSVFolder = 'C:\Users\ricardo.braganca\Desktop\NEW\*.csv'
    $OutputFile = 'C:\Users\ricardo.braganca\Desktop\NEW\dados1.txt'
    $OutputFile2 = 'C:\Users\ricardo.braganca\Desktop\NEW\dados2.txt'

    # Group objects by number
    foreach ($file in $files){
    $groups = Get-Content $file.FullName | ConvertFrom-Csv -Delimiter '|' -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10,H11,H12,H13,H14,H15,H16,H17,H18,H19,H20,H21,H22,H23,H24,H25,H26,H27,H28,H29,H30,H31,H32,H33,H34,H35,H36,H37,H38,H39,H40,H41,H42,H43,H44,H45,H46,H47,H48,H49,H50,H51,H52,H53,H54,H55,H56,H57,H58,H59,H60,H61,H62,H63,H64,H65,H66,H67,H68,H69,H70,H71,H72,H73,H74,H75,H76,H77,H78,H79,H80 |
    Group-Object -Property H9
    $groups | ForEach-Object {If ($_.group.H1 -like 'FE' -AND $_.group.H14 -like 'MAI000' -AND $_.group.H1 -like '01' -and $_.group.H14 -like 'MAIFIM'){
    $_.group | Export-Csv "C:\Users\ricardo.braganca\Desktop\NEW\$($_.Name).csv" -NoTypeInformation -Encoding Unicode }}
    }

    #join files in txt
    $CSV= @();

    Get-ChildItem C:\Users\ricardo.braganca\Desktop\NEW\$($_.Name).csv | ForEach-Object { {$_ -replace '"FE"' ,'"01"' }
    $CSV += @(Import-Csv -Path $_)

    }

    $CSV | Export-Csv -Path $OutputFile -NoTypeInformation -Force -Encoding Unicode

    #Remove csv files

    Remove-Item -Path $CSVFolder -Filter *.csv -Force -Recurse

  • #44612
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Looking at you logic, it looks like you are doing:

    • Parse logs in a directory on the pipe delimiter
    • Generate a single CSV from all logs
    • Capture groups based on filtered criteria

    I created 3 log files with the following:

    2016-06-23T18:15:50|user1|cmp1
    2016-06-23T18:15:50|user3|cmp5
    2016-06-23T18:15:50|user3|cmp3
    2016-06-23T18:15:50|user1|cmp3
    2016-06-23T18:15:50|user6|cmp2
    2016-06-23T18:15:50|user7|cmp1
    2016-06-23T18:15:50|user8|cmp3
    2016-06-23T18:15:50|user2|cmp1
    

    Take a look at this approach.

    • Put all of the log files into single object.
    • Group objects and filter based on contents of grouped items
    • Replace p1 with pp11
      #Compile all logs into a single compiled result
      $compiledResult = foreach ($file in (Get-ChildItem "C:\Users\Rob\Desktop\Logs")) {
          #dynamically generate the header
          $header = @()
          $header = for($i=1;$i -le ((Get-Content $file.FullName)[0]).Split("|").Count;$i++) {
             "H{0}" -f $i 
          }
          #Import the file as a CSV with the pipe delimiter and our dynamic header
          Import-CSV -Path $file.FullName -Delimiter "|" -Header $header
      }
      
      #Do your grouping and then filter the group with a Where clause
       $groupResult = foreach ($matchedGroup in $compiledResult | Group-Object -Property h2 | Where{$_.Group.h3 -eq 'cmp1'}) {
          $matchedGroup.Group
       }
      
      #Use a calculated expression to do a replace
      $final = $groupResult | Select H1, H2, @{Name="H3";Expression={$_.H3.Replace("p1", "pp11")}}
      #Export a CSV
      $final | Export-CSV C:\Users\Rob\Desktop\final.csv -NoTypeInformation
      

      Results:

      H1                  H2    H3    
      --                  --    --    
      2016-06-23T18:15:50 user1 cmpp11
      2016-06-23T18:15:50 user1 cmp3  
      2016-06-23T18:15:50 user1 cmpp11
      2016-06-23T18:15:50 user1 cmp3  
      2016-06-23T18:15:50 user1 cmpp11
      2016-06-23T18:15:50 user1 cmp3  
      2016-06-23T18:15:50 user7 cmpp11
      2016-06-23T18:15:50 user7 cmpp11
      2016-06-23T18:15:50 user7 cmpp11
      2016-06-23T18:15:50 user2 cmpp11
      2016-06-23T18:15:50 user2 cmpp11
      2016-06-23T18:15:50 user2 cmpp11
      

You must be logged in to reply to this topic.