Link to home
Start Free TrialLog in
Avatar of namerg
namergFlag for United States of America

asked on

How to string cat first char with another value and export a new csv?

How to extract the first character of GivenName and string cat with Surname and export it into a new csv file and add the Username Header.



"GivenName","Surname","Name"
"Carole","Greer","Carole Greer"
"Jorge","Lynch","Jorge Lynch"
"Sally","Parker","Sally Parker"
"Demetrius","Tatum","Demetrius Tatum"
"Joshua","Harris","Joshua Harris"
"Michael","Weaver","Michael Weaver"
"Royce","Hicks","Royce Hicks"
"Timothy","Dutton","Timothy Dutton"
"Karen","Murphy","Karen Murphy"
"James","White","James White"

Open in new window

I have the following code:

$SourceUsersFile = " " 
$SourceUsersWithUsernameFile = " "
Import-Csv -Path $SourceUsersFile |
   Select-Object -Property GivenName, Surname, Name, @{n='Username'; e={$_.GivenName}} |
   Export-Csv -NoTypeInformation -Path $SourceUsersWithUsernameFile


#$subdomain = "Carole"
#$subdomain.Substring(0,1)

Open in new window

Is it possible to apply the For Each-Object instead of Select-Object?


Thanks for your help

Avatar of oBdA
oBdA

All you need is a subexpression (or two, actually):
$SourceUsersFile = " " 
$SourceUsersWithUsernameFile = " "
Import-Csv -Path $SourceUsersFile |
   Select-Object -Property GivenName, Surname, Name, @{n='Username'; e={"$($_.GivenName[0])$($_.Surname)"}} |
   Export-Csv -NoTypeInformation -Path $SourceUsersWithUsernameFile

Open in new window

Do you need/want to force a special case for the user name (all lower, first two upper, ...)?

Here's an updated code snippet that extracts the first character of GivenName, concatenates it with Surname, and exports the result to a new CSV file with a Username header: 

$SourceUsersFile = "C:\Path\To\Source\Users\File.csv"
$SourceUsersWithUsernameFile = "C:\Path\To\Source\Users\With\Username\File.csv"


Import-Csv -Path $SourceUsersFile |
   Select-Object -Property GivenName, Surname, @{n='Name'; e={$_.GivenName + ' ' + $_.Surname}}, @{n='Username'; e={$_.GivenName.Substring(0,1) + $_.Surname}} |
   Export-Csv -NoTypeInformation -Path $SourceUsersWithUsernameFile

Open in new window

In this code, the Select-Object cmdlet is used to select the GivenNameSurnameName, and Username properties. The Name property is created by concatenating GivenName and Surname with a space in between, while the Username property is created by extracting the first character of GivenName using the Substring method and concatenating it with Surname. The Export-Csv cmdlet is then used to export the result to a new CSV file with the -NoTypeInformation parameter to exclude the type information from the output. 


Yes, you can use the ForEach-Object cmdlet instead of the Select-Object cmdlet to achieve the same result. Here's an example code that uses ForEach-Object: 

$SourceUsersFile = "C:\Path\To\Source\Users\File.csv"

$SourceUsersWithUsernameFile = "C:\Path\To\Source\Users\With\Username\File.csv"


Import-Csv -Path $SourceUsersFile | ForEach-Object {

    $GivenName = $_.GivenName

    $Surname = $_.Surname

    $Name = "$GivenName $Surname"

    $Username = "$($GivenName.Substring(0, 1))$Surname"


    [PSCustomObject]@{

        GivenName = $GivenName

        Surname = $Surname

        Name = $Name

        Username = $Username

    }

} | Export-Csv -NoTypeInformation -Path $SourceUsersWithUsernameFile



In this code, the ForEach-Object cmdlet is used to iterate over each row of the input CSV file. Inside the loop, the GivenNameSurnameName, and Username properties are created using the same logic as in the previous code. A custom object is then created using the PSCustomObject type accelerator and the four properties are added to the object. Finally, the custom object is sent to the pipeline and exported to a new CSV file using the Export-Csv cmdlet.

Avatar of namerg

ASKER

@Obda,
Please if you do not mind how to add having the first character uppercase and the value for Surname all lowercase? Thanks for your help
ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial