Split csv file by columns using Powershell

Question

I am a novice at powershell but this looks to be the best tool to do this task. have a csv file that looks like this:

Date,mary1,mary2,maryr3,mary4
9/01/2011 1:00,1.39,3.43,3.29,1.83
9/01/2011 3:00,0,0,0,0
9/01/2011 4:00,0,0,0,0
9/01/2011 5:00,0,0,0,0
9/01/2011 6:00,1.91,0.07,0.09,0.09
9/01/2011 7:00,5.57,2.01,2.11,3.4
9/01/2011 8:00,0.53,0.41,0.35,2.01
9/01/2011 9:00,4.72,0.15,0.21,3.15
9/01/2011 10:00,0.21,0,0.49,0.72
9/01/2011 11:00,2.44,0.77,2.06,1.7
9/01/2011 12:00,0,4.91,5.21,1.98

This is comma delimited. I am trying to split this into multiple text files containing one column, based on the header column.

In reality I will have multiple columns, so I would like to have a more generic code to deal with more rows and columns.

Any thoughts on that? I am able to use the split function and get individual columns out, but my knowledge of powershell yet does not allow me to do the multiple extraction.

Any help would be greatly appreciated.

CB. · Accepted Answer · 2012-11-29 06:42:13Z

3

try:

$a = Import-Csv .\myfile.csv
$b = $a[0] | Get-Member | ? { $_.membertype -eq 'noteproperty'} | select name
$b | % { $a | ft -Property $_.name | out-file "$($_.name).txt"  }

answered Nov 29, 2012 at 6:42

CB.

61.3k9 gold badges171 silver badges165 bronze badges

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

Comments

DanielBarbarian · Accepted Answer · 2017-02-20 07:50:10Z

1

 ###import csv file with value

$ps1 = Import-Csv .\testps.csv
  foreach($p in $ps1){

## value which will be seprated

$t = $p.test 
#####split delimiter is space you can select your own like , / etc.
$t1 =  $t.Split(" ")
 ##########first value
  $test1 = $t1[0]
######second value
  $test2 = $t1[1]
   #####Adding value in csv file test1 and test2
   echo $p | Select-Object *,@{n='test1';e={"$test1"}},@{n='test2';e=    {"$test2"}}|Export-Csv -Append  testdone.csv 

}

edited Feb 20, 2017 at 7:50

DanielBarbarian

5,39312 gold badges37 silver badges45 bronze badges

answered Feb 20, 2017 at 7:25

rverma

111 bronze badge

Comments

vonPryz · Accepted Answer · 2012-11-29 06:18:47Z

0

Look up Import-CSV or process the file row by row. To read the file row by row, use the .Net Framework. Split each row on comma and add desired columns to output file based on array index. Caveat for row-wise processing: if there are commas in the source data, this will break horribly. Like so,

# Open the source file
$file = [IO.File]::OpenText("somefile.csv")
# Read each line
while($line = $file.ReadLine()) {
    # Split line data on comma
    $l= $line.Split(',')
    # Add columns 0 and 2 other file 1
    add-content "otherfile1.csv" $("{0},{1}" -f $l[0], $l[2])
    # Add columns 1, 3  and 5 other file 2
    add-content "otherfile2.csv" $("{0},{1},{2}" -f $l[1], $l[3], $l[5])
}

answered Nov 29, 2012 at 6:18

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

2 Comments

Mary Over a year ago

Thanks a lot, I will try this now and get back to you.

Mary Over a year ago

Thanks a lot, this works very well for splitting this into two files, but any idea how I loop it to extract one column at a time and give me a total of 5 (or n) files?

Collectives™ on Stack Overflow

Split csv file by columns using Powershell

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related