SOLVED

Importing CSV, splitting names and joining

%3CLINGO-SUB%20id%3D%22lingo-sub-2555890%22%20slang%3D%22en-US%22%3EImporting%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555890%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EVery%20new%20to%20PowerShell%20and%20scripting%20so%20I'm%20trying%20to%20do%20a%20lab%20exercise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20take%20input%20from%20a%20CSV%20file%20where%20the%20name%20is%20formatted%20as%20%22Surname%2C%20Name%22.%20When%20I%20split%20this%20it%20returns%20as%20expected%20each%20surname%20and%20name%20on%20separate%20lines.%20Trying%20to%20%22join%22%20this%20to%20be%20%22Name%20Surname%22%20proves%20to%20be%20a%20challenge.%20I%20only%20get%20the%20first%20%22name%20surname%22%20repeated%20over%20and%20over%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECSV%3C%2FP%3E%3CPRE%3EName%3CBR%20%2F%3ERhodes%2C%20Dorthy%20%3CBR%20%2F%3EWoodward%2C%20Chauncey%3CBR%20%2F%3EHorn%2C%20Julian%26nbsp%3B%26nbsp%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3BScript%3C%2FP%3E%3CDIV%3E%3CPRE%3E%24Users%20%3D%20Import-Csv%20-Path%20%22C%3A%5CUsers%5Cuser%5CScripts%5CLabs%5CStuffForLabs%5CUserListB.csv%22%20-Delimiter%20%22%3B%22%20%7C%3CBR%20%2F%3EForEach-Object%20%7B%3CBR%20%2F%3E(%24_.Name%20-split%20'%2C%20')%3CBR%20%2F%3E%7D%3CBR%20%2F%3EForEach%20(%24user%20in%20%24users)%20%7B%3CBR%20%2F%3E%24name%20%3D%20%40(%24Users%5B1%5D%20%2B%20%22%20%22%20%2B%20%24Users%5B0%5D)%3CBR%20%2F%3E%24name%3CBR%20%2F%3E%7D%3C%2FPRE%3E%3CP%3ERunning%20the%20first%20part%26nbsp%3B%3CSPAN%3E%24users%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Breturns%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3ERhodes%3CBR%20%2F%3EDorthy%20%3CBR%20%2F%3EWoodward%3CBR%20%2F%3EChauncey%3CBR%20%2F%3EHorn%20%3CBR%20%2F%3EJulian%20%3C%2FPRE%3E%3CP%3EThe%20second%20part%26nbsp%3B%24name%20returns%20the%20same%20name%20multiple%20times%3C%2FP%3E%3CPRE%3EDorthy%20Rhodes%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20be%20very%20thankful%20for%20any%20help%20and%20pointers%20to%20what%20I%20am%20doing%20wrong.%20Have%20been%20banging%20my%20head%20against%20the%20wall%20for%20very%20long%20time%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKjetil%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2555890%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556043%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F907953%22%20target%3D%22_blank%22%3E%40kjetilj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20you%20need%20to%20set%20a%20variable%20and%20auto%20implement%2C%20because%20it%20is%20printing%20the%20same%20position%3C%2FP%3E%3CPRE%3E%24name%20%3D%20%40(%24Users%5B1%5D%20%2B%20%22%20%22%20%2B%20%24Users%5B0%5D)%3C%2FPRE%3E%3CP%3Ea%20logic%20like%20these%3C%2FP%3E%3CPRE%3E%24name%20%3D%20%40(%24Users%5Bi%5D%20%2B%20%22%20%22%20%2B%20%24Users%5Bj%5D)%3C%2FPRE%3E%3CP%3E%26nbsp%3Bi%3D1%20and%20j%3D0%2C%20using%20i%3Di%2B2%20and%20j%3Dj%2B2.%20You%20can%20use%20a%20for%20to%20print%20the%20next%20position%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556711%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556711%22%20slang%3D%22en-US%22%3EThank%20you!%3CBR%20%2F%3EI%20understand%20what%20your%20saying%2C%20but%20need%20to%20figure%20out%20how%20to%20do%20it%20%3A)%3C%2Fimg%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556848%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F907953%22%20target%3D%22_blank%22%3E%40kjetilj%3C%2FA%3E%26nbsp%3BHi%2C%20like%20these.%20Dont%20forget%20to%20flag%20as%20a%20solution%20if%20it%20works.%20Thank%20you%20very%20much%3C%2FP%3E%3CPRE%3E%24Users%20%3D%20Import-Csv%20-Path%20%22C%3A%5CUsers%5Cuser%5CScripts%5CLabs%5CStuffForLabs%5CUserListB.csv%22%20-Delimiter%20%22%3B%22%20%7C%3CBR%20%2F%3EForEach-Object%20%7B%3CBR%20%2F%3E(%24_.Name%20-split%20'%2C%20')%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%24i%3D1%3CBR%20%2F%3E%24j%3D0%3CBR%20%2F%3EForEach%20(%24user%20in%20%24users)%20%7B%3CBR%20%2F%3E%24name%20%3D%20%40(%24Users%5Bi%5D%20%2B%20%22%20%22%20%2B%20%24Users%5Bj%5D)%3CBR%20%2F%3E%24i%3D%24i%2B2%3CBR%20%2F%3E%24j%3D%24j%2B2%3CBR%20%2F%3E%24name%3CBR%20%2F%3E%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556926%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867300%22%20target%3D%22_blank%22%3E%40yuzoyox%3C%2FA%3E%26nbsp%3BHi%20again%20%3A)%3C%2Fimg%3E%3CBR%20%2F%3EThank%20you%2C%20I'm%20very%20grateful%20for%20your%20help.%20You%20confirmed%20the%20way%20I%20thought%20it%20was%20supposed%20to%20be%20written%2C%20but%20it%20sadly%20gave%20an%20ParseError.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EParserError%3A%20%3CBR%20%2F%3ELine%20%7C%3CBR%20%2F%3E2%20%7C%20%24name%20%3D%20%40(%24Users%5Bi%5D%20%2B%20%22%20%22%20%2B%20%24Users%5Bj%5D)%20%3CBR%20%2F%3E%7C%20~%3CBR%20%2F%3E%7C%20Array%20index%20expression%20is%20missing%20or%20not%20valid.%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2557014%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F907953%22%20target%3D%22_blank%22%3E%40kjetilj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20possible%20solutions%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%24Users%20%3D%20Import-Csv%20-Path%20%22C%3A%5CUsers%5Cuser%5CScripts%5CLabs%5CStuffForLabs%5CUserListB.csv%22%20-Delimiter%20%22%3B%22%20%7C%0AForEach-Object%20%7B%0A(%24_.Name%20-split%20'%2C%20')%0A%7D%0Afor%20(%24i%3D0%3B%20%24i%20-le%20(%24users.count-1)%3B%20%24i%3D%24i%2B2%20)%7B%0A%24Users%5B%24i%2B1%5D%20%2B%20%22%20%22%20%2B%20%24Users%5B%24i%5D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20the%20output%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3EDorthy%20%20Rhodes%0AChauncey%20Woodward%0AJulian%20%20Horn%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2557018%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383049%22%20target%3D%22_blank%22%3E%40gastone%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat!%20Yes%2C%20that%20worked.%20No%20I%20need%20to%20understand%20it...%20better%20reread%20the%20about_for%20file%20again%20and%20compare%20it%20to%20your%20solution%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2F%408341BD79091AF36AA2A09063B554B5CD%2Fimages%2Femoticons%2Fsmile_40x40.gif%22%20alt%3D%22%3Asmile%3A%22%20title%3D%22%3Asmile%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2557035%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%2C%20splitting%20names%20and%20joining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557035%22%20slang%3D%22en-US%22%3ESorry%20I%20forgot%20to%20add%20%24%3CBR%20%2F%3E%3CBR%20%2F%3E%24Users%20%3D%20Import-Csv%20-Path%20%22C%3A%5CUsers%5Cuser%5CScripts%5CLabs%5CStuffForLabs%5CUserListB.csv%22%20-Delimiter%20%22%3B%22%20%7C%3CBR%20%2F%3EForEach-Object%20%7B%3CBR%20%2F%3E(%24_.Name%20-split%20'%2C%20')%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%24i%3D1%3CBR%20%2F%3E%24j%3D0%3CBR%20%2F%3EForEach%20(%24user%20in%20%24users)%20%7B%3CBR%20%2F%3E%24name%20%3D%20%40(%24Users%5B%24i%5D%20%2B%20%22%20%22%20%2B%20%24Users%5B%24j%5D)%3CBR%20%2F%3E%24i%3D%24i%2B2%3CBR%20%2F%3E%24j%3D%24j%2B2%3CBR%20%2F%3E%24name%3CBR%20%2F%3E%7D%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

