How to combine two csv files using PowerShell
I have two .CSV files that contain information about the employees I work with. The first file (ActiveEmploye.csv) has approximately 70 different fields with 2500 elements. Another (EmployeEmail.csv) has four fields (FirstName, LastName, Email, FullName) and 700 records. Both files have a common FullName field and this is the only one I can use to compare each file. I need to add an email address (from EmployeEmail.csv) to the relevant employees in ActiveEmploye.csv. And for those without an email address, the field can be blank.
I tried using the Join-Object function that I found on the internet a few days ago, but the first csv files contain too many fields for this function to handle.
Any suggestions are greatly appreciated!
source to share
There are probably several ways to trick this cat. I would try the following:
Import-Csv EmployeeEmail.csv | ForEach-Object -Begin {
$Employees = @{}
} -Process {
$Employees.Add($_.FullName,$_.email)
}
Import-Csv ActiveEmployees.csv | ForEach-Object {
$_ | Add-Member -MemberType NoteProperty -Name email -Value $Employees."$($_.FullName)" -PassThru
} | Export-Csv -NoTypeInformation Joined.csv
source to share
Here is @BartekB's alternative method that I used to combine multiple fields to the left and had better results for processing time.
Import-Csv EmployeeEmail.csv | ForEach-Object -Begin {
$Employees = @{}
} -Process {
$Employees.Add($_.FullName,$_)
}
Import-Csv ActiveEmployees.csv |
Select *,@{Name="email";Expression={$Employees."$($_.FullName)"."email"}} |
Export-Csv Joined.csv -NoTypeInformation
This allows you to query the index of the FullName array on $ Employees and then retrieve the value of the named element in that element.
source to share