Powershell split csv column value into two seperate column values

Question

Lets say I have a csv file with 5 records (in reality it's about 80,000).

Csv File:

column1,columnICareAbout,column2
someRandomValue,John Doe - Generic,someRandomValue
someRandomValue,Captain Teemo - pocketPet,someRandomValue
someRandomValue,Pinky Brain - Fictional,someRandomValue
someRandomValue,Miyamoto Musashi - swordsman,someRandomValue
someRandomValue,Kato yasunori - troubleMaker - Extreme,someRandomValue

Given the following code:

Note: I know the array values are correct, I just need to know how to loop through the array in the expression.

$firstNames = @()
$firstNameCounter = 0
$lastNames = @()
$lastNameCounter = 0

Import-Csv $file1 | 
    foreach { 
        $firstNames += $_.Description.split(" ")[0]
        $lastNames += $_.Description.split(" ")[1]
    }

Import-Csv $file1 |
    Select-Object  *, @{n='First Name'; e={$firstNames[$firstNameCounter];$script:firstNameCounter++}}, @{n='Last Name'; e={$lastNames[$lastNameCounter];$script:lastNameCounter++}} | 
    Export-Csv "testresults.csv" -NoTypeInformation -Force

I only get the first element in the array every time. So the end result file looks like this

column1,columnICareAbout,column2,First Name,Last Name
someRandomValue,John Doe - Generic,someRandomValue,John,Doe
someRandomValue,Captain Teemo - pocketPet,someRandomValue,John,Doe
someRandomValue,Pinky Brain - Fictional,someRandomValue,John,Doe
someRandomValue,Miyamoto Musashi - swordsman,someRandomValue,John,Doe
someRandomValue,Kato yasunori - troubleMaker - Extreme,someRandomValue,John,Doe

I want the file to look like this

column1,columnICareAbout,column2,First Name,Last Name
someRandomValue,John Doe - Generic,someRandomValue,John,Doe
someRandomValue,Captain Teemo - pocketPet,someRandomValue,Captain,Teemo
someRandomValue,Pinky Brain - Fictional,someRandomValue,Pinky,Brain
someRandomValue,Miyamoto Musashi - swordsman,someRandomValue,Miyamoto,Musashi
someRandomValue,Kato yasunori - troubleMaker - Extreme,someRandomValue,Kato,yasunori

Can Anyone tell me what I'm doing wrong?

Why do it in two steps? Import-CSV $File1 | Select *,@{l='First Name';e={$_.Description.Split(' ')[0]}},@{l='Last Name';e={$_.Description.Split(' ')[1]}} | Export-CSV testresults.csv -NoType -Force — TheMadTechnician
– TheMadTechnician, Commented Jan 20, 2016 at 19:05
Rather than having a second loop just append the properties in your first loop with $_ | Add-Member -MemberType NoteProperty -Name FirstName -Value $firstName — skukx
– skukx, Commented Jan 20, 2016 at 20:43
Thanks TheMadTechnician. This worked. Although I still want to know how to iterate through an array during an expression. This would help me with a problem I am facing at this moment. — Fiddle Freak
– Fiddle Freak, Commented Jan 20, 2016 at 22:15
@skukx Thanks for the answer, I might be able to use this logic on this next problem I'm facing. — Fiddle Freak
– Fiddle Freak, Commented Jan 20, 2016 at 22:17

skukx · Accepted Answer · 2016-01-20 20:46:14Z

1

Import-Csv $file1 | 
foreach { 
    $firstName = $_.Description.split(" ")[0]
    $lastName = $_.Description.split(" ")[1]

    $_ | Add-Member -MemberType NoteProperty -Name FirstName -Value $firstName
    $_ | Add-Member -MemberType NoteProperty -Name LastName -Value $lastName
} | Export-Csv 'results.csv'

Add-Member commands will append the 2 columns FirstName and LastName

answered Jan 20, 2016 at 20:46

skukx

6275 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell split csv column value into two seperate column values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related