Update Multiple CSV files with a find and replace loop-CodePudding

I have multiple CSV files I need to recursively find and replace text inside of and am looking for the simplest way to do it. I know of .NET with [io.file]::readalltext, Get-Content, and Import-CSV, but no matter the method all of the examples point towards a specific CSV and not an array of them and I'm having a hard time translating any of them to a loop.

I'm just replacing a value "X" with a value of "Y". The CSV files have headers I can use and I figure Import-CSV and .Replace would be the easiest and sanest method.

For starters,

I know the IO method for a singular file for what I want to achieve is:

[io.file]::readalltext("C:\Temp\test.csv").replace("X","Y") | Out-File "C:\Temp\test.csv" -Encoding ascii –Force

Same for Get-Content:

(Get-Content "C:\Temp\test.csv").replace('X', 'Y') | Set-Content "C:\Temp\test.csv"

And a foreach loop:

$source = "C:\Temp\test.csv"

$csv = Import-Csv $source

foreach ($line in $csv) {
    $line.'COLUMNA' = $line.'COLUMNA'.Replace("X","Y")
}

$csv | Export-Csv $source'.bak' -NoTypeInformation

I just seem to be having a hard time grasping how to form the collection part of the foreach() so that I can do multiple files.

foreach ($<item> in $<collection>){<statement list>

I do have a script that's importing these files and making some changes already that I'll try to incorporate the best answer into, but it would be great if it was listed by itself so that anyone else could come along and understand it better without extra variables mucking it up.

Get-ChildItem -Path $source -Recurse -Include *.csv | ForEach-Object {
    $path = $_.FullName;
    $_.FullName | Import-Csv | ForEach-Object {
        $_.'COLUMND' = $_.'COLUMNB' '-' $_.'COLUMNC';
        $_
    } | Export-Csv -NoTypeInformation -Path $path'.bak'
}

Edited: I've tried

Get-ChildItem -Path $source -Include *.csv | ForEach-Object {
  $path = $_.FullName;
  $_.FullName | Import-Csv | ForEach-Object {
      foreach($line in Import-CSV $path){
        $line.'COLUMNA' = $line.'COLUMNA'.Replace("X","Y")
      }
  } | Export-Csv -NoTypeInformation -Path $path'.bak'
}

But that fails.

CodePudding user response：

If I understand correctly, this code should solve your issue, see inline comments for details:

# get all CSVs in `$source` and enumerate them
Get-ChildItem -Path $source -Filter *.csv | ForEach-Object {
    $_ | Import-Csv | ForEach-Object {
        $columnD = '{0}-{1}' -f $_.COLUMNB, $_.COLUMNC
        # using this method ensures that
        # - if the `COLUMND` does not exist it will be added
        # - if the `COLUMND` exists, it will be replaced with a new value
        $_.PSObject.Properties.Add([psnoteproperty]::new('COLUMND', $columnD))
        
        # update `COLUMNA` replacing all `X` with `Y`
        $_.COLUMNA = $_.COLUMNA.Replace('X', 'Y')

        # output the updated object
        $_
    } | Export-Csv ($_.FullName   '.bak') -NoTypeInformation
}