none
How to compare 2 queries and construct an output csv RRS feed

  • Question

  • My story is as follows

    i have 2 spreadsheets with with 1 sheet having headings displayname, samaccountname,firstname,lastname.

    the second spreadsheet has address, phonenumber,samaccountname,state

    i want to do the following

    1) find the samaccount in spreadsheet 1 and match it to the record in spreadsheet 2

    2) create a spreadsheet with the information collected in both spreadsheets

    i.e. displayname, samaccountname,firstname,lastname, address, phonenumber,samaccountname,state

    now previously i use to do a for loop and inside the for loop, i'd do another one to find the matching samaccount

    however the problem i have now is that i have 1400+ records and its taking forever

    Monday, April 23, 2018 1:09 PM

Answers

  • Your code confused me a little bit. The following example might guide you to a slightly faster approach.
    $VMMasterData = @'
    displayname, samaccountname,firstname,lastname
    DisplayName01,sAMAccountName01,FirstName01,LastName01
    DisplayName02,sAMAccountName02,FirstName02,LastName02
    DisplayName05,sAMAccountName05,FirstName05,LastName05
    DisplayName06,sAMAccountName06,FirstName06,LastName06
    DisplayName09,sAMAccountName09,FirstName09,LastName09
    DisplayName10,sAMAccountName10,FirstName10,LastName10
    '@
    
    $VMGuestMasterData = @'
    address, phonenumber,samaccountname,state
    Address01,Phone01,sAMAccountName01,State01
    Address02,Phone02,sAMAccountName02,State02
    Address03,Phone03,sAMAccountName03,State03
    Address04,Phone04,sAMAccountName04,State04
    Address07,Phone07,sAMAccountName07,State07
    Address08,Phone08,sAMAccountName08,State08
    Address09,Phone09,sAMAccountName09,State09
    Address10,Phone10,sAMAccountName10,State10
    '@
    
    ConvertFrom-Csv -InputObject $VMGuestMasterData -Delimiter ',' -OutVariable VmguestMasterList
    ConvertFrom-Csv -InputObject $VMMasterData -Delimiter ',' -OutVariable VmMasterList
    
    
    $NewList = foreach ($VmguestMaster in $VmguestMasterList) {
        If($VmMasterList.sAMAccountName -contains $VmguestMaster.sAMAccountName){
            $Index = $VmMasterList.sAMAccountName.IndexOf($VmguestMaster.sAMAccountName)
            [PSCustomObject]@{
                Address = $VmguestMaster.address
                PhoneNumber = $VmguestMaster.phonenumber
                sAMAccountName = $VmguestMaster.samaccountname
                State  = $VmguestMaster.state
                DisplayName = $VmMasterList.DisplayName[$Index]
                FirstName = $VmMasterList.FirstName[$Index]
                LastName = $VmMasterList.LastName[$Index]
            }
        }
    }
    
    $NewList | Format-Table -AutoSize
    
    If your csv files (sheets) look like you stated before you should be able to test this approach with very little adjustments.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    • Marked as answer by dannguyen5886 Thursday, May 3, 2018 8:38 AM
    Tuesday, April 24, 2018 8:42 AM

All replies

  • You might show what you have so far. We could be able to speed up your code a bit.  ;-)

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Monday, April 23, 2018 1:28 PM

  • $VMSelectObj = @('Name','PowerState','Notes','Guest','CoresPerSocket','MemoryMB','MemoryGB','VMHostId','VMHost','VApp','FolderId','Folder','ResourcePoolId','ResourcePool','HARestartPriority','HAIsolationResponse','DrsAutomationLevel','VMSwapfilePolicy','VMResourceConfiguration','Version','PersistentId','GuestId','UsedSpaceGB','ProvisionedSpaceGB','DatastoreIdList','ExtensionData','CustomFields','Id','Uid','Client')
    $VMGSelectObj = @('OSFullName','IPAddress','State','Disks','HostName','Nics','ScreenDimensions','VmId','VM','VmUid','VmName','Uid','GuestId','ToolsVersion','ExtensionData','Client','GuestFamily')


    $VmguestMasterList = Import-Csv c:\temp\vmguestmasterlist.csv
    $VmMasterList = Import-Csv c:\temp\vmMasterlist.csv






    foreach($VmgetVM in $VmguestMasterList)
        {
        $VmID = $VmgetVM.VmId

        foreach($VMguestVM in $VmMasterList)
            {
            $iD = $vmguestVM.Id

            if($VMID -match $ID)
                {
                Write-Host "Match" -ForegroundColor Green
                }
                else
                {
                Write-Host "Not Match" -ForegroundColor Red
                Write-Host "$VmID and $ID don't match" -ForegroundColor Red
                }

            }
        }

    #VMID and ID are the same values and are the primary key

                
    Tuesday, April 24, 2018 1:09 AM
  • Your code confused me a little bit. The following example might guide you to a slightly faster approach.
    $VMMasterData = @'
    displayname, samaccountname,firstname,lastname
    DisplayName01,sAMAccountName01,FirstName01,LastName01
    DisplayName02,sAMAccountName02,FirstName02,LastName02
    DisplayName05,sAMAccountName05,FirstName05,LastName05
    DisplayName06,sAMAccountName06,FirstName06,LastName06
    DisplayName09,sAMAccountName09,FirstName09,LastName09
    DisplayName10,sAMAccountName10,FirstName10,LastName10
    '@
    
    $VMGuestMasterData = @'
    address, phonenumber,samaccountname,state
    Address01,Phone01,sAMAccountName01,State01
    Address02,Phone02,sAMAccountName02,State02
    Address03,Phone03,sAMAccountName03,State03
    Address04,Phone04,sAMAccountName04,State04
    Address07,Phone07,sAMAccountName07,State07
    Address08,Phone08,sAMAccountName08,State08
    Address09,Phone09,sAMAccountName09,State09
    Address10,Phone10,sAMAccountName10,State10
    '@
    
    ConvertFrom-Csv -InputObject $VMGuestMasterData -Delimiter ',' -OutVariable VmguestMasterList
    ConvertFrom-Csv -InputObject $VMMasterData -Delimiter ',' -OutVariable VmMasterList
    
    
    $NewList = foreach ($VmguestMaster in $VmguestMasterList) {
        If($VmMasterList.sAMAccountName -contains $VmguestMaster.sAMAccountName){
            $Index = $VmMasterList.sAMAccountName.IndexOf($VmguestMaster.sAMAccountName)
            [PSCustomObject]@{
                Address = $VmguestMaster.address
                PhoneNumber = $VmguestMaster.phonenumber
                sAMAccountName = $VmguestMaster.samaccountname
                State  = $VmguestMaster.state
                DisplayName = $VmMasterList.DisplayName[$Index]
                FirstName = $VmMasterList.FirstName[$Index]
                LastName = $VmMasterList.LastName[$Index]
            }
        }
    }
    
    $NewList | Format-Table -AutoSize
    
    If your csv files (sheets) look like you stated before you should be able to test this approach with very little adjustments.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    • Marked as answer by dannguyen5886 Thursday, May 3, 2018 8:38 AM
    Tuesday, April 24, 2018 8:42 AM
  • it is indeed quicker, thank you for this. 
    Thursday, May 3, 2018 8:38 AM