none
PoswerShell - Compare 2 x CSV files, only if data matches in column 1 add all data from file 2 to file 1 or output collated results RRS feed

  • Question

  • OK, 1st the background...
    I have been provided a list of users of a particular group.
    We needed to know what computers (or IP's) the users are connected to...

    So, using Shared Folders, I have exported a list of all users connected to H drives (everyone on the domain) and I need to match their computernames (or IP) to the specific list of users I have...

    That gives me 2 x CSV files. 1 x master list of users, I list of all users PC or IP names.
    I need to be able to match the usernames on the master list (File1) with the usernames on File2 and append the computernames and connected times to File1 omitting the usernames that are not required...

    This is to determine what PC's the user is logged into and what site their computers are at.
    I also need to capture if they are logged into more than one computer (info is contained in File2)

    File1 Example:

    User FirstName LastName ADSite HDriveLocation HDriveLocationTS Office2010FAT
    XY123456 BOB BUILDER LONDON  \\SERVER\%USERNAME% \\SERVER\USERNAME TRUE
    ABC09876 JIM INYCRICKET  CHICAGO  \\SERVER\USERNAME
    \\SERVER\USERNAME TRUE
    ADD11223  SALLY METLARRY TOKYO \\SERVER\USERNAME \\SERVER\USERNAME FALSE

    File2 Example:

    User Computer  Connected Idle
    XY123456  192.168.1.216  3 days 06:13:18 0:00:03
    XY123456 192.168.1.254  1 day 01:21:30 23:08:39
    WXY46810  PCNAMEXYZ   6:44:19 0:00:05



    I have found a PS script which "might" do what I need it to, but I haven't been able to "tweak" it right - it's omitting the details from File2 - http://powergui.org/thread.jspa?threadID=19522
    Also I can't figure out how to collect multiple computernames (that users are logged into) from File 2 and add this to File1

    Working with Powershell, but happy to consider any other options (EG: Excel, Macros, VBS, etc)

    Here's what I have got so far: (PS)

    $File1 = Import-Csv "C:\Users.csv"
    $File2 = Import-Csv "C:\Shares.csv"

    $Output = @()
    ForEach ($Item in $File1)
    { $Result = $File2 | Where { $Item.User -eq $_.User }
    $Output += New-Object PSObject -Property @{
    USER = $Item.User
    FirstName = $Item.FirstName
    LastName = $Item.LastName
    ADSite = $Item.ADSite
    Computer = $_.Computer
    Connected = $_.Connected
    Idle = $_.Idle
    Office2010FAT = $Item.Office2010FAT
    HDriveLocation = $Item.Connected
    HDriveLocationTS = $Item.Idle
    }
    }
    $Output | Select User,FirstName,LastName,ADSite,Computer,Connected,Idle,Office2010FAT,HDriveLocation,HDriveLocationTS | Export-Csv C:\Users\a1033965\Desktop\result.csv -NoTypeInformation


    However, the output I'm getting is missing the results from File2 and I NEED multiple computers listed if a user is logged into them:
    Current Output:

    USER FirstName LastName ADSite Computer Connected  Idle  Office2010FAT  HDriveLocation  HDriveLocationTS
    XY123456 BOB BUILDER LONDON TRUE
    ABC09876 JIM INYCRICKET CHICAGO TRUE
    ADD11223  SALLY METLARRY TOKYO TRUE


    (TRUE is actually in the right column, under Office2010FAT as it should be - just doesn't look like it as I post this)

    Any help with this would be much appreciated. Cheers

    Friday, April 4, 2014 12:48 AM

Answers

  • Thanks for your help guys - I ended up getting an addon for excel and merging the results in Office... Managed to do what I needed.
    Thursday, April 10, 2014 10:07 PM

All replies

  • The PowerShell team made a blog post about this topic a few years back. Check out their Join-Object function: http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx
    Friday, April 4, 2014 12:53 AM
  • Thanks David, I'll read over that now and post back shortly after - looks like it might have

    what I need there...

    Friday, April 4, 2014 1:15 AM
  • Looks good - however, I'm not getting any output... (left script running all weekend - script still appears to be running, smaller runs finish ok, but still no output :( )
    Still having trouble finding anything that'll work...
    Sunday, April 6, 2014 8:18 PM
  • How many rows are in each file?


    ¯\_(ツ)_/¯

    Sunday, April 6, 2014 9:57 PM
  • 750 in one file

    7500 in the second

    Sunday, April 6, 2014 11:23 PM
  • What is the product of 750 and 7500?

    It is 10 times 750 squared.  That is a very big number.

    This will take a long time.  It would be faster in a database.

    Which is which?  7500 people and 750 computers? 

    What  you are being asked to do and the data you are being given seem suspicious.

    One file or the other must have a large number of duplicate records.


    ¯\_(ツ)_/¯

    Sunday, April 6, 2014 11:44 PM
  • This technique will output all records that match on a field.

    # method 1

    If $csv1 is 750 and $csv2 is 7500 then this will be very fast.  Ifnothing is output thenteh fields are no matchable for any of a number of reasons.

    # method 2

    $users1=$csv1 | select user, computer -unique
    $users2=$csv2 | select -unique

    Now back-match  the files. there should be no duplicates. Use linked method or method #1/


    ¯\_(ツ)_/¯

    Monday, April 7, 2014 12:13 AM
  • Please verify that your files look exactly like this:

    #file1 format
    User,FirstName,LastName,ADSite,HDriveLocation,HDriveLocationTS,Office2010FAT
    XY123456,BOB,BUILDER,LONDON,\\SERVER\%USERNAME%,\\SERVER\USERNAME,TRUE
    ABC09876,JIM,INYCRICKET,CHICAGO,\\SERVER\USERNAME,\\SERVER\USERNAME,TRUE
    ADD11223,SALLY,METLARRY,TOKYO,\\SERVER\USERNAME,\\SERVER\USERNAME,FALSE

    #file2 format
    User,Computer,Connected,Idle
    XY123456,192.168.1.216,3 days 06:13:18,0:00:03
    XY123456,192.168.1.254,1 day 01:21:30,23:08:39
    WXY46810,PCNAMEXYZ,6:44:19,0:00:05


    ¯\_(ツ)_/¯



    • Edited by jrv Monday, April 7, 2014 1:49 AM
    Monday, April 7, 2014 12:19 AM
  • There was a similar question over on PowerShell.org a couple of months ago: http://powershell.org/wp/forums/topic/merging-very-large-collections/ .  In that thread, I posted a rewrite of Join-Object which performed at about 2x the speed of the original, in my tests.  Even better would be to implement it in C#, but the best solution would be to stick all that data into a SQL database instead.
    Monday, April 7, 2014 2:02 AM
  • There was a similar question over on PowerShell.org a couple of months ago: http://powershell.org/wp/forums/topic/merging-very-large-collections/ .  In that thread, I posted a rewrite of Join-Object which performed at about 2x the speed of the original, in my tests.  Even better would be to implement it in C#, but the best solution would be to stick all that data into a SQL database instead.

    I agree with David although, if it is a simple cross match it can be easily done in PS.  I just ran a dummied up 1.2 million record cross-join and it ran in less than a minute. I suspect your CSV I snot really a CSV.


    ¯\_(ツ)_/¯

    Monday, April 7, 2014 3:10 AM
  • Thanks for your help guys - I ended up getting an addon for excel and merging the results in Office... Managed to do what I needed.
    Thursday, April 10, 2014 10:07 PM