CODEWITHSUNDEEP
powershellpowershell-3.0powershell-4.0
Rob Shin
Rob Shin

Reputation: 1

Export results of (2) cmdlets to separate columns in the same CSV

I'm new to PS, so your patience is appreciated.

I'm trying to grab data from (2) separate CSV files and then dump them into a new CSV with (2) columns. Doing this for (1) is easy, but I don't know how to do it for more.

This works perfectly:

Import-CSV C:\File1.csv | Select "Employee" | Export-CSV -Path D:\Result.csv -NoTypeInformation

If I add another Import-CSV, then it simply overwrites the existing data:

Import-CSV C:\File2.csv | Select "Department" | Export-CSV -Path D:\Result.csv -NoTypeInformation

How can I get columns A and B populated with the info result from these two commands? Thanks for your help.

Upvotes: 0

Views: 885

Answers (2)

Saggie Haim
Saggie Haim

Reputation: 344

I would have choose this option:

$1 = Import-Csv -Path "C:\Users\user\Desktop\1.csv" | Select "Employee" 
$2 = Import-Csv -Path "C:\Users\user\Desktop\2.csv" | Select "Department" 
$marged = [pscustomobject]@()
$object = [pscustomobject]
for ($i=0 ; $i -lt $1.Count ; $i++){
    $object = [pscustomobject]@{
    Employees = $1[$i].Employee
    Department = $2[$i].Department}
    $marged += $object
}
$marged | ForEach-Object{ [pscustomobject]$_  } | Export-Csv -Path "C:\Users\user\Desktop\3.csv" -NoTypeInformation -Force

Upvotes: 1

Matt G
Matt G

Reputation: 632

I'll explain how I would do this, but I do it this way because I'm more comfortable working with objects than with hastables. Someone else may offer an answer using hashtables which would probably work better.

First, I would define an array to hold your data, which can later be exported to CSV:

$report = @()

Then, I would import your CSV to an object that can be iterated through:

$firstSet = Import-CSV .\File1.csv

Then I would iterate through this, importing each row into an object that has the two properties I want. In your case these are Employee and Department (potentially more which you can add easily).

foreach($row in $firstSet)
{
   $employeeName = $row.Employee
   $employee = [PSCustomObject]@{
      Employee = $employee
      Department = ""
   }

   $report += $employee
}

And, as you can see in the example above, add this object to your report.

Then, import the second CSV file into a second object to iterate through (for good form I would actually do this at the begining of the script, when you import your first one):

$secondSet = Import-CSV .\File2.csv

Now here is where it gets interesting. Based on just the information you have provided, I am assuming that all employees in the one file are in the same order as the departments in the other files. So for example, if I work for the "Cake Tasting Department", and my name is on row 12 of File 1, row 12 of File 2 says "Cake Tasting Department".

In this case it's fairly easy. You would just roll through both lists and update the report:

$i = 0

foreach($row in $secondSet)
{
   $dept = $row.Department
   $report[i].Department = $dept

   $i++
}

After this, your $report object will contain all of your employees in one row and departments in the other. Then you can export it to CSV:

$report | Export-CSV .\Result.csv -NoTypeInformation

This works if, as I said, your data aligns across both files. If not, then you need to get a little fancier:

foreach($row in $secondSet)
{
   $emp = $row.Employee
   $dept = $row.Department
   $report | Where {$_.Employee -eq $emp} foreach {$_.Department = $dept
}

Technically you could just do it this way anyway, but it depends on a lot of things. First of all whether you have the data to match in that column across both files (which obviously in my example you don't otherwise you wouldn't need to do this in the first place, but you could match across other fields you may have, like EmployeeID or DoB). Second, on the sovereignty of individual records (e.g., if you have multiple matching records in your first file, you will have a problem; you would expect duplicates in the second as there are more than one person in each department).

Anyway, I hope this helps. As I said there is probably a 'better' way to do this, but this is how I would do it.

Upvotes: 0

Related Questions