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, Age开发者_开发知识库Group2, 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.)

---

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

---

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