locked
Query array tables RRS feed

  • Question

  • I have two CSV files and I need to use one column from one csv file as a key into other csv file and get value from another column based on it.

    e.g.  CSV 1

    name,emp-id,title,manager

    john,123,cpa,peter

    peter,345,manager,russell

    CSV 2

    dept,emp-id, manager-id

    it,123,345

    So what i need is use manager-id from CSV 2 file and use it as a key to look into CSV1 file against emp-id column and then get corresponding name

    so from example, use 345 from CSV 2 as key to look up name peter from CSV 1

    Wednesday, March 4, 2015 6:55 AM

Answers

  • Hi,

    you can import csv files and compare contents like this:

    $Employees = Import-Csv "C:\temp\Csv1.csv"
    $Departments = Import-Csv "C:\temp\Csv2.csv"
    
    foreach ($Department in $Departments)
    {
    	$Manager = $Employees | Where-Object { $_.'emp-id' -eq $Department.'manager-id' } | Select-Object -ExpandProperty "name"
    	"Department " + $Department.dept + " is managed by " + $Manager
    }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by KaPes1 Thursday, March 5, 2015 7:32 PM
    Wednesday, March 4, 2015 8:21 AM

All replies

  • Hi,

    you can import csv files and compare contents like this:

    $Employees = Import-Csv "C:\temp\Csv1.csv"
    $Departments = Import-Csv "C:\temp\Csv2.csv"
    
    foreach ($Department in $Departments)
    {
    	$Manager = $Employees | Where-Object { $_.'emp-id' -eq $Department.'manager-id' } | Select-Object -ExpandProperty "name"
    	"Department " + $Department.dept + " is managed by " + $Manager
    }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by KaPes1 Thursday, March 5, 2015 7:32 PM
    Wednesday, March 4, 2015 8:21 AM
  • Thanks, that's what i ended up doing, I wished there was simple "filtering" capability in array like $Departments[$user].Name, just like Hash Tables, but with more than one value pair. I guess i could embed objects in hash tables and then achieve that.

    Thursday, March 5, 2015 7:35 PM
  • Hi KaPes,

    actually, implementing something similar to that wouldn't be that hard:

    $Departments = Import-Csv "C:\temp\Csv2.csv"
    
    $Employees = @{ }
    Import-Csv "C:\temp\Csv1.csv" | %{ $Employees[$_.'emp-id'] = $_ }
    
    foreach ($Department in $Departments)
    {
    	"Department " + $Department.dept + " is managed by " + $Employees[$Department.'manager-id'].Name
    }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Thursday, March 5, 2015 8:44 PM