powershell - split csv into multiple csv by column

Question

I have .csv like that:

but many more lines of course.. (sorry I have added it like picture because text was not able to be formatted correctly)

and I would like to have 4 .csv files like which contains every column separatelly so in x.csv, I will only have data from first column. in y.csv I will have only data from second column (they are separated by ";"

so far I have this code but I'm not able to even loop through columns (looping through rows works..)

$dataaa = Import-Csv ".\input.csv" -Delimiter ";" | Group ID | %{
    $ID=".\out.csv" -f $_.Name
    $_.Group | export-csv "$CurDir\$ID" -NoType
}

$a=0
echo "$a"

$Results = foreach( $Column in $dataaa ){
$a=$a+1
echo "$a" #echo number of column
#select first column and export into x.csv - have NO idea how to
}

echo "$a"

thank you for your help

AdminOfThings · Accepted Answer · 2020-05-28 16:08:14Z

1

You can do the following, which allows for any potential column names you may have:

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

Select-Object will need a calculated property here because your property names have wildcard characters. The use of the -LiteralPath switch may be necessary on some commands because of the wildcard characters as well.

answered May 28, 2020 at 16:08

AdminOfThings

25.2k4 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ondrej Over a year ago

oh man.. what you have write I would be never able to write - THANK YOU 100000000 times! now I can study that code to understand it :) also I was trying some other code and was thinking that it doesn't work because of file name contains [] but it works in your code so coool.. btw. just currious for the future - original csv has only clean text/numbers and ";" symbols but new csv has every line in "" symbols like "0.2454" and not just 0.2454 - is there any special way to have clean lines without "" symbols or that is just a way how ps works? but thanks a lot man anyway!

Ondrej Over a year ago

I got it ;) need to manually remove characters + a little play due to the special file names $file = ".\PositionX ``[mm``].csv" Get-Content $file | Foreach {$_ -replace '"', ""} | Set-Content ".\oxxx.csv"

Farbkreis · Accepted Answer · 2020-05-28 14:43:52Z

0

Its should be straight forward:

1.) Import the data with import-csv into a variable e.g. $tmp (dont forget to set the delimter correctly

2.) Select the property with $tmp | Select-Object -Property Position x...

3.) Export the selected data with export csv (and maybe the parameter notypeinformation)

answered May 28, 2020 at 14:43

Farbkreis

6444 silver badges12 bronze badges

Collectives™ on Stack Overflow

powershell - split csv into multiple csv by column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related