CODEWITHSUNDEEP
powershellcsv
microset
microset

Reputation: 408

How to split single column (Have multiple value) into multi value through powershell

I have CSV File , that contains with multiple value in single column , I wanna split every value to next columns. Please find below attached Imageenter image description here

I am using below code but no any luck around:

$CSVpath1 = "G:\Test.csv"
$csv = Import-Csv -Path $CSVpath1 -Delimiter ';'
$p= (Get-Member -InputObject $csv[0] -MemberType Properties).Name
foreach ($property in $p) {
    $csv | Select-Object @{n=$p;e={$_.$p}} |
        Export-Csv -LiteralPath "$p.csv" -NoTypeInformation -Delimiter ';'
}

Please find attached CSV file : Test CSV

Upvotes: 0

Views: 1131

Answers (1)

Steven
Steven

Reputation: 7087

Seems similar to this answer. Similar techniques of mixing string manipulation with the native Csv cmdlets.

$CSVpath = "c:\temp\test1.csv"
$Data    = Get-Content $CSVpath
$Headers = $Data[0] -split ','

$Data = ($Data | Select-Object -Skip 1) -replace '\"|,'    
    
$Data | ConvertFrom-Csv -Header $Headers -Delimiter ";"

Bring in the file, use the first line to determine the headers. Then convert the remaining lines from CSV to PSObjects using the ConvertFrom-Csv cmdlet with the -Headers & -Delimiter params. Also replacing what looks like superfluous quoting.

That will result in objects that look like:

H1 h2 H3 H4  H5 H6 H7                 H8 H9 H10
-- -- -- --  -- -- --                 -- -- ---
A  B  C  D   E  F  G,,,,,,,,,,,,,,,,,
H  I  J  K   L  M  N                  O  P  Q
A  B  C  D   E  F  G,,,,,,,,H         I  J  K
10 a  C  D,H I  J  K                  L  M  N

However, it's unclear what's to be done with the commas. I would suggest the data be cleaned up beforehand.

Replacing the commas as in the previous answer and also overwriting the original file:

$CSVpath = "c:\temp\test1.csv"
$Data    = Get-Content $CSVpath
$Headers = $Data[0] -split ','

$Data = ($Data | Select-Object -Skip 1) -replace '\"|,'

$Data | ConvertFrom-Csv -Header $Headers -Delimiter ";" | 
Export-Csv -Path $CSVpath -Delimiter ";" -NoTypeInformation

Withstanding the export this should look like:

H1 h2 H3 H4 H5 H6 H7 H8 H9 H10
-- -- -- -- -- -- -- -- -- ---
A  B  C  D  E  F  G
H  I  J  K  L  M  N  O  P  Q
A  B  C  D  E  F  GH I  J  K
10 a  C  DH I  J  K  L  M  N

Note: Both tables were output using Format-Table which by default truncates some of the columns. Use Format-Table * -AutoSize to see the full table.

All can be shortened down to:

$CSVpath = "c:\temp\test1.csv"
$Data    = Get-Content $CSVpath
$Headers = $Data[0] -Split ','

($Data | Select-Object -Skip 1) -replace '\"|,' |
ConvertFrom-Csv -Header $Headers -Delimiter ";" | 
Export-Csv -Path $CSVpath -Delimiter ";" -NoTypeInformation

# To trim superfluous ";" belonging to emty fields.
(Get-Content $CSVpath).TrimEnd(';') | Set-Content $CSVpath

Note: For a true Csv file it's unnecessary to remove trailing ;. They are natural representations of properties that don't have a value. At any rate, and per request, I added some code to remove them.

Upvotes: 1

Related Questions