.net - How can i replace data in multiple .txt files at once in Powershell?

Question

I have multiple .txt files in a folder, which have a specific data format in which every line, starts from 3 digit numbers, example say.

101,3333,35899,BufferC1,99,02,333
102,3344,30079,BufferD2,89,03,444
.... and so on.

Now, if the record starts with "101", then I have to replace the 3rd & 5th index element of the same line with some text. Once all files are replaced, then I have to copy all modified files to another directory/folder on the same machine.

I have written some code which is not working. Please help me as I am a newbie in powershell. Am I working on a correct approach?

 $FileNamesList = @(Get-ChildItem C:TestFiles*.txt | Select-Object -ExpandProperty Name)
    
     for($i=0; $i -lt $FileNamesList.count; $i++)
        {      
            # original folder path
            $FilePath1 = 'C:TestFiles' + $FileNamesList[$i]
            
            #modified files after text is replaced will be copied in another folder
            $FilePath2 = 'C:TestFilesModified' + $FileNamesList[$i]   
    
            $OriginalFileData = Get-Content $FilePath1
    
        # Is this correct, can i assign foreach cmd result to a variable?
        $ModifiedFileData= ForEach($Row in $OriginalFileData ) 
                    {                 
                         If($Row.Split(",")[0] -eq "101") 
                         {
                            $Row -replace($Row.Split(",")[3]),"Test File"
                            $Row -replace($Row.Split(",")[5]),"Test Data"
                         } 

                     else{
                       $Row
                        }

             Out-File -FilePath $FilePath2 -InputObject $ModifiedFileData
            }

Answer 1

I like @Theo's approach. I starting thinking along the same lines and writing similarly minded code. However, I got hung up on the CSV idea and ended up with a few alternate approaches to share.

There are a few of issues with a CSV approach:

1. Lack of headers
2. Difficulty re-exporting because Export-Csv doesn't have a -NoHeader parameter
3. Dealing with a variable number of columns as mentioned in the comments

My first approach to this was to use the ConvertFrom-String cmdlet to get formal objects out of the raw data.

$SourceFolder      = "C:TempSourceFolder"
$DestinationFolder = "C:Tempdestination"

ForEach( $File in Get-ChildItem $SourceFolder -Filter *.txt )
{
    $DestinationFile = Join-Path -Path $DestinationFolder -ChildPath $File.Name
    
    $File | 
    Get-Content | 
    ConvertFrom-String -Delimiter "," | 
    ForEach-Object{
        If( $_.P1 -eq 101 ) {
            $_.P3 = "P3 Replacement Value"
            $_.P5 = "P5 Replacement Value"
        }
        $_.PSObject.Properties.Value -join "," # Output from the loop
    } |
    Set-Content -Path $DestinationFile
}

The advantage of this is you don't need to know how many fields there are in any 1 file or even any 1 row of a given file. ConvertFrom-String will simply add extra properties for a given row. Unrolling the values with $_.PSObject.Properties.Value also allows for an arbitrary number of properties with limited code.

The Second approach requires knowing the maximum number of columns. For the purposes of the example, assume we have a variable number of columns, but never more than say 8. We can use the -Header argument with the Import-Csv command.

$Header            = "P1","P2","P3","P4","P5","P6","P7","P8"
$SourceFolder      = "C:TempSourceFolder"
$DestinationFolder = "C:Tempdestination"

ForEach( $File in Get-ChildItem C:	empSourceFolder -filter *.txt )
{
    $DestinationFile = Join-Path -Path $DestinationFolder -ChildPath $File.Name
    
    Import-Csv -Path $File.FullName -Header $Header |
    ForEach-Object{
        If( $_.P1 -eq 101 ) {
            $_.P3 = "P3 Replacement Value"
            $_.P5 = "P5 Replacement Value"
        }
        ($_.PSObject.Properties.Value -join ",").TrimEnd(",") # Output from the loop
    } |
    Set-Content -Path $DestinationFile
}

Note: I supposed even if you didn't know the maximum number of fields you could assign big array to the $Header variable.

Note: The .TrimEnd(",") method. Since there may be fewer fields on agiven line than there are elements in the $Header array, Import-Csv wil add a property and assign it a null value. In turn this may result in extra commas in the string resulting from the -join.

WARNING: This may also pose a problem if there are legitimately null trailing fields.

Finally, I did figure out a way to leverage the *-Csv cmdlets a little further, but it only works with the ConvertFrom-String example:

$SourceFolder      = "C:TempSourceFolder"
$DestinationFolder = "C:Tempdestination"

ForEach( $File in Get-ChildItem $SourceFolder -Filter *.txt )
{
    $DestinationFile = Join-Path -Path $DestinationFolder -ChildPath $File.Name
    
    $File | 
    Get-Content | 
    ConvertFrom-String -Delimiter "," | 
    ForEach-Object{
        If( $_.P1 -eq 101 ) {
            $_.P3 = "P3 Replacement Value"
            $_.P5 = "P5 Replacement Value"
        }
        $_
    } |
    ConvertTo-Csv -NoTypeInformation |
    Select-Object -Skip 1 |
    Set-Content -Path $DestinationFile
}

This doesn't need a -join expression, however it's at the expense of the additional Select-Object pipeline.