Forum Discussion
How to split rows into column by specific delimiter
Hello.
Good afternoon! Well, being direct:
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
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?
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) } } $ResultHope that helps.
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?
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.
