Monday, September 11, 2017

Powershell CSV Join

I am currently working on a fairly large project migrating mailboxes from on-premise Exchange 2010 upto Microsoft's O365. One of the issues that we're having is that mailbox permissions don't always migrate correctly. So I've developed process to capture the mailbox permissions in one form or another in a CSV prior to migration, then I reapply them after reapplying them.

One of the issues experienced is that on-premise account information doesn't always match up with what's in O365. For example, I have the local Exchange / AD account information, but not necessarily what the account looks like in o365. So I wrote this script to join two CSV files based on similar properties. This way I can join a CSV containing mailbox information (i.e. SamAccountName) to the Get-MailboxPermission "USER" field.

.\CSVJoin.PS1 -CSV1Data "c:\bin\All-Mailboxes.CSV" -CSV1Property Samaccountname -CSV2Data "c:\bin\All-MailboxPermissions.csv" -CSV2Property USER -JoinedCSV "c:\BIN\Joined-CSV.csv"

The  script will export a CSV containing all fields from CSV1 plus all the fields from CSV2 that don't overlap in name.

    [parameter(Mandatory=$true,HelpMessage="Path and filename for source CSV 1")][ValidateScript({Test-Path $_ })][string]$csv1Name,
    [parameter(Mandatory=$true,HelpMessage="Propery from CSV1 to join files on.")][string]$csv1Property,
    [parameter(Mandatory=$true,HelpMessage="Path and filename for source CSV 2")][ValidateScript({Test-Path $_ })][string]$Csv2Name,
    [parameter(Mandatory=$true,HelpMessage="Propery from CSV2 to join files on.")][string]$csv2Property,
    [parameter(Mandatory=$true,HelpMessage="Path and Name for combined CSV file")][string]$JoinedCSV

$csv1Data = Import-CSV $csv1Name | ?{$_.$CSV1Property -ne $null}
$csv2Data = Import-csv $Csv2Name | ?{$_.$CSV2Property -ne $null}

#Capture all the column values for each CSV file and compare them.
$csv1Members = $csv1Data[0] | Get-Member | ?{$_.membertype -eq "NoteProperty"}
$csv2Members = $csv2Data[0] | Get-Member | ?{$_.membertype -eq "NoteProperty"}
$AddCSV2members = Compare-Object $csv1Members $csv2Members | ?{$_.sideindicator -eq "=>"} | %{$_.inputobject}

#Populate HashTable with First CSV based on JOIN fields
$csv1HashTable = @{}
$csv1Data | %{$csv1HashTable[$_.$csv1Property.trim()] = $_}

#Loop through Second CSV and join fields to first CSV. 
$newCSV = @()
ForEach ($c in $csv2Data) {
    $Row = $csv1HashTable[$c.$csv2property.trim()]
    if ($row ) {
        ForEach ($m in $AddCSV2members) {
            $Row | add-member -Membertype NoteProperty -Name $ -value $C.$($ -force
        $newCSV += $Row

if ($newCSV) {
    $newCSV | Export-csv $JoinedCSV -notypeinformation

No comments:

Post a Comment