username length?

mallyg27

I'm importing usernames to active directory through excel. My question is, I have 800 users. I want the username to be first letter of the first name and first five letters of the last name. A lot of my users have long last names. Do I have to actually manually delete the last few letters off there names or is there a easier way to do this?

dynamik

Found this quick and appears to do what you want.

Dim rng as Range, cell as Range
With Activesheet
set rng = Intersect(.usedRange,.columns(5)).Cells
End With
for each cell in rng
cell = Left(cell.Text,5)
Next

mallyg27

dynamik wrote: »

Found this quick and appears to do what you want.

Dim rng as Range, cell as Range
With Activesheet
set rng = Intersect(.usedRange,.columns(5)).Cells
End With
for each cell in rng
cell = Left(cell.Text,5)
Next

I'm not really too familiar with the running of scripts. What do I have to do with that code that you gave me?

dynamik

You'll have to do that research on your own. I just did a little googling because I was curious if it was possible.

Unfortunately, my Office skills are rather weak

That should hopefully get you pointed in the right direction though. You could probably search for some beginner tutorials on how to do scripting in Excel or something.

mallyg27

dynamik wrote: »

You'll have to do that research on your own. I just did a little googling because I was curious if it was possible.

Unfortunately, my Office skills are rather weak

That should hopefully get you pointed in the right direction though. You could probably search for some beginner tutorials on how to do scripting in Excel or something.

Thanks a lot. You've been of great help to me this weekend.

inc0mplete

Dynamik is the man. This has no relevance. Sorry for the ***** if that's what people call it.

RobertKaucher

inc0mplete wrote: »

Dynamik is the man. This has no relevance. Sorry for the ***** if that's what people call it.

Butt kissing? Lol

RobertKaucher

mallyg27 wrote: »

I'm importing usernames to active directory through excel. My question is, I have 800 users. I want the username to be first letter of the first name and first five letters of the last name. A lot of my users have long last names. Do I have to actually manually delete the last few letters off there names or is there a easier way to do this?

Even if you have figured this out... could you send me a small sample of your excel file? I would gladly write a PowerShell script to do this.

mallyg27

RobertKaucher wrote: »

Even if you have figured this out... could you send me a small sample of your excel file? I would gladly write a PowerShell script to do this.

I will send you the file when I get home.

RobertKaucher

here is the script that I have come up with. It is quick and dirty and I think there MUST be a powershell function to allow you to trim the a string so that it is only x number of characters long, but I have no idea what it is. I also modified the user's path to the home dir to reflect the correct spelling of the username. I had to open the original csv and save it again as a CSV in Excel because PowerShell was choking on your original CSV. Something was wrong with the format. I will email you my results!

I would love any criticism of the script. Thanks!

$results = Import-Csv .\data.csv
for($a = 0; $a -lt $results.Length; $a++)
{
##Split the username and rebuild it so that it is only 6 chars
$goodName = $results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username.toCharArray()
for($i = 0; $i -lt 6; $i++)
{
$validUser += $goodName/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$i[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2
}
$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username = $validUser
###Split the home directory and then replace the username with the valid one.
$homepath = $results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.home.split("$")
$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.home = $homepath[0]+"$\"+$validUser
$validUser = ""

}
$results | export-csv .\NewAddUserData.csv -NoTypeInformation

dynamik

Substring method.

RobertKaucher

dynamik wrote: »

Substring method.

Thanks!

$results=Import-Csv .\data.csv
for($a= 0; $a-lt$results.Length; $a++)
{
##Split the username and rebuild it so that it is only 6 chars
$i=$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username.toCharArray().Length
if($i-le 6)
{
$j=$i
}
else
{
$j= 6
}
$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username =$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username.Substring(0,$j)
###Split the home directory and then replace the username with the valid one.
$homepath=$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.home.split("$")
$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.home =$homepath[0]+"$\"+$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username
}
$results | export-csv .\NewAddUserData.csv-NoTypeInformation

I need to think about C# with PowerShell. Because had I been doing this in C# I would have known to use Substring... It is just so very different sometimes I don't even consider they have tons of similar methonds.

dynamik

It's all .NET baby, whoooo!

Psoasman

Very nice. command line is awesome for this sort of thing!

mallyg27

RobertKaucher wrote: »

Thanks!

$results=Import-Csv .\data.csv
for($a= 0; $a-lt$results.Length; $a++)
{
##Split the username and rebuild it so that it is only 6 chars
$i=$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username.toCharArray().Length
if($i-le 6)
{
$j=$i
}
else
{
$j= 6
}
$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username =$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username.Substring(0,$j)
###Split the home directory and then replace the username with the valid one.
$homepath=$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.home.split("$")
$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.home =$homepath[0]+"$\"+$results/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]$a[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2.username
}
$results | export-csv .\NewAddUserData.csv-NoTypeInformation

I need to think about C# with PowerShell. Because had I been doing this in C# I would have known to use Substring... It is just so very different sometimes I don't even consider they have tons of similar methonds.

That's exactly what I needed. Good Stuff. Thanks again. What did you use to do this, so I can read up on it? Because, I will need to do this every year when we get new students.