CODEWITHSUNDEEP
excelbashcsvsplitkettle
dani
dani

Reputation: 5000

Split this csv/xls into separate files based on two columns?

I have a 35 MB Excel file with these columns:

Index, Name, Year, AgeGroup1, AgeGroup2, AgeGroup3 [...]
1, Sweden, 1950, 20, 25, 27
2, Norway, 1950, 22, 27, 28
2, Sweden, 1951, 24, 24, 22

I'd like to split the file into several csv files based on the "Name" column (and preferably also name the files based on the value in this column).
I'd also like the files to be sorted by "Year" (but this could of course be done in Excel beforehand.)

A bash script or Kettle/Pentaho solution would be much appreciated. (Alternatives are also welcome.)

Upvotes: 1

Views: 1654

Answers (2)

Kent
Kent

Reputation: 195029

i just used the example data you pasted there.

awk oneliner can do it for you:

 awk -F, 'NR==1{title=$0;next} { print >> ($2".csv");colse}' yourCSV

see below test:

kent$  l  
total 4.0K
-rw-r--r-- 1 kent kent 136 2011-10-05 11:04 t

kent$  cat t
Index, Name, Year, AgeGroup1, AgeGroup2, AgeGroup3
1, Sweden, 1950, 20, 25, 27
2, Norway, 1950, 22, 27, 28
2, Sweden, 1951, 24, 24, 22


kent$  awk -F, 'NR==1{title=$0;next} { print >> $2".csv"}' t

kent$  head *.csv
==>  Norway.csv <==
2, Norway, 1950, 22, 27, 28

==>  Sweden.csv <==
1, Sweden, 1950, 20, 25, 27
2, Sweden, 1951, 24, 24, 22

update

 awk -F, 'NR>1{ fname=$2".csv"; print >>(fname); close(fname);}' yourCsv

Upvotes: 1

Dimitre Radoulov
Dimitre Radoulov

Reputation: 27990

If awk is acceptable, export to csv and run the following command:

awk -F, '{
  print > ($2 ".csv") 
  }' OFS=, infile.csv

Report back if you:

  1. Want to preserve the header line in all files.
  2. Get errors because of too many open files.

To sort the file outside of Excel:

sort -t, -k3,3n infile.csv | awk ...

Edit: This will take care of most of the issues (except for the concurrently open files):

{
  read
  printf '%s\n' "$REPLY"
  sort -bt, -k3,3
  } < infile | 
    awk -F', *' 'NR == 1 {
      h = $0; next
      }
    {
      f = $2 ".csv"
      if (!_[f]++) 
        print h > f 
      print > f 
      }' OFS=', '

If you hit the "too many open files" limit of your awk implementation, you could use something like this:

awk -F, 'NR > 1 { 
  if (f) close (f)
  f = $2 ".csv"
  print > f
  }' OFS=, infile

Upvotes: 0

Related Questions