CODEWITHSUNDEEP
powershellcsv
BrandonCrimmins
BrandonCrimmins

Reputation: 11

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

***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

Upvotes: 1

Views: 461

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174900

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

Upvotes: 1

Related Questions