2

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
  • To clarify, you're saying that you want the file split into pieces of what size? And that any of column two's particular values should exist in only one file? – Jeff Schaller Nov 9 '16 at 14:17
  • 2
    With that many records, have you considered using a database instead of multiple files? – mattdm Nov 9 '16 at 14:27
  • I want to split the file by approx. 50 million records which would give me approx. 7 files, but I need the records in each file not to bleed into next file by the value of the second column. I need to run analytics program(using R) for each file and all records by the value of the second column needs to be contained in that file. – Sal Esposito Nov 9 '16 at 15:31
4

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
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.