1

Ok so I have a CSV file with Firstname, surname, title, office, and department.

I desperately need to add the SamAccountName to this csv file.

I then need to import this CSV file to AD.

Long story shortened i gave a CSV file with SamAccountName and various other fields to someone to update and they thought it would be a good idea to delete said columns… So i’m left with a spreadsheet that i cant import into AD.

I have little knowledge of powershell but i know technically speaking this should be possible if not i have a long job on my hands! :frowning:

Help!

Thanks

Lawtey

2 Spice ups
2

Why not send them the file again and ask them to update it properly?

Or dump the data from AD again. Sort this pile on firstname & surname. Sort the file other file by firstname & surname, add a new column for samaccountname and copy’n’ paste if for the other sorted file.

edit: actually sort them by lastname & firstname.

3

I wanted to know if it was possible firstly

Ive tried the following:

$users = Get-Content C:\test\sarah1.csv
$SplitName = -split $user
$FirstName=$SplitName[0]
$LastName=$splitName[1]

foreach ($user in $users){
Get-ADUser -Filter {(GivenName -eq $FirstName) -and (Surname -eq $LastName)} |Select-Object samaccountname |Export-CSV -Path C:\test\output.txt }

and many others i have googled literally all day and im stuck… thats why i came here. Its the first part im really after. the second part where you import them i can porbably nail with googles help.

4

didnt think about sorting the file by first name and surname. it might work i know shes deleted at least 10 users from it (our test users) ill give it a go thanks m boyle

5

Try this

$names = ipcsv  C:\test\sarah1.csv
ForEach ($Name in $Names)
{
   $FirstFilter  = $Name.FirstName
    $SecondFilter = $Name.LastName
      $user=Get-ADUser -Filter "GivenName -like '$FirstFilter' -and Surname -like '$SecondFilter'"
       if( $User )
    {
        $User |Select-Object samaccountname |Export-CSV -Path C:\test\output.csv -NoTypeInformation
    }
    else
    {
        New-Object PSObject -Property @{
            GivenName      = $FirstFilter
            Surname        = $SecondFilter
            SamAccountName = 'MISSING ACCOUNT'
            }
    }

}
2 Spice ups
6

You can’t add a directory element to a file from information that isn’t there, unless of course it can be calculated from something you do have. Not sure how you would want someone to help you with this without knowing how the data is structured. But why do it in Powershell if you are not familiar with it already? Just use Excel if your data is already in a CSV format, and you can Google and test your formulas a bit easier probably as its a more generic question/problem.

7 
$users = Get-Content C:\test\sarah1.csv
 
foreach ($user in $users)
{
    $SplitName = -split $user
    $FirstName = $SplitName[0]
    $LastName = $splitName[1]

    Get-ADUser -Filter {(GivenName -eq $FirstName) -and (Surname -eq $LastName)} |Select-Object samaccountname |Export-CSV -Path C:\test\output.txt -Append
}

Something like this?

You need to do the splitting inside the nody of the foreach loop.

8

JitenSh thank you so much!!! that worked perfectly!

1 Spice up
9

not sure if i will need to create a new post i might of got too excited here i only tested this on a test CSV with one first name and surname.

Now when i run the below on the actual CSV with over 500 users. its only out putting one users details, It doesn’t give me any errors. I didn’t know what New-Object would do (would it create a user in AD) so i didn’t add that in. Is this what i need to add in in order for it to work?

$names = ipcsv C:\test\sarah.csv
ForEach ($Name in $Names)
{
$FirstFilter = $Name.FirstName
$SecondFilter = $Name.LastName
$ThirdFilter = $Name.Department
$user=Get-ADUser -Filter “GivenName -like ‘$FirstFilter’ -and Surname -like ‘$SecondFilter’”
if( $User )
{
$User |Select-Object samaccountname |Export-CSV -Path C:\test\output.csv -NoTypeInformation
}
}

Let me know if i need to create a new post. :slight_smile:

thanks again.

Lawtey

10

In the Export-Csv part you need to add the -Append parameter. Otherwise the data in the csv file gets overwritten every time.

11

Ah, you created the new post after all :slight_smile:

        New-Object PSObject -Property @{
            GivenName      = $FirstFilter
            Surname        = $SecondFilter
            SamAccountName = 'MISSING ACCOUNT'
        }

This code creates a new object in powershell.

It does not touch AD in any way.

To see the cmdlets that do work with AD type this command in:

Get-Command -Module ActiveDirectory

That will show you all the cmdlets in the ActiveDirectory module.