Home > Enterprise >  Powershell - add string after 14th 24th 36th 46th and 55th character following a word
Powershell - add string after 14th 24th 36th 46th and 55th character following a word

Time:05-14

We have many files which contain header lines beginning with HDR that need to be updated for clarity. I am new to coding in powershell and would like to know how I can update the code below to accomplish this.
Example:
Before:

HDR,9345561421,20220510,1536838657,20220510,5550810,111003

After:

HDR, INV-9345561421, DATE-20220510, PO-1536838657, DATE-20220510, ORDER-5550810, CUST-111003
Get-ChildItem 'C:\SFTP\Whirlpool\Invoices\*.csv' | ForEach {
     (Get-Content $_) | ForEach  {          
    $_.Insert(HDR, ,"INV-").Insert(HDR, .........., ,"NVDATE-").Insert(HDR, .........., ........,  ,"PO-").Insert(HDR, .........., ........, ..........,   ,"PODATE-").Insert(HDR, .........., ........, .........., ........,   ,"ORDER-").Insert(HDR, .........., ........, .........., ........, .......,   ,"SHIPTO-")
} | Set-Content $_
}

The solution below provided by Santiago Squarzon worked perfectly.

$head = 'INV', 'DATE', 'PO', 'DATE', 'ORDER', 'CUST'
foreach($csv in Get-ChildItem 'C:\SFTP\Whirlpool\Invoices\*.csv') {
    $newContent = switch -Regex -File $csv.FullName {
        '^HDR' {
            $i = [ref] 0
            [regex]::Replace($_, ',', { ', '   $head[$i.Value  ]   '-' })
            continue
        }
        Default { $_ }
    }
    Set-Content -LiteralPath $csv.FullName -Value $newContent
}

CodePudding user response:

Assuming you want to replace any line starting with HDR and said line has the same number of items comma-separated (5 commas in total), you could use this call to Replace(String, String, MatchEvaluator):

$head = 'INV', 'DATE', 'PO', 'DATE', 'ORDER', 'CUST'
$line = 'HDR,9345561421,20220510,1536838657,20220510,5550810,111003'
$i = [ref] 0
[regex]::Replace($line, ',', { ', '   $head[$i.Value  ]   '-' })

# Produces this output:
# HDR, INV-9345561421, DATE-20220510, PO-1536838657, DATE-20220510, ORDER-5550810, CUST-111003

You can combine above logic with a switch using the -File parameter to read your files and the -Regex parameter to target the lines starting with HDR (^HDR):

$head = 'INV', 'DATE', 'PO', 'DATE', 'ORDER', 'CUST'
foreach($csv in Get-ChildItem 'C:\SFTP\Whirlpool\Invoices\*.csv') {
    $newContent = switch -Regex -File $csv.FullName {
        '^HDR' {
            $i = [ref] 0
            [regex]::Replace($_, ',', { ', '   $head[$i.Value  ]   '-' })
            continue
        }
        Default { $_ }
    }
    Set-Content -LiteralPath $csv.FullName -Value $newContent
}
  • Related