E=mc2
asked on
Modify Powershell fiile to add a field in a .csv file
Modify the PS Script below, sot that depending on what it sees in position 4, it will add a field.
For example:
If it sees 12345, it will add a ",100"
If it sees 67891, it will add a ".500"
(without the quotations).
[code][/$inFolder = '\\saved\data'
$outSuffix = '_rev'
$replace_67891 = @{
'SPECIFIC PLACE' = '01-US'
}
$referenceCount = 0
$row = 0
Get-ChildItem -Path $inFolder -Filter *.csv | Where-Object {$_.BaseName -notmatch "$($outSuffix)\Z"} | ForEach-Object {
$outFile = Join-Path -Path $_.DirectoryName -ChildPath "$($_.BaseName)$($outSuffi x)$($_.Ext ension)"
$saveFile = $false
Write-Host "Processing '$($_.Name)' ..."
$content = Get-Content -Path $_.FullName | ForEach-Object {
$fieldChanged = $false
$row++
$fields = $_.Split(',')
If ($referenceCount -eq 0) {$referenceCount = $fields.Count}
If ($fields.Count -ne $referenceCount) {
Throw "Field count in line $($row) is incorrect (is: $($fields.Count), expected: $($referenceCount))!"
}
$old3 = $fields[3]
If ($fields[3] -eq '67891') {
If ($replace_67891.ContainsKe y($fields[ 26])) {
$fields[3] = $replace_67891[$fields[26] ]
$fieldChanged = $true
}
}
If ($fieldChanged) {
Write-Host " line $($row): $($old3) [$($fields[26])]) --> $($fields[3])"
$saveFile = $true
}
$fields -join ','
}
If ($saveFile) {
$content | Set-Content -Path $outFile
Write-Host "Changes written to $($outFile)."
} Else {
Write-Host "No changes found."
}
}code]
Below is a sample of the ,csv file it is looking at:
5,A,01234,67891,Y,1253/2,1 775478,,20 181003,201 81107,2018 1114,,1234 567890000, OTHERCOMP ACCOUNTS PAYABLE,123 ROAD WAY,,FOREST,TX,01234-5678, ,,,1234564 870004,Ano ther Warehouse, 300 Apt View Court NW,Specific Place,TX,12345-5678,,,018- 441-5522,4 1245,DEFOR PBC,,USD,N ,,,,,,,,,, N,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,0098789 4564560,00 456,152322 ,CANVAS,,, ,123.00,US ,0.00,US,0 .00,US,10. 5200,0.000 0,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,
5,A,01234,55555,Y,1254/2,1 771234,,20 181003,201 81107,2018 1114,,1234 567890000, OTHEROTHER COMP ACCOUNTS PAYABLE,123 ROAD WAY,,JUNGLE,GA,01234-9999, ,,,1234564 870004,BAn other Warehouse, 400 Sea View Court NW,AnotherSpecific Place,GA,12345-9999,,,018- 441-5522,4 1245,GHIOR PBC,,USD,N ,,,,,,,,,, N,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,0098789 4564560,00 456,152322 ,CANVAS,,, ,123.00,US ,0.00,US,0 .00,US,10. 5200,0.000 0,,,,,,,,, ,,,,,,,,,, ,,,,,,,,,, ,,,
For example:
If it sees 12345, it will add a ",100"
If it sees 67891, it will add a ".500"
(without the quotations).
[code][/$inFolder = '\\saved\data'
$outSuffix = '_rev'
$replace_67891 = @{
'SPECIFIC PLACE' = '01-US'
}
$referenceCount = 0
$row = 0
Get-ChildItem -Path $inFolder -Filter *.csv | Where-Object {$_.BaseName -notmatch "$($outSuffix)\Z"} | ForEach-Object {
$outFile = Join-Path -Path $_.DirectoryName -ChildPath "$($_.BaseName)$($outSuffi
$saveFile = $false
Write-Host "Processing '$($_.Name)' ..."
$content = Get-Content -Path $_.FullName | ForEach-Object {
$fieldChanged = $false
$row++
$fields = $_.Split(',')
If ($referenceCount -eq 0) {$referenceCount = $fields.Count}
If ($fields.Count -ne $referenceCount) {
Throw "Field count in line $($row) is incorrect (is: $($fields.Count), expected: $($referenceCount))!"
}
$old3 = $fields[3]
If ($fields[3] -eq '67891') {
If ($replace_67891.ContainsKe
$fields[3] = $replace_67891[$fields[26]
$fieldChanged = $true
}
}
If ($fieldChanged) {
Write-Host " line $($row): $($old3) [$($fields[26])]) --> $($fields[3])"
$saveFile = $true
}
$fields -join ','
}
If ($saveFile) {
$content | Set-Content -Path $outFile
Write-Host "Changes written to $($outFile)."
} Else {
Write-Host "No changes found."
}
}code]
Below is a sample of the ,csv file it is looking at:
5,A,01234,67891,Y,1253/2,1
5,A,01234,55555,Y,1254/2,1
ASKER
Thanks so much let me try it now..
ASKER
Thanks again. I guess I should have asked in stead of doing a replace on line 3, it should have simply looked at position 4 and if it saw 12345 or 67891 then it should have added an extra field at the end of that line.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, this works thanks so much.
Open in new window