Very new to PowerShell and scripting so I'm trying to do a lab exercise.

 

I'm trying to take input from a CSV file where the name is formatted as "Surname, Name". When I split this it returns as expected each surname and name on separate lines. Trying to "join" this to be "Name Surname" proves to be a challenge. I only get the first "name surname" repeated over and over again.

 

CSV

Name
Rhodes, Dorthy
Woodward, Chauncey
Horn, Julian  

 Script

$Users = Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";" |
ForEach-Object {
($_.Name -split ', ')
}
ForEach ($user in $users) {
$name = @($Users[1] + " " + $Users[0])
$name
}

Running the first part $users returns

Rhodes
Dorthy
Woodward
Chauncey
Horn
Julian

The second part $name returns the same name multiple times

Dorthy Rhodes

 

Would be very thankful for any help and pointers to what I am doing wrong. Have been banging my head against the wall for very long time now.

 

Kjetil

12 Replies

@kjetilj 

 

Hi, you need to set a variable and auto implement, because it is printing the same position

$name = @($Users[1] + " " + $Users[0])

a logic like these

$name = @($Users[i] + " " + $Users[j])

 i=1 and j=0, using i=i+2 and j=j+2. You can use a for to print the next position

Thank you!
I understand what your saying, but need to figure out how to do it :)

@kjetilj Hi, like these. Dont forget to flag as a solution if it works. Thank you very much

$Users = Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";" |
ForEach-Object {
($_.Name -split ', ')
}
$i=1
$j=0
ForEach ($user in $users) {
$name = @($Users[i] + " " + $Users[j])
$i=$i+2
$j=$j+2
$name
}

 

@yuzoyox Hi again :)
Thank you, I'm very grateful for your help. You confirmed the way I thought it was supposed to be written, but it sadly gave an ParseError.

 

 

ParserError: 
Line |
2 | $name = @($Users[i] + " " + $Users[j])
| ~
| Array index expression is missing or not valid.

 

best response confirmed by kjetilj (Occasional Contributor)
Solution

@kjetilj 

Here is a possible solutions

 

$Users = Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";" |
ForEach-Object {
($_.Name -split ', ')
}
for ($i=0; $i -le ($users.count-1); $i=$i+2 ){
$Users[$i+1] + " " + $Users[$i]
}

 

 

this is the output

 

Dorthy  Rhodes
Chauncey Woodward
Julian  Horn

 

@gastone 

Great! Yes, that worked. No I need to understand it... better reread the about_for file again and compare it to your solution :smile:

Sorry I forgot to add $

$Users = Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";" |
ForEach-Object {
($_.Name -split ', ')
}
$i=1
$j=0
ForEach ($user in $users) {
$name = @($Users[$i] + " " + $Users[$j])
$i=$i+2
$j=$j+2
$name
}

@yuzoyox 

Uh, I should have seen that myself so that's on me! Just tired after steering at the same lines and comparing it to "google" for hours.

Grateful for you help, and this makes more sense to n00b !

You are on the right place, here is to solve problem and learn a new way to use powershell, like @gastone use. powershell community are very friendly

Another way to approach it:

$Users = Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";"
foreach ($user in $Users)
{
    $name = "{1} {0}" -f ($user -split ', ')
    $name
} 

Might be a little easier to understand.

Though I have complicated it with the way I have used the -f format string.

 

@psophos 

Nice, thank you! Came across similar examples when I searched for a solutions but at that time it didn't make sense to me. Now it's more clear :)

 

Will play around with all approaches as the CSV grows with more real life information. 

@psophosNice the idea to use -f but your code is wrong, not really, the worng part is  that @kjetilj 

using

Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";"

for reading a non csv file and the  non present delimiter ";" is necessary to correct the starting point error.

This is not a CSV!

Name
Rhodes, Dorthy
Woodward, Chauncey
Horn, Julian  

 

@psophosThis the correct code

cls
"The corrected script, for the non CSV..."
$Users = Import-Csv -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" -Delimiter ";"
foreach ($user in $Users)
{
    $name = "{1} {0}" -f ($user.name -split ',')
    $name
} 

 

A more readable code with some comment

# 
# skip the first line, so we have a regular csv
$reallyAcsv=Get-Content -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" | Select-Object -Skip 1
# Add header for readability 
$USRs=$reallyAcsv | ConvertFrom-Csv -Header 'surname','name'
# Now is easy to understand...
$USRs|foreach-object {"$($_.name)  $($_.surname)"}
# if you want only name...
$USRs|foreach-object {$_.name)}
# if you want only surname...
$USRs|foreach-object {$_.surname)}

The same code in a single line

"+ In a single line +"
Get-Content -Path "C:\Users\user\Scripts\Labs\StuffForLabs\UserListB.csv" | 
    Select-Object -Skip 1 | 
        ConvertFrom-Csv -Header 'surname','name' | 
            foreach-object {"$($_.name)  $($_.surname)"}

I hope now is more clear the different solutions
Bye Gas