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
}