0

I am going to try to break this simply...

The problem description

2 csv files (destination file gets its fields automatically filled in by other csv files)

I have a source csv file with 3 rows containing:

sourceColumn1,sourceColumn2,sourceColumn3,sourceColumn4,sourceColumn5,sourceColumn6,sourceColumn7    ,sourceColumn8    ,sourceColumn9,sourceColumn10,sourceColumn11,sourceColumn12,sourceColumn13,sourceColumn14,sourceColumn15,sourceColumn16
sourceValue1 ,value2a      ,value3a      ,value4a      ,value5a      ,             ,value7a          ,sourceValueFound1,value9a      ,              ,              ,value12a      ,              ,              ,              ,
value1b      ,value2b      ,value3b      ,value4b      ,value5b      ,sourceValue2 ,                 ,sourceValueFound2,value9b      ,              ,              ,              ,value13b      ,              ,              ,
value1c      ,             ,value3c      ,value4c      ,value5c      ,sourceValue3 ,                 ,sourceValueFound3,value9c      ,              ,              ,              ,              ,              ,sourceValue3  ,

I have a destination csv file (before code is run) with 3 rows containing:

"destinationColumn1","destinationColumn2","destinationColumn3","destinationColumn4","destinationColumn5","destinationColumn6"
"value1aa"          ,"value2aa"          ,"value3aa"          ,"sourceValue1"      ,"value5aa"          ,"AdValueFound1"
"value1bb"          ,"sourceValue2"      ,"value3bb"          ,"value4bb"          ,"sourceValue2"      ,"AdValueFound2"
"value1cc"          ,"sourceValue3"      ,"value3cc"          ,"value4cc"          ,"value5cc"          ,"No SourceValueFound"

The End Goal

I wish to add a column destinationColumn7 in the destination file with the where(values -ne $null) in sourceColumn8 from the source file.

Matching the correct values is done by finding the sourceValue# from the source file and matching it with the sourceValue# in the destination file.

So the destination file, should look like this:

"destinationColumn1","destinationColumn2","destinationColumn3","destinationColumn4","destinationColumn5","destinationColumn6" ,"destinationColumn7"
"value1aa"          ,"value2aa"          ,"value3aa"          ,"sourceValue1"      ,"value5aa"          ,"AdValueFound1"      ,"sourceValueFound1"
"value1bb"          ,"sourceValue2"      ,"value3bb"          ,"value4bb"          ,"sourceValue2"      ,"AdValueFound2"      ,"sourceValueFound2"
"value1cc"          ,"sourceValue3"      ,"value3cc"          ,"value4cc"          ,"value5cc"          ,"No SourceValueFound","sourceValueFound3"

What I have tried so far

To do this, I am using the following code...

Write-Host "Creating hash table with columns `"sourceColumn1`" and `"sourceColumn8`" From $sourceCsvFile"
$FirstHashTable = @{}
Import-Csv $sourceCsvFile | ForEach-Object {
    $FirstHashTable[$_.sourceColumn1] = If ($_.sourceColumn8) {$_.sourceColumn8} Else {'No SourceValueFound'}}
Write-Host "Complete."

Write-Host "Appending new destinationColumn7 column from hash table"
(Import-Csv $destinationCsvFile) |
    Select-Object -Property *, @{n='destinationColumn7';e={
    If ($FirstHashTable.ContainsKey($_.destinationColumn4)){
        $FirstHashTable[$_.destinationColumn4]
    } ElseIf ($FirstHashTable.ContainsKey($_.destinationColumn5)){
        $FirstHashTable[$_.destinationColumn5]
    } ElseIf ($FirstHashTable.ContainsKey($_.destinationColumn1)){
        $FirstHashTable[$_.destinationColumn1]
    } Else {
        'No MatchFound'
    }}} | Export-Csv "$destinationCsvFile-Temp" -NoType
Move-Item "$destinationCsvFile-Temp" $destinationCsvFile -Force
Write-Host "Complete."

The result of the destination file:

