A file data.csv has the following data

1,avocado,mexican green fruit
1,kiwi,green fruit
1,banana,yellow fruit
1,mango,yellow fruit

To organize data into fruit catagories, I've done

awk -F ',' '{print >> ($3 ".csv")}' data.csv

which creates 3 files, mexican green fruit.csv, green fruit.csv, yellow fruit.csv

I want the spaces in the names of these files to be replaced with underscores _

So, the files names should be mexican_green_fruit.csv, green_fruit.csv, yellow_fruit.csv

Need help in this awk one liner to do this

Looking for an awk only answer


3 Answers 3


An awk-only answer (as the OP requested) for GNU awk would be:

awk -F',' '{print > gensub(/[[:space:]]+/,"_","g",$3) ".csv"}' data.csv

An awk-only answer for any POSIX awk if your input is small enough such that you can't exceed the "too many open files" threshold would be:

awk -F',' '{out=$3 ".csv"; gsub(/[[:space:]]+/,"_",out); print > out}' data.csv

An awk-only answer for any POSIX awk if you might exceed the "too many open files" threshold would be:

awk -F',' '{out=$3 ".csv"; gsub(/[[:space:]]+/,"_",out); if (!seen[$3]++) printf "" > out; print >> out; close(out)}' data.csv

but that last would be slow as it's closing and reopening the output file for every write and it assumes you can store every $3 value in memory. You can make it a bit more efficient by only closing the output file if/when it changes:

awk -F',' '$3 != prev {close(out); out=$3 ".csv"; gsub(/[[:space:]]+/,"_",out); if (!seen[$3]++) printf "" > out; prev=$3} {print >> out}' data.csv

If you're OK with an answer that's not awk-only though, then using the DSU (Decorate/Sort/Undecorate) idiom using any POSIX awk, sort, and cut, the following will work efficiently and robustly for any size of input file that sort can handle (and it's designed to use demand paging, etc. to handle extremely large files), and for any number of output files:

$ cat tst.sh
#!/usr/bin/env bash

awk '
    BEGIN{ FS=OFS="," }
    { print $3,NR,$0 }
' "${@:-}" |
sort -t',' -k1,1 -k2,2n |
cut -d',' -f3- |
awk '
    BEGIN{ FS=OFS="," }
    $3 != prev {
        out = $3 ".csv"
        prev = $3
    { print > out }

$ ./tst.sh data.csv

$ head *.csv
==> data.csv <==
1,avocado,mexican green fruit
1,kiwi,green fruit
1,banana,yellow fruit
1,mango,yellow fruit

==> green_fruit.csv <==
1,kiwi,green fruit

==> mexican_green_fruit.csv <==
1,avocado,mexican green fruit

==> yellow_fruit.csv <==
1,banana,yellow fruit
1,mango,yellow fruit

For more info on DSU see https://stackoverflow.com/questions/71691113/how-to-sort-data-based-on-the-value-of-a-column-for-part-multiple-lines-of-a-f/71694367#71694367.

  • Sweet. DSU is also referred to as a "Schwartzian Transform" for anyone interested in such things en.wikipedia.org/wiki/Schwartzian_transform May 28 at 12:13
  • @MarkSetchell Thanks! I know some people use that term but a "Schwartzian Transform" is just an implementation of DSU in perl. DSU was around long before that perl implementation, referring to it in general as a Schwarzian transform is like if you posted an implementation of binary sort in awk and we started calling all binary sorts in all languages a Setchellian transform.
    – Ed Morton
    May 28 at 14:14
  • I wasn't doubting you in the slightest, or arguing or disputing the naming or which came first or is the more generic term. It's just such an elegant technique that I wanted to give other readers the opportunity to read up on it and search for it by sharing an alternative name that might help them identify examples. May 28 at 22:26

Could be done with a function for instance:

awk -F, '
  function csvfile(name) {
    gsub(/[[:space:]]+/, "_", name)
    return name".csv"
  {print >> csvfile($3)}'

Here replacing every sequence of one or more whitespace characters (including space, tab, cr...) with _.

  • @GypsyCosmonaut, I can't reproduce, csvfile() is being passed the 3rd field with $3. If you change to $2, it will write to files named after the 2nd field but you'll still have files for the 3rd column from earlier run when you used $3. May 27 at 11:46
  • My bad.. I made a mistake in modifying it according to my needs.. May 27 at 11:54
  • If you ran that command twice it'd append to the output files instead of overwriting them courtesy of using >> instead of >. That would fail with a "too many open files" error once you get past some threshold with most awks or slow down with GNU awk. You could close the files as you go since you're using >> anyway if you want to avoid the "too many open files" issue or you could switch to > to avoid the appending instead of overwriting issue.
    – Ed Morton
    May 27 at 13:59

(with gnu awk or similar) You could run something along the lines of

awk -F, '{print > gensub(/ /,"_","g",$3)".csv"}' ex.csv
  • gensub is a functional sub -- slightly easy to compose.
  • > is probably better than >> unless you have some csv created before this command is exectuted.
  • we may get into problems if we have millions of different $3 values.

Edit: to cope with new requirements (in fact a new question)

awk -F, '
  NF == 0     {next}
  !seen[$3]++ {print "Quantity, f..., c..." > gensub(/ /,"_","g",$3)".csv"}
              {print                        > gensub(/ /,"_","g",$3)".csv"}  
' ex.csv
  • 2
    You should mention that requires an awk that supports the non-POSIX extension gensub() and would fail with most awks once you get past a threshhold of concurrently open files since you arent closing them as you go. It'd work in GNU awk.
    – Ed Morton
    May 27 at 13:48
  • You mentioned millions of different $3 values. I've seen the "too many open files" error with less than 20 open files.
    – Ed Morton
    May 27 at 13:56
  • @EdMorton in my old laptop linux / awk seq 20000 | awk '{print > "_"$1}' works perfectly... ( but I don't want to try with millions :) )
    – JJoao
    May 27 at 14:02
  • Yes, I know in some (most?) awks on some (most?) platforms you can have thousands of open files but sometimes you can't. Also in some awks you can write print > "_"$1 as you did but that's undefined behavior per POSIX and so in other awks it'll tell you it's a syntax error (which I forgot to comment on first time around!). I'd bet your old laptop is running GU awk given the behavior described. It's all fine, just worth stating such caveats in your answer (or simply saying "it'll work in GNU awk") so no-one trying to use it spends time trying to figure it out if they run into those problems.
    – Ed Morton
    May 27 at 14:06
  • 1
    @EdMorton, in my previous comment, "my old laptop" is in fact an old machine but running a normal recent linux system with gnu-awk. I confirm that some decades ago the "max number of open files" was a real problem. In my answer I added "With Gnu-awk or similar"
    – JJoao
    May 27 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.