How to merge variables to one string and output to specific colum in CSV?

Question

I made a script that generates usernames based on names out of a CSV. Now I want to export those usernames to the same CSV I got them, but in a third column that's named username. How do I merge the output and export them to my CSV? Can I capture the output of Write-Host? I just noticed that my Nachname.substring doesn't include the first letter, why is that?

$users = Import-Csv -Path C:\Users\username\Desktop\Namen.csv -Header "Vorname","Nachname","Username" -Delimiter ';' 

Foreach($user in $users) {
    $number = Get-Random -Minimum 10 -Maximum 99
    $firstName = $user.Vorname.Substring(0,1)
    $lastName = $user.Nachname.Substring(0.7)
    $usernames = Write-Host "$($firstName)$($lastName)$($number)"
}

My CSV looks like this:

Monika Aldegar MAldegar57
Iban Ashok IAshok81
Aelmaer Dusko ADukso15
and so on...

now those are two columns for the first name and last name. I want the created username in the third column

Can you provide us a sample csv with 1 record and the output you expect? — Martin Brandl, Sep 26 '19 at 08:35
@MartinBrandl sure, https://imgur.com/a/MkWt4ua. My script currently outputs the names like Mldegar29, Ishok41, Aukso22 and so on. Now I want to export those full usernames to the CSV in the third column. — kardon, Sep 26 '19 at 08:45
Please [edit] your question and show your CSV sample *as text*. Do not post screenshots on external resources that may vanish at any given time. — Ansgar Wiechers, Sep 26 '19 at 08:57

score 0 · Answer 1 · edited Sep 27 '19 at 08:46

You have a litte error in

$lastName = $user.Nachname.Substring(0.7)

Should be:

$lastName = $user.Nachname.Substring(0,7)

For collecting your information you can use a custom object

$myobj = new-Object PSObject

and add it into a list:

[System.Collections.ArrayList]$myList = @()

Then add properties into it:

Add-Member -InputObject $myobj -NotePropertyName Vorname -NotePropertyValue $firstname
Add-Member -InputObject $myobj -NotePropertyName Nachname -NotePropertyValue $lastname

Then assign your newly created username to a variable

$username=$firstName+$lastName+$number

And also add it into the object

Add-Member -InputObject $myobj -NotePropertyName Username -NotePropertyValue $username

If you have all the data you wanted to retrieve add the object into a list:

$mylist.add($myobj)

So in the end your script could look like:

$users = Import-Csv -Path C:\Users\username\Desktop\Namen.csv -Header   "Vorname","Nachname","Username" -Delimiter ';' 
[System.Collections.ArrayList]$myList = @()
Foreach($user in $users) {
    $myobj = new-Object PSObject
    $number = Get-Random -Minimum 10 -Maximum 99
    $firstName = $user.Vorname.Substring(0,1)
    $lastName = $user.Nachname.Substring(0.7)
    Add-Member -InputObject $myobj -NotePropertyName Vorname -NotePropertyValue $user.Vorname
    Add-Member -InputObject $myobj -NotePropertyName Nachname -NotePropertyValue $user.Nachname
    $username=$firstName+$lastName+$number
    Add-Member -InputObject $myobj -NotePropertyName 
    Username -NotePropertyValue $username
    $mylist.add($myobj)
} 
$mylist | Export-CSV "YOURPATH" -Delimiter ";"

score 0 · Answer 2 · answered Sep 26 '19 at 09:03

You substring on the Nachname property uses a dot instead of a comma. You should also check for the length there in order to avoid an exception if the name is less then 7 characters. This is how I would implement it:

$users = Import-Csv -Path C:\Users\username\Desktop\Namen.csv -Header "Vorname","Nachname" -Delimiter ';'
$users | Select-Object *, @{e={'{0}{1}{2}' -f $_.Vorname.Substring(0,1), $_.Nachname.Substring(0, ([System.Math]::Min(7, $_.Nachname.Length))), (Get-Random -Minimum 10 -Maximum 99)}; l="UserName"}

Sample output:

Vorname Nachname UserName  
------- -------- --------
Monika  Aldegar  MAldegar94
Iban    Ashok    IAshok85

You probably want to save it back to a CSV by appending the following line to the script: | Export-CSV "C:\Users\username\Desktop\Namen2.csv" -Delimiter ";"

score 0 · Answer 3 · answered Sep 26 '19 at 09:10

No, you cannot capture the output of Write-Host. Related. Also related.

Use a calculated property for adding a new column from values of existing columns.

$csv     = 'C:\Users\username\Desktop\Namen.csv'
$headers = 'Vorname', 'Nachname'
$delim   = ';'

(Import-Csv $csv -Header $headers -Delimiter $delim) |
    Select-Object *, @{n='Username';e={
        $number    = Get-Random -Minimum 10 -Maximum 99
        $firstName = $user.Vorname.Substring(0,1)
        $lastName  = $user.Nachname.Substring(0,7)
        "${firstName}${lastName}${number}"
    }} |
    Export-Csv $csv -NoType -Delimiter $delim

Note that the parentheses around the Import-Csv statement are required because you want to write the output back to the same file. Without them you'd end up with an empty file because Export-Csv would start writing before Import-Csv finished reading, thus effectively truncating the file.

How to merge variables to one string and output to specific colum in CSV?

3 Answers3