How to merge two csv files with PowerShell

Question

I have two .CSV files that contain information about the employees from where I work. The first file (ActiveEmploye.csv) has approximately 70 different fields with 2500 entries. The other one (EmployeEmail.csv) has four fields (FirstName, LastName, Email, FullName) and 700 entries. Both files have the FullName field in common and it is the only thing I can use to compare each file. What I need to do is to add the Email address (from EmployeEmail.csv) to the corresponding employees in ActiveEmploye.csv. And, for those who don't have email address, the field can be left blank.

I tried to use a Join-Object function I found on internet few days ago but the first csv files contain way too much fields for the function can handle.

Any suggestions is highly appreciated!

BartekB · Accepted Answer · 2013-01-28 16:36:43Z

5

There are probably several ways to skin this cat. I would try this one:

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

answered Jan 28, 2013 at 16:36

BartekB

8,71034 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DarKZoNe Over a year ago

For a strange reason, the Joined.csv has the field "Email" added like your code is supose to do, however it is blank for every employees.

BartekB Over a year ago

Hm, first thing that pops in: are you sure that field in csv is "fullname" not something similar - e.g. with trailing space...? I tried it with some test-code and it worked for me, but trailing spaces in csv fields bit me in the.. back far to many times... thus my first guess. Same for email field.

DarKZoNe Over a year ago

You are right, trailing space are to blame! Now I have to find a Regex that remove space only at the end of each fields, not every spaces in the .CSV files. Thx for your help

BartekB Over a year ago

You can also force -Header on Import-Csv and just select -skip 1 to ignore actual header: Import-Csv -Header FullName, email EmployeeEmail.csv | select -skip 1 | foreach...

Dan Over a year ago

If you pass the object to the array you can join more items than just the email. For example, using $Employees.Add($_.FullName,$_) you can then call Add-Member with -Value $Employess."$($_.FullName)"."email".

Dan · Accepted Answer · 2013-07-23 17:19:26Z

0

Here is an alternate method to @BartekB which I used to join 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 one to query the array index of FullName on $Employees and then get the value of a named member on that element.

answered Jul 23, 2013 at 17:19

Dan

7326 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to merge two csv files with PowerShell

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related