Author Posts

June 23, 2016 at 6:38 pm

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

June 23, 2016 at 11:22 pm

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