CODEWITHSUNDEEP
powershellpowershell-3.0
Fiddle Freak
Fiddle Freak

Reputation: 2051

Powershell split csv column value into two seperate column values

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?

Upvotes: 0

Views: 13352

Answers (1)

skukx
skukx

Reputation: 617

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

Upvotes: 1

Related Questions