I am hoping to create a series of text files each containing a comma separated list of values from column 1 of an input file based on their shared value in column 2 of the input file.

Input file format:

CB-03-01-01     CB-03.1
CB-03-01-02     CB-03.1
CB-03-01-03     CB-03.1
CB-03-02-01     CB-03.2
CB-03-02-02     CB-03.2
CB-03-02-03     CB-03.2
CB-08-01        CB-08
CB-08-02        CB-08
CB-08-03        CB-08

Desired outputs: CB-03.1.txt






Thanks for any help you can provide!

3 Answers 3


The command you can use is:

awk '{a[$2]=a[$2]","$1} END {for(i in a) print substr(a[i],2) >i".txt"}' input_file

a is associative array and substr function remove the leading comma from first assignment of the array element.

As per comment you may need to replace >i".txt" with >(i".txt"); close(i".txt") with some versions of awk and to close open file handlers

  • print ... >i".txt" will give you a syntax error in some awks, it needs to be print >(i".txt") to work in all awks, and not closing the output files as you go will lead to a "too many open" files error in most awks and a significant slowdown in others if you cross some threshold. Storing all of the values in an array will potentially use up a lot of memory unnecessarily.
    – Ed Morton
    Jul 20, 2023 at 13:16
  • @EdMorton, right, usage of memory may become big. But there is always tradeoffs, CPU, memory, disk... Jul 20, 2023 at 13:23
  • 2
    If you change for(i in a) print substr(a[i],2) >i".txt" into for(i in a) { print substr(a[i],2) >(i".txt"); close(i".txt") } it'll solve both the portability and too many open files issues with no tradeoffs other than writing an extra 20 chars or so.
    – Ed Morton
    Jul 20, 2023 at 13:33
  • @EdMorton, about tradeoffs I mean memory usage if many keys for array :) Jul 20, 2023 at 14:05
  • 1
    Yes, that is also a way it could be done but, as you point, out it'd be slow.
    – Ed Morton
    Jul 20, 2023 at 14:31

Using Raku (formerly known as Perl_6)

~$ raku -e 'my %h; for lines() { %h.push: .[1] => .[0] given .split(/ \s+ /) };
            for %h.sort() { 
                spurt( ( .key ~ ".txt" ).IO, $_.value.join(",") ~ "\n", createonly => True);
            };'  file.txt

Above is a solution coded in Raku, a member of the Perl-family of programmming languages. An advantage of Raku is high-level support for Unicode.

Basically the problem as stated is a key/value problem, wherein all key/value pairs are stored in a %h hash. Every time you see a new second column (text) element you create a key, adding the corresponding first column (text) element as value. Since keys in a hash are maintained unique, subsequent values for the same key get pushed (or appended) to the same key.

Instead of taking input off the command line, you can write the code below which has the appropriate path hardcoded inside:

~$ raku -e 'my $in = "/path/to/file.txt".IO; my %h;
            for $in.lines() { %h.push: .[1] => .[0] given .split(/ \s+ /) };
            for %h.sort() { 
                my $out = IO::Spec::Unix.catpath($, $in.dirname, .key) ~ ".txt"; 
                spurt( $out,  $_.value.join(",") ~ "\n", createonly => True) };'

Other options include saving the code immediately above (inside the single quotes) as a script, and calling it at the command line with raku script.raku. Note above, the hardcoded script can be ported to other OSes, simply by changing the IO::Spec call as appropriate (to IO::Spec::Win32, IO::Spec::Cygwin, etc.).



Using any awk (untested):

awk '
    $2 != prev {
        if ( out != "" ) {
            print "" > out
        out = $2 ".txt"
        sep = ""
        prev = $2
        printf "%s%s", sep, $1 > out
        sep = ","
    END {
        if ( out != "" ) {
            print "" > out
' file

The above only has 1 output file open at a time and so can't run into a "too many open files" error (or slowdown) and only stores 1 line at a time in memory so it'll continue to work no matter how large your input file is.

It does assume your input is grouped by the $2 values as in the example you provided but if that's not the case in your real data then just sort it by the 2nd field first, e.g. sort -k2,2 file | awk 'script'.

You must log in to answer this question.

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