3

I have a test1.csv file with two columns

group,email
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]

My goal is to create separate text files depending on the value of the first column.

For example:

the first file named [email protected] and containing

[email protected]
[email protected]
[email protected]
[email protected]

the first file named [email protected] and containing

[email protected]
[email protected]

and so on.

I have this awk command

awk -F"," 'NR==1{header=$0}NR>2&&!a[$1]++{print header > (""$1"")}NR>2{print > (""$1"")}' test1.csv

but the result is not really what I'm looking for, although the naming of the files is correct:

group,email
[email protected],[email protected]
[email protected],[email protected]
[email protected],[email protected]

I'm also getting an 'Awk: too many open files' whenever the .csv file is too big

Any help is appreciated. Also looking into different languages such as sed or grep

3
  • 1
    Please edit your question with this information: Is the input file sorted, as is the example you provide? Do you want the headers in the output files too? If yes, add them to the expected output. (I suspect yes because in your attempt you really seem to be trying to include the header, but I missed it in my first parse of the question.)
    – Quasímodo
    Commented Jun 4, 2020 at 21:58
  • Ciao Giuseppe, I have added a mlr based solution unix.stackexchange.com/a/591041/195582
    – aborruso
    Commented Jun 5, 2020 at 9:58
  • 1
    Should all output files start with the header line or none of them or what?
    – Ed Morton
    Commented Jun 5, 2020 at 19:11

7 Answers 7

1

This is all you need:

awk -F ',' 'NR<2{next} p1!=$1&&p1{close(p1)} {p1=$1;print $2>p1}' file
  • NR<2{next}: Skip header.
  • p1!=$1&&p1{close(p1)}: When the 1st field of previous line (p1) is different from the 1st field of the current line, the previous output file is closed, so that you don't get "too many open files" (unless no file was opened yet and p1 is unset).
  • {p1=$1;print $2>p1}: Put 1st field in p1 variable and print 2nd field to a file with the name of the 1st field.

Notice that the above awk assumes that lines with same first fields are grouped together in file, as the provided sample indicates. If this is not the case, a straightforward solution is to provide a sorted input to awk, explicitly skipping the header (since the header would not be in the first line anymore):

sort file | awk -F ',' '/^group,email$/{next} p1!=$1&&p1{close(p1)} {p1=$1;print $2>p1}'
3
  • You need to sort first if you close. If the group appears again then opening to write again will erase any previous content.
    – bu5hman
    Commented Jun 4, 2020 at 21:07
  • @bu5hman In my answer I considered that OP's file sample being sorted as not accidental. But, yes, we know that sometimes new users miss those important details when providing a minimal, reproducible example. Thank you for your remark, I'm adding that assumption in the answer itself.
    – Quasímodo
    Commented Jun 4, 2020 at 21:40
  • You could tail -n +2 file | sort | to lose the header and avoid the need to test every line.
    – bu5hman
    Commented Jun 5, 2020 at 8:52
1

You can awk to redirect $2 straight to the filename given in $1 (domain names should be whitespace safe)

awk -F, 'NR>1{print $2 > $1}' file

Though this skips the header and leaves files open. You can fix this by testing if this is a new file ++h[$1]==1 to write > a header, use append >> for $2 and then close($1)

awk -F, '
  NR==1{header=$0}
  NR>1{
    if(++h[$1]==1)print header > $1; 
    print $2 >> $1; close ($1)
}' file

And to avoid duplicates just test on ++f[$0]==1before appending..

awk -F, '
  NR==1{header=$0}
  NR>1&&++f[$0]==1{
    if(++h[$1]==1)print header > $1; 
    print $2 >> $1; close ($1)
}' file
head *.com

==> [email protected] <==
group,email
[email protected]
[email protected]
[email protected]
[email protected]

==> [email protected] <==
group,email
[email protected]
[email protected]

==> [email protected] <==
group,email
[email protected]
[email protected]
[email protected]

If your file is wholly or partially sorted then you can avoid blindly opening and closing at every line by

awk -F, '
  NR==1{header=$0}
  NR>1&&++f[$0]==1{
    if ($1 != old) close(old);
    if(++h[$1]==1)print header > $1; 
    print $2 >> $1; old=$1
}' file

Because this uses $2 >> it doesn't matter if there is a second chunk of $1 in your data file, it won't be erased by a fresh >

2
  • the OP's sample output files do not contain a header, so your solution shouldn't be needing one. For closing files on exit, this may be simpler awk -F, 'NR>1{a[$1];print $2 > $1};END{for (k in a) close(k)}' file
    – iruvar
    Commented Jun 5, 2020 at 4:44
  • OP code shows that they want a header. As far as your one liner goes it still keeps all the file handles open until the end. Anyway, am sure OP can make up their own mind as to what suits.
    – bu5hman
    Commented Jun 5, 2020 at 8:02
1

Using Miller (https://github.com/johnkerl/miller) is simply

mlr --csv put -q 'tee > $group, $*' ./input.csv
mlr -I --c2n cut -f email ./group*@*

It's not awk, but I think it could be useful to you

0

you can make a unique list of first field and store in a variable for example mygroups

mygroups=$(awk -F ',' '{print $1}' test.csv | sort | uniq)

Then you can match column 1 with variable in each line

for i in $(echo $mygroups); do awk -F ',' -v val="$i" '$1==val {print $2}' test.csv >> "$i" ;done
0
$ awk '{print (NR>1),$0}' file | sort -k1,1n -k2 | cut -d' ' -f2- |
    awk -F, '
        NR==1 { hdr=$0; next }
        $1 != out { close(out); out=$1; print hdr > out }
        { print $2 " > " out }
    '

The above will work using any awk for practically any size input file with the input lines in any order and it will be fast.

Change print $2 " > " out to print $2 > out once done testing to actually generate the output files.

0

Using GNU version of sed and writing in its extended mode we run the first pass over the csv input and from it generate the necessary series of sed code. In the second pass we apply this code on the csv and get our required .com filest The deduplication is handled within the sed first pass itself.

sed -Ee '
  1s|.*|1b|
  1! s|(.*),.*|/^\1,/ s///w \1|
  G
  /^([^\n]*)\n(.*\n)?\1(\n|$)/d
  P;h;d
' test1.csv | sed -nf - test1.csv
head *.com
0

command

for i in `awk -F "," '{print $1}' p.txt| awk '{if(!seen[$1]++){print $1}}'`; do awk -v i="$i" -F "," '$1==i {print $2}' p.txt > $i.txt; done



output

$ cat [email protected] 
[email protected]
[email protected]
[email protected]
[email protected]

cat [email protected] 
[email protected]
[email protected]

$ cat [email protected] 
[email protected]
[email protected]
[email protected]

You must log in to answer this question.

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