1

I must work with data from a huge CSV file in a format like this one. The file is very huge (~200MB) and my PC is having difficulties with it, so I would like to split the file in smaller ones easier to cope with. Supposing that the file has a format like this:

NAME,SURNAME,SEX,CITY,AGE RANK
Tom,Brown,M,New York,20-40
Dick,Clarke,M,Seattle,0-20
Katie,Johnson,F,Boston,40-60
Harry,Smith,M,Washington,40-60
Amy,Davies,F,Chicago,20-40
Emily,Adams,F,New York,20-40
...

I would like to split it as follows:

  • separate age ranks
  • each file no longer than given line number, otherwise splitted again

For example:

  • 0-20.1.csv (5000 lines)
  • 0-20.2.csv (5000 lines)
  • 0-20.3.csv (1234 remaining lines)
  • 20-30.1.csv (5000 lines)
  • 20-30.2.csv (4321 remaining lines)
  • ...

I would also like to repeat the first line (header) of the input file at the beginning of each output file and also remove some columns I don't need, but that's not essential. So my ideal output for age rank 20-40 would be (supposing I want to remove NAME and AGE RANK column):

SURNAME,SEX,CITY
Brown,M,New York
Davies,F,Chicago
Adams,F,New York
...

Is there a way to automatically manipulate the file like that? I can use any tool or script, but I would much prefer to avoid proprietary software.

2
  • Would you like a PHP solution?
    – Parto
    Jul 11, 2020 at 10:44
  • @Parto I know know very little about PHP but I'd give it a try.
    – Ntakwetet
    Jul 11, 2020 at 10:56

1 Answer 1

1

It would be helpful if you included a mini-example with enough data for splitting at, say, 5 lines instead of 5000. However, you should be able to use something like this, in Awk:

awk -F, -v nsplit=5000 '
  NR==1 {OFS=FS; hdr=$0; next} 
  (++count[$5] % nsplit == 1) {
    close(fname[$5]); 
    fname[$5] = $5 "." (++ind[$5]) ".csv";
    print hdr > fname[$5]
  } 
  {print > fname[$5]}
' file.csv

Testing with nsplit=2:

$ awk -F, -v nsplit=2 '
  NR==1 {OFS=FS; hdr=$0; next} 
  (++count[$5] % nsplit == 1) {
    close(fname[$5]); 
    fname[$5] = $5 "." (++ind[$5]) ".csv";
    print hdr > fname[$5]
  } 
  {print > fname[$5]}
' file.csv

gives

$ head 0-20.?.csv 20-40.?.csv 40-60.?.csv 
==> 0-20.1.csv <==
NAME,SURNAME,SEX,CITY,AGE RANK
Dick,Clarke,M,Seattle,0-20

==> 20-40.1.csv <==
NAME,SURNAME,SEX,CITY,AGE RANK
Tom,Brown,M,New York,20-40
Amy,Davies,F,Chicago,20-40

==> 20-40.2.csv <==
NAME,SURNAME,SEX,CITY,AGE RANK
Emily,Adams,F,New York,20-40

==> 40-60.1.csv <==
NAME,SURNAME,SEX,CITY,AGE RANK
Katie,Johnson,F,Boston,40-60
Harry,Smith,M,Washington,40-60

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .