# rsimport.ps1 - Rosetta Stone import script # (C) 2012 Daniele Raffo # # # This script eases the import of users into the Rosetta Stone service. # It takes as input a CSV file formatted as such: # # first_name,last_name,email # John,Smith,jsmith@example.org # Jane,Doe,jane@example.com # ... # # and automatically generates an appropriate username and password, sending them via email to each user. # The script outputs an import text for Rosetta Stone. You must run the Rosetta Stone Manager, # select the menu item Learners -> Import, tick the option to use column headings from the first row, # and paste the import text into the Manual Import textfield. # CSV file containing the users to import $csvfilein = "toimport.csv" # IP address of your SMTP server (no auth) $mailserver = "11.22.33.44" # Your company name $rsorg = "YourOrg" # RS settings typical of your installation $rsurl = "http://rosettastone.yourorg.com" $rsgroup = "Main Group" $rslanguage = "French Level 1" # RS mail notification addresses $rsmailfrom = "rosettastone@yourorg.com" $rsmailbcc = "root@yourorg.com" # Return the first piece of a composite firstname/lastname function takefirst([string]$in) { $out = $in.Trim() $out = $out.toLower() $out = $out.Replace(".", "") $out = $out.Replace("'", "") $out = $out.Replace("-", " ") $tokens = $out.Split('- ') $out = $tokens[0] # Handle special lastname cases $particles = "de", "da", "di", "dos", "le", "li", "los", "el", "al", "van", "von" if ($particles -contains $tokens[0]) { $out = $tokens[0] + $tokens[1] } if ($tokens[0] -eq "van" -and $tokens[1] -eq "der") { $out = "vander" + $tokens[2] } if ($tokens[0] -eq "de" -and $tokens[1] -eq "la") { $out = "dela" + $tokens[2] } if ($tokens[0] -eq "de" -and $tokens[1] -eq "los") { $out = "delos" + $tokens[2] } return $out } $list = Import-Csv $csvfilein $rand = New-Object System.Random $user = New-Object 'object[,]' 500,5 $i = 0 Clear-Host Write-Host "Copy & paste the following lines to the Rosetta Stone import textfield:" Write-Host " " Write-Host "-------8<-------8<-------8<-------8<-------8<-------8<-------8<-------" Write-Host "user_name,password,group,language,first_name,last_name,email" foreach ($entry in $list) { # Copy firstname, lastname, and email $firstname = ($entry.first_name).Trim() $lastname = ($entry.last_name).Trim() $email = ($entry.email).Trim() # Generate RS username from firstname and lastname $username = $(takefirst($firstname)) + "." + $(takefirst($lastname)) $username = $username.substring(0, [System.Math]::Min(23, $username.length)) # Generate random password $password = "rstmp" + $rand.next(100000,999999) Write-Host "$username,$password,$rsgroup,$rslanguage,$firstname,$lastname,$email" $user[$i,0] = $username $user[$i,1] = $password $user[$i,2] = $firstname $user[$i,3] = $lastname $user[$i,4] = $email $i++ } Write-Host "-------8<-------8<-------8<-------8<-------8<-------8<-------8<-------" Write-Host " " $notify = Read-Host "Do you want to notify users via email? [y/n]" if ($notify -eq "y") { $notifyeach = Read-Host "Ready to send email notifications. Confirm for each user? [y/n]" for ($r = 0; $r -lt $i; $r++) { if ($notifyeach -eq "y") { $notifyuser = Read-Host "Send email to $($user[$r,2]) $($user[$r,3]) <$($user[$r,4])>? [y/n]" } else { $notifyuser = "y" } if ($notifyuser -eq "y") { # Send the email message to the user Write-Host "Sending email to $($user[$r,2]) $($user[$r,3]) <$($user[$r,4])> ..." $emailFrom = New-Object system.net.Mail.MailAddress $rsmailfrom, "$rsorg Rosetta Stone" $emailTo = New-Object system.net.Mail.MailAddress "$($user[$r,4])", "$($user[$r,2]) $($user[$r,3])" $emailBcc = New-Object system.Net.Mail.MailAddress $rsmailbcc $emailMessage = New-Object system.Net.Mail.MailMessage $emailFrom, $emailTo $emailMessage.Bcc.Add($emailBcc); $emailMessage.Subject = "Welcome to $rsorg Rosetta Stone" $emailMessage.Body = @" Dear $($user[$r,2]) $($user[$r,3]), Welcome to the $rsorg Rosetta Stone service. Your username is: $($user[$r,0]) Your password is: $($user[$r,1]) Please change your password upon your first login. You can access Rosetta Stone at $rsurl . Best regards, The $rsorg Rosetta Stone team "@ $smtpClient = New-Object System.Net.Mail.SmtpClient $mailserver try { $smtpClient.Send($emailMessage); } catch { "Unable to send email: " -f $Error[0] } } } } Write-Host "Done."