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!

+3


source to share


2 answers


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

      

+4


source


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.

0


source







All Articles