"destinationColumn1","destinationColumn2","destinationColumn3","destinationColumn4","destinationColumn5","destinationColumn6" ,"destinationColumn7"
"value1aa"          ,"value2aa"          ,"value3aa"          ,"sourceValue1"      ,"value5aa"          ,"AdValueFound1"      ,"sourceValueFound1"
"value1bb"          ,"sourceValue2"      ,"value3bb"          ,"value4bb"          ,"sourceValue2"      ,"AdValueFound2"      ,"No SourceValueFound"
"value1cc"          ,"sourceValue3"      ,"value3cc"          ,"value4cc"          ,"value5cc"          ,"No SourceValueFound","No SourceValueFound"

As we can see, this did not append the last 2 rows with the sourceValueFound# under destinationColumn7. Instead they are No SourceValueFound.

Next I noticed the sourceValue# was in a different column in the source file, and the sourceValueFound# was not $null

So I made a change to the code...

$sourceCsvFile = 'C:\Temp\test1.csv'
$destinationCsvFile = 'C:\Temp\test2.csv'

Write-Host "Creating hash table with columns `"sourceColumn1`" and `"sourceColumn8`" From $sourceCsvFile"
$FirstHashTable = @{}
Import-Csv $sourceCsvFile | ForEach-Object {
$FirstHashTable[$_.sourceColumn1] = If ($_.sourceColumn8) {$_.sourceColumn8} Else {'No SourceValueFound'}}
Write-Host "Complete."

Write-Host "Creating hash table with columns `"sourceColumn6`" and `"sourceColumn8`" From $sourceCsvFile"
$SecondHashTable = @{}
Import-Csv $sourceCsvFile | ForEach-Object {
$SecondHashTable[$_.sourceColumn6] = If ($_.sourceColumn8) {$_.sourceColumn8} Else {'No SourceValueFound'}
}
Write-Host "Complete."


Write-Host "Appending new destinationColumn7 column from hash table"
(Import-Csv $destinationCsvFile) |
    Select-Object -Property *, @{n='destinationColumn7';e={
    If (($FirstHashTable.ContainsKey($_.destinationColumn4)) -and ($FirstHashTable.ContainsKey($_.destinationColumn4) -ne 'No SourceValueFound')) {
        $FirstHashTable[$_.destinationColumn4]
    } ElseIf (($FirstHashTable.ContainsKey($_.destinationColumn5)) -and ($FirstHashTable.ContainsKey($_.destinationColumn5) -ne 'No SourceValueFound')){
        $FirstHashTable[$_.destinationColumn5]
    } ElseIf (($FirstHashTable.ContainsKey($_.destinationColumn2)) -and ($FirstHashTable.ContainsKey($_.destinationColumn2) -ne 'No SourceValueFound')){
        $FirstHashTable[$_.destinationColumn2]
    } ElseIf ($SecondHashTable.ContainsKey($_.destinationColumn4)){
        $SecondHashTable[$_.destinationColumn4]
    } ElseIf ($SecondHashTable.ContainsKey($_.destinationColumn5)){
        $SecondHashTable[$_.destinationColumn5]
    } ElseIf ($SecondHashTable.ContainsKey($_.destinationColumn2)){
        $SecondHashTable[$_.destinationColumn2]
    } Else {
        'No MatchFound'
    }}} | Export-Csv "$destinationCsvFile-Temp.csv" -NoType
Write-Host "Complete."

The result of the destination file:

"destinationColumn1","destinationColumn2","destinationColumn3","destinationColumn4","destinationColumn5","destinationColumn6" ,"destinationColumn7"
"value1aa"          ,"value2aa"          ,"value3aa"          ,"sourceValue1"      ,"value5aa"          ,"AdValueFound1"      ,"No SourceValueFound"
"value1bb"          ,"sourceValue2"      ,"value3bb"          ,"value4bb"          ,"sourceValue2"      ,"AdValueFound2"      ,"sourceValueFound2"
"value1cc"          ,"sourceValue3"      ,"value3cc"          ,"value4cc"          ,"value5cc"          ,"No SourceValueFound","No SourceValueFound"

