3

I have a test1.csv file with two columns

group,email
group1@email.com,user1@email.com
group1@email.com,user2@email.com
group1@email.com,user3@email.com
group1@email.com,user4@email.com
group2@email.com,user5@email.com
group2@email.com,user6@email.com
group3@email.com,user7@email.com
group3@email.com,user8@email.com
group3@email.com,user9@email.com

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

For example:

the first file named group1@email.com and containing

user1@email.com
user2@email.com
user3@email.com
user4@email.com

the first file named group2@email.com and containing

user5@email.como
user6@email.com

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
group1@email.com,user2@email.com
group1@email.com,user3@email.com
group1@email.com,user4@email.com

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

Improve this question
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 Jun 4 '20 at 21:58
  • Ciao Giuseppe, I have added a mlr based solution unix.stackexchange.com/a/591041/195582 – aborruso Jun 5 '20 at 9:58
  • 1
    Should all output files start with the header line or none of them or what? – Ed Morton Jun 5 '20 at 19:11
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}'
Improve this answer
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 Jun 4 '20 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 Jun 4 '20 at 21:40
  • You could tail -n +2 file | sort | to lose the header and avoid the need to test every line. – bu5hman Jun 5 '20 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

==> group1@email.com <==
group,email
user1@email.com
user2@email.com
user3@email.com
user4@email.com

==> group2@email.com <==
group,email
user5@email.com
user6@email.com

==> group3@email.com <==
group,email
user7@email.com
user8@email.com
user9@email.com

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 >

Improve this answer
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 Jun 5 '20 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 Jun 5 '20 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

Improve this answer
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
Improve this answer
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.

Improve this answer
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
Improve this answer
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 group1@email.com.txt 
user1@email.com
user2@email.com
user3@email.com
user4@email.com

cat group2@email.com.txt 
user5@email.com
user6@email.com

$ cat group3@email.com.txt 
user7@email.com
user8@email.com
user9@email.com
Improve this answer

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.