Replace string several csv files

PowerShell Help
1

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

2

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