CODEWITHSUNDEEP
powershell
Jagbir Singh
Jagbir Singh

Reputation: 39

How to replace a column (for ex 5th column) in text file (tab delimited) using Windows PowerShell

My files consists of following data (no header). Lets take 2 files

b1.txt

DEPOSIT ADD     123456789 (VALUE)(VARIABLE) NNNN    VALUEVARIABLE                                                   
DEPOSIT ADD     234567890(P75)  NNNN    234567890(P75)

b2.txt

DEPOSIT ADD     345678901(P75) NNNN    345678901(P75)

These are tab delimited text files. There are total of 5 columns in each file. (123456789 (VALUE)(VARIABLE) is a single value column).

My requirements are:

  1. I need to fetch only the row which contains P75 to update in the same file.
  2. I have to replace the 5th column with VTGSPVTG (complete value with P75 only) (234567890(P75)--> VTGSPVTG)

My script's try command is as follows.

$CurrFilePath : C:\testing
$TransIncludePattern =b*.txt
try
{ 
    $CurrFileName = Get-childitem -Path $CurrFilePath\* -Include $TransIncludePattern | Sort-Object LastWriteTime;
    $CurrFileName | 
        Select-Object -ExpandProperty FullName | 
        ForEach-Object {
            Write-Host "Changing file $_";
            $content = (Get-Content $_);
            $newContent = $content | ForEach-Object {if ($_ -match '\(P75\)'){
                ($_ -replace '\(P75\)','(VTG)(SPVTG)')} else {$_} ;
            }
            $newContent | Set-Content $_;

            Write-Host "File Changed: $_";
        }
    Write-Host "P75 values are now replaced";

    Copy-Item $CurrFileName -Destination $OutFilePath -Force;
    Move-Item $CurrFileName -Destination $ProcessFilePath -Force;
      
     
    Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())";

    exit 0;
}

Can any one help me change the "if" statement so that my second requirement mentioned above is fulfilled?

Thank You

Upvotes: 0

Views: 280

Answers (1)

Doug Maurer
Doug Maurer

Reputation: 8868

Like both Lee and Mathias commented, bringing the data in as a well formed object would make this simpler. Based on your sample data and description, I believe this will do what you're looking for.

$currFilePath = 'C:\Temp'
$TransIncludePattern = 'b*.txt'

Get-ChildItem $currFilePath -File -Include $TransIncludePattern | foreach {
    Write-Host "Changing file $($_.fullname)"
    $CSVData = Import-Csv $_.FullName -Delimiter "`t" -Header First,Second,Third,Fourth,Fifth
    $CSVData | where fifth -Match '(P75)' | foreach {$_.fifth = $_.fifth -replace '\(P75\)','(VTG)(SPVTG)'}
    $csvdata.psobject.properties.value -join "`t" | Set-Content $_.FullName -Encoding UTF8
}

There are other approaches such as Select-String that may work but without knowing more about the data, it probably wouldn't be as reliable as the method above.

Upvotes: 1

Related Questions