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?

Benjamin Bannier · Accepted Answer · 2017-09-29 12:48:22Z

14

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, 2017 at 12:48

Benjamin Bannier

59.5k12 gold badges63 silver badges85 bronze badges

answered Jun 17, 2015 at 19:16

Andrzej Pronobis

36.6k18 gold badges83 silver badges92 bronze badges

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

4 Comments

user3616643 Over a year ago

is it possible to print rounded values instead of (e.g. 260 instead of 260.483)?

Andrzej Pronobis Over a year ago

You can truncate the values using the int() function, awk -F ';' '{print > "data"int($2)".dat"}' data.dat

Andrzej Pronobis Over a year ago

and of course you can print only selected columns (and truncate them) e.g. using print int($3) > ....

Mihai Târnovan Over a year ago

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.

Cyrus · Accepted Answer · 2015-06-17 19:19:54Z

2

Try this:

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

answered Jun 17, 2015 at 19:19

Cyrus

90.2k15 gold badges112 silver badges173 bronze badges

2 Comments

saifuddin778 Over a year ago

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?

Cyrus Over a year ago

@khan: I suggest to start a new question.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related