How do I split a large file into multiple smaller ones based on the value in a text column?

Question

I have a large file that I need to split into workable pieces. (350 million records) The key is the second column value cannot overflow into the next file.

Reading and writing take way to long, split command does not work. anything else I can do?

example file with 10 records to be split into 3 output files:

aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,23,xxx
aa,23,xxx
aa,23,xxx
aa,23,xxx
aa,24,xxx

output1:

aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,22,xxx

output2:

aa,23,xxx
aa,23,xxx
aa,23,xxx
aa,23,xxx

output3:

aa,24,xxx

Answer 1

With awk:

awk -F, '$2 != ref { i++; ref = $2 } { print $0 >"output" i }' input

will split input according to the second column, into files output1, output2...

If you want to limit the number of lines per output file:

awk -F, '$2 != ref { i++; ref = $2; lines = 0 } lines >= 1000 { i++; lines = 0 } { print $0 >"output" i; lines++ }' input

will produce output files containing at most 1000 lines, respecting the constraint on the second column.

Here's another variant, which splits at the next change in the second column after a given limit has been reached (1000 lines in this example, you'd use 50000000 presumably):

awk -F, 'BEGIN { change = 1 } change && $2 != ref { i++; ref = $2; change = 0; lines = 0 } lines >= 1000 { change = 1 } { print $0 >"output" i; lines++; ref = $2 }' input

Answer 2

Using Miller:

$ mlr --nidx --fs comma put -q 'tee > $2 . ".dat", $*' ten.dat

$ cat 22.dat
aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,22,xxx
aa,22,xxx

$ cat 23.dat
aa,23,xxx
aa,23,xxx
aa,23,xxx
aa,23,xxx

$ cat 24.dat
aa,24,xxx