Now it adds the sourceValueFound# in the 2nd row of the destination file, but the 3rd row is still No SourceValueFound for destinationColumn7.

Anyone who can help me fill in what I am missing would be much appreciated.

Improve this question

1 Answer 1

Reset to default
0

I found the answer to this, i was not skipping the keys containing values of 'No SourceValueFound', because I was using ($FirstHashTable.ContainsKey($_.destinationColumn4) -ne 'No SourceValueFound') instead of ($FirstHashTable[$_.destinationColumn4] -ne 'No SourceValueFound')

Then I had to add this to the other 3 ElseIf statements to ensure all non 'No SourceValueFound' values were checked first, before assuring 'No SourceValueFound' was correct.

$sourceCsvFile = 'C:\Temp\test1.csv'
$destinationCsvFile = 'C:\Temp\test2.csv'

Write-Host "Creating hash table with columns `"sourceColumn1`" and `"sourceColumn8`" From $sourceCsvFile"
$FirstHashTable = @{}
Import-Csv $sourceCsvFile | ForEach-Object {
    $FirstHashTable[$_.sourceColumn1] = If ($_.sourceColumn8) {$_.sourceColumn8} Else {'No SourceValueFound'}}
Write-Host "Complete."

Write-Host "Creating hash table with columns `"sourceColumn6`" and `"sourceColumn8`" From $sourceCsvFile"
$SecondHashTable = @{}
Import-Csv $sourceCsvFile | ForEach-Object {
    $SecondHashTable[$_.sourceColumn6] = If ($_.sourceColumn8) {$_.sourceColumn8} Else {'No SourceValueFound'}}
Write-Host "Complete."


Write-Host "Appending new destinationColumn7 column from hash table"
(Import-Csv $destinationCsvFile) |
    Select-Object -Property *, @{n='destinationColumn7';e={
If (($FirstHashTable.ContainsKey($_.destinationColumn4)) -and ($FirstHashTable[$_.destinationColumn4] -ne 'No SourceValueFound')) {
        $FirstHashTable[$_.destinationColumn4]
    } ElseIf (($FirstHashTable.ContainsKey($_.destinationColumn5)) -and ($FirstHashTable[$_.destinationColumn5] -ne 'No SourceValueFound')){
        $FirstHashTable[$_.destinationColumn5]
    } ElseIf (($FirstHashTable.ContainsKey($_.destinationColumn2)) -and ($FirstHashTable[$_.destinationColumn2] -ne 'No SourceValueFound')){
        $FirstHashTable[$_.destinationColumn2]
    } ElseIf (($SecondHashTable.ContainsKey($_.destinationColumn4)) -and ($SecondHashTable[$_.destinationColumn4] -ne 'No SourceValueFound')){
        $SecondHashTable[$_.destinationColumn4]
    } ElseIf (($SecondHashTable.ContainsKey($_.destinationColumn5)) -and ($SecondHashTable[$_.destinationColumn5] -ne 'No SourceValueFound')){
        $SecondHashTable[$_.destinationColumn5]
    } ElseIf (($SecondHashTable.ContainsKey($_.destinationColumn2)) -and ($SecondHashTable[$_.destinationColumn2] -ne 'No SourceValueFound')){
        $SecondHashTable[$_.destinationColumn2]
    } ElseIf ($FirstHashTable.ContainsKey($_.destinationColumn4)) {
        $FirstHashTable[$_.destinationColumn4]
    } ElseIf ($FirstHashTable.ContainsKey($_.destinationColumn5)){
        $FirstHashTable[$_.destinationColumn5]
    } ElseIf ($FirstHashTable.ContainsKey($_.destinationColumn2)){
        $FirstHashTable[$_.destinationColumn2]
    } Else {
        'No MatchFound'
    }}} | Export-Csv "$destinationCsvFile-Temp.csv" -NoType
Write-Host "Complete."

Works now...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.