Replace text in CSV from hashtable

Question

I'm trying to Import-Csv "Newusers.csv" that is an export from a payroll system. I need to import this to Active Directory and need to update the physicalDeliveryOfficeName column in the CSV file for each user.

Currently the export from the payroll system users a code for the sitename. I need to replace this code with the site name. I have a hashtable that has the site code = site name. I have over 100 site locations.

$hashtable = @{
    28 = 'Example Site 1'
    29 = 'Example Site 2'
    36 = 'Example Site 3'
    37 = 'Example Site 4'
    50 = 'Example Site 5'
    51 = 'Example Site 6'
    52 = 'Example Site 7'
    53 = 'Example Site 8'
}

Mathias R. Jessen · Accepted Answer · 2017-10-15 23:28:33Z

2

I have over 100 site locations

In that case you're definitely on the right path.

Use the site code value as the key when accessing the hashtable:

Import-Csv newusers.csv |ForEach-Object {
    Set-ADUser -Identity $_.username -replace @{physicalDeliveryOffice=$hashtable[$_.sitecode]}
}

answered Oct 15, 2017 at 23:28

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

3 Comments

Lee Over a year ago

Thanks for the quick reply. If i need to just update the csv file (as this will be used for another system too) whats the command i need to use inplace of the set-aduser to just update the csv

Manu Over a year ago

Use the -Append parameter : Export-Csv -Append -Path "C:\xxxxx\File.csv"

Manu Over a year ago

Sorry I misinterpreted the question, my bad

Ansgar Wiechers · Accepted Answer · 2017-10-16 08:24:29Z

1

To replace the site codes with site names in your CSV import the CSV, modify the column, then export the data back to a CSV. There are several ways you can approach this, e.g. by replacing the entire column using Select-Object with a calculated property:

$file = 'C:\path\to\newusers.csv'
(Import-Csv $file) |
    Select-Object *,@{n='SiteName';e={$hashtable[$_.sitecode]}} -Exclude sitecode |
    Export-Csv $file -NoType

or by simply changing the values in the column in a loop:

$file = 'C:\path\to\newusers.csv'
$csv  = Import-Csv $file
foreach ($record in $csv) {
    $record.sitecode = $hashtable[$_.sitecode]
}
$csv | Export-Csv $file -NoType

answered Oct 16, 2017 at 8:24

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Collectives™ on Stack Overflow

Replace text in CSV from hashtable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related