CODEWITHSUNDEEP
powershellcsv
user2637202
user2637202

Reputation: 133

Combine columns from 2 CSV files

This seems very basic yet I can't find or figure out anywhere I have 2 CSV files I would like to create a new one that will have matched columns.

Huge.csv

"Share","Group","Username","Name","LogonScript"
"\\SHARE\TEST","Group Test","administrator","Administrator name","(no-script)"
"\\SHARE\TEST","Group Test","user1","user name1","logon.bat"
"\\SHARE\TEST","Group Test","user2","user name2","logon.bat"

Little.csv

"Username","Computer","NetworkDrives"
administrator,PC100,M:\\share\it#N:\\share\test
user2,PC102,M:\\share\it#N:\\share\test

Desired output:

output.csv

"Share","Group","Username","Name","LogonScript","Computer","NetworkDrives"
"\\SHARE\TEST","Group Test","administrator","Administrator name","(no-script)",PC100,M:\\share\it#N:\\share\test
"\\SHARE\TEST","Group Test","user1","user name1","logon.bat",,
"\\SHARE\TEST","Group Test","user2","user name2","logon.bat",PC102,M:\\share\it#N:\\share\test

Here's the code I'm working with:

$HugeFile = Import-Csv -Path .\Huge.csv
$LittleFile = Import-Csv -Path .\Little.csv

ForEach ($entryh in $HugeFile) {
    $o | add-member NoteProperty -Name "Share" -Value ($entryh.Share)
    $o | add-member NoteProperty  -Name "Group" -Value ($entryh.Group)
    $o | add-member NoteProperty  -Name "Username" -Value ($entryh.Username)
    $o | add-member NoteProperty  -Name "Name" -Value ($entryh.Name)
    $o | add-member NoteProperty -Name "LogonScript" -Value  
    ($entryhu.LogonScript)

    ForEach ($entryl in $LittleFile) {
        If ($($entryh.Username) -eq  $($entryl.Username)) {
            $o | add-member NoteProperty -Name "Computer" -Value ""
            ($entryl.Computer)

            $o | add-member NoteProperty -Name "NetworkDrives" -Value ""
            ($entryl.NetworkDrives)
        } Else {
            $o | add-member NoteProperty -Name "Computer" -Value "," -Force
            $o | add-member NoteProperty -Name "NetworkDrives" -Value "," -Force
        }

        $o | Export-Csv -NoTypeInformation .\output.csv
    }
}
}

My code is not working. :/

My second problem is that I am thinking if there a better option because for each "Username" in Huge.csv I have to compare with "Username" in Little.csv.

Maybe creating a hashtable could be more optimal. Concat Computer and Network a create a value? Like:

key                 Computer+NetworkDrive
-----------         ---------------------
administrator       PC100,M:\\share\it#N:\\share\test
user2               PC102,M:\\share\it#N:\\share\test

Thanks a lot!

Edit Thanks @Ansgar-Wiechers

Upvotes: 0

Views: 2556

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

Yes, a hashtable is probably the best way to go about this. I'd do it like this:

$additionalData = @{}
Import-Csv .\Little.csv | % {
  $additionalData[$_.Username] = $_.Computer, $_.NetworkDrives
}

Import-Csv .\Huge.csv `
  | select Share, Group, Username, Name, LogonScript, @{n='Computer';e={}},
           @{n='NetworkDrives';e={}} `
  | % {
    if ( $additionalData.ContainsKey($_.Username) ) {
      $_.Computer      = $additionalData[$_.Username][0]
      $_.NetworkDrives = $additionalData[$_.Username][1]
    }
    $_
  } | Export-Csv -NoTypeInformation .\output.csv

Or, following @mjolinor's suggestion, using separate hashtables for computers and network drives:

$computers = @{}
$netDrives = @{}
Import-Csv .\Little.csv | % {
  $computers[$_.Username] = $_.Computer
  $netDrives[$_.Username] = $_.NetworkDrives
}

Import-Csv .\Huge.csv `
  | select Share, Group, Username, Name, LogonScript, @{n='Computer';e={}},
           @{n='NetworkDrives';e={}} `
  | % {
    if ( $computers.ContainsKey($_.Username) ) {
      $_.Computer = $computers[$_.Username]
    }
    if ( $netDrives.ContainsKey($_.Username) ) {
      $_.NetworkDrives = $netDrives[$_.Username]
    }
    $_
  } | Export-Csv -NoTypeInformation .\output.csv

Upvotes: 1

Related Questions