Link to home
Start Free TrialLog in
Avatar of E=mc2
E=mc2Flag for Canada

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)$($outSuffix)$($_.Extension)"
      $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.ContainsKey($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,1775478,,20181003,20181107,20181114,,1234567890000,OTHERCOMP ACCOUNTS PAYABLE,123 ROAD WAY,,FOREST,TX,01234-5678,,,,1234564870004,Another Warehouse, 300 Apt View Court NW,Specific Place,TX,12345-5678,,,018-441-5522,41245,DEFORPBC,,USD,N,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,00987894564560,00456,152322,CANVAS,,,,123.00,US,0.00,US,0.00,US,10.5200,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
5,A,01234,55555,Y,1254/2,1771234,,20181003,20181107,20181114,,1234567890000,OTHEROTHERCOMP ACCOUNTS PAYABLE,123 ROAD WAY,,JUNGLE,GA,01234-9999,,,,1234564870004,BAnother Warehouse, 400 Sea View Court NW,AnotherSpecific Place,GA,12345-9999,,,018-441-5522,41245,GHIORPBC,,USD,N,,,,,,,,,,N,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,00987894564560,00456,152322,CANVAS,,,,123.00,US,0.00,US,0.00,US,10.5200,0.0000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Avatar of oBdA
oBdA

This should do the trick:
$inFolder = '\\saved\data'
$outSuffix = '_rev'
$replace_67891 = @{
	'SPECIFIC PLACE' = '01-US'
}
$addLastField = @{
	'12345' = '100'
	'67891' = '500'
}

$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)$($outSuffix)$($_.Extension)"
	$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))!"
		}
		$fields = $fields + ''
		$old3 = $fields[3]
		If ($addLastField.Keys -contains $fields[3]) {
			$fields[-1] = $addLastField[$fields[3]]
			$fieldChanged = $true
		}
		If ($fields[3] -eq '67891') {
			If ($replace_67891.ContainsKey($fields[26])) {
				$fields[3] = $replace_67891[$fields[26]]
				$fieldChanged = $true
			}
		}

		If ($fieldChanged) {
			Write-Host "    line $($row): $($old3) [$($fields[26])]) --> $($fields[3]); last: $($fields[-1])"
			$saveFile = $true
		}
		$fields -join ','
	}
	If ($saveFile) {
		$content | Set-Content -Path $outFile
		Write-Host "Changes written to $($outFile)."
	} Else {
		Write-Host "No changes found."
	}
}

Open in new window

Avatar of E=mc2

ASKER

Thanks so much let me try it now..
Avatar of E=mc2

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
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of E=mc2

ASKER

Perfect, this works thanks so much.