Combine data from 2 CSV files into 1 new CSV using Powershell

Question

***EDITED FOR CLARITY:

My ADP CSV contains these headers exactly as listed: Action Date, Job Information Effective Date,Location Name, Legal Name, Employee Status Code, First Name, Last Name, Employee ID, Job Title, Location State, Location City, Employee Class, Hire Date, Job Termination Date, FLSA Over-Time Indicator, FLSA Status.

My Office 365 CSV contains these headers exactly as listed: DISPLAY NAME, EMAIL ADDRESS

I need a CSV that lists: Display Name, Email Address, City, State, Location Name, Job Title.

I need a Powershell Script that combines the two files together to leave me with only the Office 365 Users and the data that their profile is missing such as City, State, Location Name, Job Title.

I understand creating the Hash Table for the Office 365 List, but every time I create one it returns "The array index evaluated to Null" which in my understanding means I am not using the correct header names in the hash table.

****Edited AGAIN! I am attaching my code. It does everything I need it to except that the Email column it creates is blank. I no longer get hast table errors.

    $ItemsToKeep = 'First Name','Last Name','Employee Status Code','Location Name','Job Title','Location State'
    $ItemsToKeep += @{Name = "Display Name"; Expression = {$_.'First Name' + ' ' + $_.'Last Name'}}
    $ItemsToKeep += @{Name = "Email"; Expression = { $EmailLookupTable[$_.'Display Name'] }}

    $EmailLookupTable = @{}
    Import-Csv C:\O365LIST.csv |ForEach-Object {
    $EmailLookupTable[$_.'Display Name'] = $_.'Email Address'
    }



    Import-Csv C:\ADPLIST.csv |Where-Object {$_.'Employee Status Code' -eq 'Active'} | Select $ItemsToKeep | export-csv C:\Testing.csv -notypeinformation

Mathias R. Jessen · Accepted Answer · 2016-10-12 12:55:56Z

1

First, load the second CSV into a hashtable, this way you can easily map the display name from the first CSV to an email address:

#Import the second CSV and use a hashtable to store the email address based on the display name
$EmailLookupTable = @{}
Import-Csv C:\employee_emails.csv |ForEach-Object {
    $EmailLookupTable[$_.'Display Name'] = $_.'Email Address'
}

With your existing script, instead of exporting to a new file in between each operation, chain them all together in a single pipeline - most of the code can be automated away with a tiny bit of metaprogramming!

# Automate away the task of replacing spaces with underscores
$ColumnsToKeep = "Location Name","Employee Status Code","Job Title","Location State","Location City","First Name","Last Name"
$PropertiesToSelect = $ColumnsToKeep |ForEach-Object {
    @{Name = $_ -replace ' ','_'; Expression = [scriptblock]::Create('$_."{0}"' -f $_)}
}

# Add the display name property
$PropertiesToAdd = @(@{Name = "Display_Name"; Expression = {$_.First_Name + ' ' + $_.Last_Name}})

# Add the Email address from the lookup table
$PropertiesToAdd += @{Name = "Email"; Expression = { $EmailLookupTable[$_.First_Name + ' ' + $_.Last_Name] }}

# 1. Import CSV
# 2. Filter on status
# 3. Select the properties to keep/rename
# 4. Select the properties from the previous step + the new ones
# 5. Export CSV
Import-Csv C:\ADPLIST.csv |Where-Object {$_.'Employee Status Code' -eq 'Active'} |Select-Object $PropertiesToSelect |Select-Object *,$PropertiesToAdd |Export-Csv C:\final.csv

edited Oct 12, 2016 at 12:55

answered Oct 10, 2016 at 19:05

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

8 Comments

BrandonCrimmins Over a year ago

You are awesome! This helps my company tremendously. How ever, it is returning the error "The array index evaluated to Null" while trying to create the hash table. Also, when I run my list from ADP, and the other list from Office 365, the headers don't have underscores in them. I wasn't getting the underscores until I ran my code so we could delete the replace step from this as long as the code uses the two original lists and not my modified ADP csv. The headers are:

BrandonCrimmins Over a year ago

Office 365 List- DISPLAY NAME, EMAIL ADDRESS (However for some reason when I export it, it puts about 30 spaces and the end of EMAIL ADDRESS but I can delete those by hand and save the file)

Mathias R. Jessen Over a year ago

@BrandonCrimmins forgot quotes around the property name when building the hashtable, updated the answer

BrandonCrimmins Over a year ago

ADP FILE: Action Date, Job Information Effective Date,Location Name, Legal Name, Employee Status Code, First Name, Last Name, Employee ID, Job Title, Location State, Location City, Employee Class, Hire Date, Job Termination Date, FLSA Over-Time Indicator, FLSA Status. It does not contain Display Name, I am creating that for the Match On criteria for both files using First Name and Last Name. I need get the Active users out of the ADP list with their State, Location Name, Location City, and Job Title. I need to add their email address from Office 365.

BrandonCrimmins Over a year ago

My final CSV should only contain the users from the Office 365 list as their the only ones with a current email address. Everyone else can be excluded as they don't need to be in our AD. I cant tell you how much I appreciate this! You're helping a small business drastically improve a part of its routine. Thank you!

|

Collectives™ on Stack Overflow

Combine data from 2 CSV files into 1 new CSV using Powershell

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related