Filtering CSV data into multiple separate files

Question

I have a CSV file named file1.csv:

something;AD;sss;Andorra;nothing;type_1;sss
something222;AD;sss222;Andorra;nothing222;type_2;aaa
thing;NL;thing3;Netherlands;thing;type_2;bb
etc;US;etc;United States;etc;type_2;nothing

I want to create separate files for each country. I make greps like that:

grep -e "\;AD\;.*\;Andorra\;" file1.csv > fileAD.csv
grep -e "\;NL\;.*\;Netherlands\;" file1.csv > fileNL.csv
grep -e "\;US\;.*\;United\sStates\;" file1.csv > fileUS.csv

This works, but I have all countries in the world, and I don't want to write these lines for every country. Is there any other solution?

I also have a column with type_1 and type_2. I need to take this into account. After all the files corresponding each country are created, I need to create new files for every country with just type_1 and new files with just type_2.

For example, for Andorra, I need the files:

• fileAD.csv:

  something;AD;sss;Andorra;nothing;type_1;sss
  something222;AD;sss222;Andorra;nothing222;type_2;aaa
  

• fileADtype_1.csv:

  something;AD;sss;Andorra;nothing;type_1;sss
  

• fileADtype_2.csv:

  something222;AD;sss222;Andorra;nothing222;type_2;aaa
  

I think that is ok to look just for the column with the abbreviation, but I wanted the two columns, the one with AD and the one with the full name Andorra for security reasons.

Answer 1

Assuming that the data is simple CSV data, i.e. that no field contains embedded delimiters or newlines:

awk -F ';' '
    {
        print > "file" $2    ".csv"
        print > "file" $2 $6 ".csv"
    }' file1.csv

This prints each line twice, once to the file given by only the second field's value, and once to the file given by the combination of the second and sixth field's value. Each output filename will be prefixed by the string file and suffixed by the string .csv, as per the text in the question.

No validation is done of the values of the two fields used in the filenames.

If you want to incorporate the country name from the fourth field:

awk -F ';' '
    {
        print > "file_" $2 "-" $4        ".csv"
        print > "file_" $2 "-" $4 "_" $6 ".csv"
    }' file1.csv

For the given data, this would create the following files

file_AD-Andorra.csv
file_AD-Andorra_type_1.csv
file_AD-Andorra_type_2.csv
file_NL-Netherlands.csv
file_NL-Netherlands_type_2.csv
file_US-United States.csv
file_US-United States_type_2.csv

The above would work well on a system using GNU awk. Other awk implementations may run into issues with keeping too many files open for writing at once. On such awk implementations, you will have to be smarter and remember to close the files after writing to them. Once a file is closed, one has to remember to print with >> the next time data should be written to the file, or else the file would be truncated.

awk -F ';' '
    function do_print(name) {
        if (seen[name] == 1) print >>name  # append to file
        else                 print  >name  # first write, truncate file
        close(name)
        seen[name] = 1
    }
    {
        do_print("file_" $2 "-" $4        ".csv")
        do_print("file_" $2 "-" $4 "_" $6 ".csv")
    }' file1.csv

This would also make the code work with awk on OpenBSD, with which you can't print > to an expression.

---

Extra (just for fun): Making the awk code output some statistics:

awk -F ';' '
    function do_print(name) {
        if (seen[name] > 0) print >>name  # append to file
        else                print  >name  # first write, truncate file
        close(name)
        seen[name]++
    }
    {
        do_print("file_" $2 "-" $4        ".csv")
        do_print("file_" $2 "-" $4 "_" $6 ".csv")
    }
    END {
        for (name in seen)
            printf "Wrote %d lines to \"%s\"\n", seen[name], name >"/dev/stderr"
    }' file1.csv

This writes some statistics to the error stream at the end of the processing. For the given data:

Wrote 1 lines to "file_NL-Netherlands.csv"
Wrote 1 lines to "file_US-United States_type_2.csv"
Wrote 1 lines to "file_AD-Andorra_type_1.csv"
Wrote 2 lines to "file_AD-Andorra.csv"
Wrote 1 lines to "file_NL-Netherlands_type_2.csv"
Wrote 1 lines to "file_US-United States.csv"
Wrote 1 lines to "file_AD-Andorra_type_2.csv"