How to split rows into column by specific delimiter

[tcboeira]

Hello.
Good afternoon! Well, being direct:

The question of the output of the ";" to "," I solved it...

I have a routine that generates a CSV file, and it looks like this:

A B C D E F G
1,2,3,4,5a 5b 5c 5d,6,7

I would like it to look like this:
A B C D E F G
1,2,3,4,5a,6,7
1,2,3,4,5b,6,7
1,2,3,4,5c,6,7
1,2,3,4,5d,6,7

But the fundamental issue is that I need to preserve the contents of the other columns, replicating them to the rows...

As in the situation... While column E is 5a, 5b, 5c... Columns A, B, C and D must repeat their contents...

To achieve this scenario, I have opened it in Excel and done the "data transformation", asking it to divide the column by the space delimiter.

This operation, due to manual intervention, ends up taking longer than it should...

Via Powershell, how could I do this?

[#2]

Hello @tcboeira,

I am sure there are more concise and elegant ways to do it, but here is one of the options that also works:

$Data=Import-Csv 'C:\input.csv'
$ColumnName="E"
$Result=New-Object System.Collections.Generic.List[PsObject]
foreach($l in $data){
    $Items=$l.$ColumnName -split ' '
    $l.psobject.Properties.Remove($ColumnName)
    foreach($i in $Items){
        $ht=[ordered]@{}
        $l.psobject.properties | Foreach { $ht[$_.Name] = $_.Value }
        $ht=$ht+@{$ColumnName=$i}
        $Result.Add($(New-Object -TypeName psobject -Property $ht))
        $l.psobject.Properties.Remove($ColumnName)
    }
}
$Result

Hope that helps.

[#3]

Hello,

First of all, thanks for the reply and sorry for the delay in getting back to you...

I think I failed to exemplify correctly...

I extract some data, via CSV file, which returns me like this:

COL1	COL2	COL3	COL4
11	12	13	14
22	22	23 24 25 26	27
33	32	33	34

And I wish it could look like this:

COL1	COL2	COL3	COL4
11	12	13	14
22	22	23	27
22	22	24	27
22	22	25	27
22	22	26	27
33	32	33	34

I even tried something using the example...

Is that right? Is it for this purpose?

@AndySvints 

[#4]

Hello @tcboeira,

Yes, the code provided is exactly for the use case that you are describing.

Here is the execution of it on your data set:

One caveat is that the order of the columns will be messed up.  Column in question will be all the way in the end.

Hope that helps.