How to split single column in csv with column names in powershell-CodePudding

I have a csv with 3 column names as Name, Period and Data, where I want to separate Name into multiple column names as

Sample.csv :

"Name","Period","Data"
"App - NA, 11002, Project - NA, FY22, Working, USD, Budget, 640605","Jan","171.4"
"App - NA, 11002, Project - NA, FY22, Working, USD, Budget, 640605","Feb","171.8"

i want the output.csv file as

"Application","Entity","Project","Years","Version","Currency","Scenario","Account","Period","Data"
"App - NA", "11002", "Project - NA", "FY22"," Working", "USD", "Budget", "640605","Jan","171.4"
"App - NA", "11002", "Project - NA", "FY22", "Working", "USD", "Budget", "640605","Feb","171.8"

I have trim and split also..but it doesn't work out

Import-Csv "D:\sample.csv"  | Foreach-Object {
    $Name = ($_.Codes -split ',').Trim()
    [pscustomobject]@{Application = $_.Name; Codes = $codes[0]}
    [pscustomobject]@{Entity = $_.Name; Codes = $codes[1]}
    [pscustomobject]@{Project = $_.Name; Codes = $codes[2]}
    [pscustomobject]@{Years = $_.Name; Codes = $codes[3]}
    [pscustomobject]@{Version = $_.Name; Codes = $codes[4]}
    [pscustomobject]@{Currency = $_.Name; Codes = $codes[5]}
    [pscustomobject]@{Scenario = $_.Name; Codes = $codes[6]}
    [pscustomobject]@{Account = $_.Name; Codes = $codes[7]}
    [pscustomobject]@{ "Period" = $_.Period}
    [pscustomobject]@{"Data" = $_.Data}
} |  Export-Csv 'D:\output.csv'

CodePudding user response：

You are over thinking this.

Here's two ways of doing that:

Import-Csv -Path 'D:\Test\yourCsvFile.csv' | ForEach-Object {
    $newColumns = ($_.Name -split ',').Trim()
    [PsCustomObject]@{
        Application = $newColumns[0]
        Entity      = $newColumns[1]
        Project     = $newColumns[2]
        Years       = $newColumns[3]
        Version     = $newColumns[4]
        Currency    = $newColumns[5]
        Scenario    = $newColumns[6]
        Account     = $newColumns[7]
        Period      = $_.Period
        Data        = $_.Data
    }
} | Export-Csv -Path 'D:\Test\yourNewCsvFile.csv' -NoTypeInformation

Or use Select-Object with calculated properties (a bit less readable)

Import-Csv -Path 'D:\Test\yourCsvFile.csv' | ForEach-Object {
    $newColumns = ($_.Name -split ',').Trim()
    $_ | Select-Object @{Name = 'Application'; Expression = {$newColumns[0]}},
                       @{Name = 'Entity'; Expression = {$newColumns[1]}},
                       @{Name = 'Project'; Expression = {$newColumns[2]}},
                       @{Name = 'Years'; Expression = {$newColumns[3]}},
                       @{Name = 'Version'; Expression = {$newColumns[4]}},
                       @{Name = 'Currency'; Expression = {$newColumns[5]}},
                       @{Name = 'Scenario'; Expression = {$newColumns[6]}},
                       @{Name = 'Account'; Expression = {$newColumns[7]}},
                       Period, Data
} | Export-Csv -Path 'D:\Test\yourNewCsvFile.csv' -NoTypeInformation

CodePudding user response：

This might be another way to do it:

$header = @(
    "Application", "Entity", "Project", "Years", "Version"
    "Currency", "Scenario", "Account", "Period", "Data"
)

(Get-Content 'D:\Test\yourCsvFile.csv' -ReadCount 0) | Select-Object -Skip 1 | & {
    begin { $header -join ',' }
    process { $_ -replace '(?<!"),\s*(?!")', '","' }
} | Export-Csv 'D:\output.csv' -NoTypeInformation

Regex demo here: https://regex101.com/r/Be1F5F/1