How to split a CSV file into multiple files based on column value

Question

I have CSV file which could look like this:

name1;1;11880
name2;1;260.483
name3;1;3355.82
name4;1;4179.48
name1;2;10740.4
name2;2;1868.69
name3;2;341.375
name4;2;4783.9

there could more or less rows and I need to split it into multiple .dat files each containing rows with the same value of the second column of this file. (Then I will make bar chart for each .dat file) For this case it should be two files:

data1.dat 
name1;1;11880
name2;1;260.483
name3;1;3355.82
name4;1;4179.48

data2.dat
name1;2;10740.4
name2;2;1868.69
name3;2;341.375
name4;2;4783.9

Is there any simple way of doing it with bash?

score 13 · Accepted Answer · edited Sep 29 '17 at 12:48

13

You can use awk to generate a file containing only a particular value of the second column:

awk -F ';' '($2==1){print}' data.dat > data1.dat

Just change the value in the $2== condition.

Or, if you want to do this automatically, just use:

awk -F ';' '{print > ("data"$2".dat")}' data.dat

which will output to files containing the value of the second column in the name.

edited Sep 29 '17 at 12:48

Benjamin Bannier

55,163
11
60
80

answered Jun 17 '15 at 19:16

Andrzej Pronobis

33,828
17
76
92

is it possible to print rounded values instead of (e.g. 260 instead of 260.483)? – user3616643 Jun 17 '15 at 19:43
You can truncate the values using the int() function, `awk -F ';' '{print > "data"int($2)".dat"}' data.dat` – Andrzej Pronobis Jun 17 '15 at 19:45
1

and of course you can print only selected columns (and truncate them) e.g. using `print int($3) > ....` – Andrzej Pronobis Jun 17 '15 at 19:52
1

I get a syntax error using the second form, I needed to write it like this: `awk -F ',' '{print > ("foo" $2 ".csv") }'` - note the extra paranthesis. – Mihai Târnovan Jul 26 '17 at 12:14

score 2 · Answer 2 · answered Jun 17 '15 at 19:19

2

Try this:

while IFS=";" read -r a b c; do echo "$a;$b;$c" >> data${b}.dat; done <file

answered Jun 17 '15 at 19:19

Cyrus

84,225
14
89
153

Any hint on how'd one go about doing it for a combination of fields? Let's say I want to the same operation over second and third column, instead of just the second column? – khan Jun 14 '18 at 17:03
@khan: I suggest to [start a new question](https://stackoverflow.com/questions/ask). – Cyrus Jun 14 '18 at 17:32

How to split a CSV file into multiple files based on column value

2 Answers2

Linked

Related