Reputation: 13
This seems basic however can't find or figure out anywhere.
I have a .csv file that I would like to import, change the user column that has different SIDs to the user name that corresponds, then export back to the .csv. I know how to find the SIDtoUser, but I can't figure out how to do the replace in the .csv. Also some user columns are blank or have "admin" which I need to leave alone.
Current
ComputerName, TimeStamp, Message, User
hp8440, 8/30/2012, message1, admin
hp8440, 8/30/2012, message2
hp8440, 8/30/2012, message3, S-1-5-21...1105
hp8440, 8/30/2012, message4, S-1-5-21...1255
Would like it to read
ComputerName, TimeStamp, Message, User
hp8440, 8/30/2012, message1, admin
hp8440, 8/30/2012, message2
hp8440, 8/30/2012, message3, user1
hp8440, 8/30/2012, message4, user2
Upvotes: 1
Views: 2174
Reputation: 16812
As a one-liner:
Import-Csv .\data.csv |%{ $_.User = ConvertSidToUserName $_.User; $_ } | Export-Csv .\updated.csv
A bit more readable...
$data = Import-Csv .\data.csv
# replace the User Propery
$data |%{ $_.User = ConvertSidToUserName $_.User }
# export back out to csv
$data | Export-Csv .\updated.csv
Upvotes: 0
Reputation: 126942
Import-Csv .\sid.csv | ForEach-Object{
if($_.User -and $_.User -ne 'admin')
{
# User is not empty and not admin
# perform translation and assign the results back to the User column
# then return the current object back to the pipeline
$_.User = (New-Object System.Security.Principal.SecurityIdentifier $_.User).Translate([System.Security.Principal.NTAccount]).Value
$_
}
else
{
# Ignore empty User or admin
# and return the current object back to the pipeline
$_
}
} | Export-Csv .\users.csv
Upvotes: 1