How to split a .CSV file using a key
-
Someone else asked this quesion, I can't remember where to reply to his post. But, since I wrote the script already, wanted to post to this good site. The code is written in biterscripting. (http://www.biterscripting.com) I would like to split a csv file into secondary csv files on a key field. Ex : all.csv (1st column is the key field for splitting action) P1;X;10 P1;Y;5 P2;Z;10 P2;A;5 P3;B;30 P3;C;60 P1.csv P2.csv P3.csv P1;X;10 P2;Z;10 P3;B;30 P1;Y;5 P2;A;5 P3;B;60 Here is the script. Patrick # START OF SCRIPT var str saved_wsep set $saved_wsep = $wsep set $wsep=";" var str output_file var str input, output, row_str, c1, prev_c1 # c1 for column 1 cat "all.csv" >$input var int row, rows set $rows = { len $input } set $row = 1 while ($row <= $rows) do lex -p $row $input >$row_str wex -p "1" $row_str >$c1 if ($c1 <> $prev_c1) do # new output file begins. Is there anything in the previous file. if ($prev_c1 <> "") do echo $output > { echo $prev_c1 ".csv" } done endif set $prev_c1 = $c1 done endif set $output = $output + $row_str + "\n" set $row = $row + 1 done echo $output > { echo $prev_c1 ".csv" } set $wsep = $saved_wsep
-
Someone else asked this quesion, I can't remember where to reply to his post. But, since I wrote the script already, wanted to post to this good site. The code is written in biterscripting. (http://www.biterscripting.com) I would like to split a csv file into secondary csv files on a key field. Ex : all.csv (1st column is the key field for splitting action) P1;X;10 P1;Y;5 P2;Z;10 P2;A;5 P3;B;30 P3;C;60 P1.csv P2.csv P3.csv P1;X;10 P2;Z;10 P3;B;30 P1;Y;5 P2;A;5 P3;B;60 Here is the script. Patrick # START OF SCRIPT var str saved_wsep set $saved_wsep = $wsep set $wsep=";" var str output_file var str input, output, row_str, c1, prev_c1 # c1 for column 1 cat "all.csv" >$input var int row, rows set $rows = { len $input } set $row = 1 while ($row <= $rows) do lex -p $row $input >$row_str wex -p "1" $row_str >$c1 if ($c1 <> $prev_c1) do # new output file begins. Is there anything in the previous file. if ($prev_c1 <> "") do echo $output > { echo $prev_c1 ".csv" } done endif set $prev_c1 = $c1 done endif set $output = $output + $row_str + "\n" set $row = $row + 1 done echo $output > { echo $prev_c1 ".csv" } set $wsep = $saved_wsep