CODEWITHSUNDEEP
csvpowershell
themagicman2136
themagicman2136

Reputation: 31

How to separate one row into multiple rows based on a single cell containing multiple values in the same row in a CSV file using PowerShell

I am attempting to loop through each row of a CSV file using PowerShell to separate out a specific cell in the row into multiple rows based on a delimiter (;). My data will look something like this:

ID Name Invoices       Member Since 
1  John A123;A234;C316 1999 
2  Dave B219;C216      2010 
3  Jim  D213;E117;G214 2003

And I would need the output to look like this:

ID Name Invoices Member Since 
1  John A123     1999 
1  John A234     1999 
1  John C316     1999 
2  Dave B219     2010 
2  Dave C216     2010 
3  Jim  D213     2003 
3  Jim  E117     2003
3  Jim  G214     2003

Finally, at the end, I would need to export this file to a new CSV file. Any help would be greatly appreciated. I have been researching this for a few hours and am having a lot of difficulties in finding resources on how to do this. Thanks in advance for your help.

Upvotes: 1

Views: 990

Answers (1)

Martin Brandl
Martin Brandl

Reputation: 58931

Just iterate over each row, retrieve the Invoices, split it by ; and create a new PSCustomObject for each using the current values:

$x = Get-Content 'Path_to_your_csv'
$y = $x | ConvertFrom-Csv -Delimiter ' '
$y | Foreach {
    $current = $_
    $current.Invoices -split ';' | foreach  {
        [PSCUstomObject]@{
            ID = $current.ID
            Name  = $current.Name 
            Invoices  = $_
            Member  = $current.Member
            Since  = $current.Since
        }
    }
} | ConvertTo-Csv -NoTypeInformation -Delimiter ' ' | % {$_ -replace '"',''}

Output:

ID Name Invoices Member Since
1 John A123 1999 
1 John A234 1999 
1 John C316 1999 
2 Dave B219 2010 
2 Dave C216 2010 
3 Jim D213 2003 
3 Jim E117 2003 
3 Jim G214 2003

Note: You probably want to adopt the delimiter (e. g. using Tabs)...

Upvotes: 1

Related Questions