Loop through csv compare content with an array and then add content to csv

Question

I don't know how to append a string to CSV. What am I doing: I have two csv files. One with a list of host-names and id's and another one with a list of host-names and some numbers. Example file 1: Hostname | ID
IWBW140004 | 3673234
IWBW130023 | 2335934
IWBW120065 | 1350213

Now I read the content of file 1 in a two dimensional array:

$pcMatrix = @(,@())
Import-Csv $outputFile |ForEach-Object {
    foreach($property in $_.PSObject.Properties){
        $pcMatrix += ,($property.Value.Split(";")[1],$property.Value.Split(";")[2])
    }
}

Then I read the content of file 2 and compare it with my array:

Import-Csv $Group".csv" | ForEach-Object {
    foreach($property in $_.PSObject.Properties){
        for($i = 0; $i -lt $pcMatrix.Length; $i++){
            if($pcMatrix[$i][0] -eq $property.Value.Split('"')[1]){
                #Add-Content here
            }
        }
    }
}

What do I need to do, to append $pcMatrix[$i][1] to the active column in file 2 in the row ID?

Thanks for your suggestions.

Yanick

Mathias R. Jessen · Accepted Answer · 2021-04-15 14:52:46Z

4

It seems like you are over-complicating this task.

If I understand you correctly, you want to populate the ID column in file two, with the ID that corresponds to the correct hostname from file 1. The easiest way to do that, is to fill all the values from the first file into a HashTable and use that to lookup the ID for each row in the second file:

# Read the first file and populate the HashTable:
$File1 = Import-Csv .\file1.txt -Delimiter "|"
$LookupTable = @{}
$File1 |ForEach-Object {
    $LookupTable[$_.Hostname] = $_.ID
}

# Now read the second file and update the ID values:
$File2 = Import-Csv .\file2.txt -Delimiter "|"
$File2 |ForEach-Object {
    $_.ID = $LookupTable[$_.Hostname]
}

# Then write the updated rows back to a new CSV file:
$File2 | Export-CSV -Path .\file3.txt -NoTypeInformation -Delimiter "|"

edited Apr 15, 2021 at 14:52

answered Nov 13, 2014 at 9:27

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

3 Comments

Yanick Schraner Over a year ago

Thanks for your suggestion. Your solution is way simpler. I've tested it but there seems to be something wrong with the Export-CSV part. The generated Export contains just information's about the $File2 object (e.g. Count Value, LongLength Value, IsReadOnly Value...) What do I need to change? Export-Csv -Path .\test.csv -InputObject $File2 -NoTypeInformation -Delimiter "|"

Mathias R. Jessen Over a year ago

Updated the example, if you pipe $File2 to the Export-Csv cmdlet, the rows/objects will be enumerated and you should see a proper csv file output

Yanick Schraner Over a year ago

yes, the pipe solution works great! Thank you very much!

Collectives™ on Stack Overflow

Loop through csv compare content with an array and then add content to csv

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related