CODEWITHSUNDEEP
powershelltext
Mary
Mary

Reputation: 73

Split csv file by columns using Powershell

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.

Upvotes: 5

Views: 8380

Answers (3)

rverma
rverma

Reputation: 11

 ###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 

}

Upvotes: 1

CB.
CB.

Reputation: 60956

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"  }

Upvotes: 3

vonPryz
vonPryz

Reputation: 24091

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])
}

Upvotes: 0

Related Questions