-1

I have csv file namely list.csv as shown below,

Pcissicola19,cissicola39,12xbauhiniae
BGDHLHFA_02833,DGDFDEGP_00879,POPGJMOL_04119
BGDHLHFA_01427,DGDFDEGP_03106,POPGJMOL_01558
BGDHLHFA_01618,DGDFDEGP_02529,POPGJMOL_04348
BGDHLHFA_01349,DGDFDEGP_02811,POPGJMOL_04175
BGDHLHFA_01734,DGDFDEGP_04039,POPGJMOL_04234
BGDHLHFA_00509,DGDFDEGP_02546,POPGJMOL_00085
BGDHLHFA_04577,DGDFDEGP_04242,POPGJMOL_00124

I need to print all the column (except first field of each column) separately into a new file named by the first field of each column. The expected output is as follows,

Pcissicola19.txt

BGDHLHFA_02833
BGDHLHFA_01427
BGDHLHFA_01618
BGDHLHFA_01349
BGDHLHFA_01734
BGDHLHFA_00509
BGDHLHFA_04577

cissicola39.txt

DGDFDEGP_00879
DGDFDEGP_03106
DGDFDEGP_02529
DGDFDEGP_02811
DGDFDEGP_04039
DGDFDEGP_02546
DGDFDEGP_04242

12xbauhiniae.txt

POPGJMOL_04119
POPGJMOL_01558
POPGJMOL_04348
POPGJMOL_04175
POPGJMOL_04234
POPGJMOL_00085
POPGJMOL_00124

I can print each column by using following command awk -F "," '{print $1}' list.csv , but it is not serving my purpose in terms of saving file based on the first field and removing the first field in all the new files. Please help me to automate the process. Thank you in advance.

-1
  • Can use a combination of bash with awk:

    for n in 1 2 3; do awk -F "," "{if (NR!=1) {print \$$n}}" list.scv > $(awk -F "," "{if (NR==1) {print \$$n}}" list.scv) ; done
    
  • Example: enter image description here

2
  • 1
    this will fail if header fields were contain whitespaces; also you will need to first know how many columns your input file has. also in case header column were same for two or more columns only the last column having that same header will be written to file and old columns will be truncated; – αғsнιη Feb 18 at 11:25
  • 1
    This has several issues in addition to those raised by @αғsнιη including having to call awk 6 times (twice per column), puting the condition inside the action part of each awk script instead of in the condition part, and letting the value of a shell variable expand to become part of the awk script (see stackoverflow.com/questions/19075671/… for how to really do that). It's simply the wrong approach to solving this problem. – Ed Morton Feb 18 at 16:30
3

With GNU awk to manage large numbers of open files if you have more than about 15 columns (or any awk if fewer than that):

$ cat tst.awk
BEGIN { FS="," }
NR==1 {
    for (i=1; i<=NF; i++) {
        out[i] = $i ".txt"
    }
    next
}
{
    for (i=1; i<=NF; i++) {
        print $i > out[i]
    }
}

$ awk -f tst.awk list.csv

$ head *.txt
==> 12xbauhiniae.txt <==
POPGJMOL_04119
POPGJMOL_01558
POPGJMOL_04348
POPGJMOL_04175
POPGJMOL_04234
POPGJMOL_00085
POPGJMOL_00124

==> Pcissicola19.txt <==
BGDHLHFA_02833
BGDHLHFA_01427
BGDHLHFA_01618
BGDHLHFA_01349
BGDHLHFA_01734
BGDHLHFA_00509
BGDHLHFA_04577

==> cissicola39.txt <==
DGDFDEGP_00879
DGDFDEGP_03106
DGDFDEGP_02529
DGDFDEGP_02811
DGDFDEGP_04039
DGDFDEGP_02546
DGDFDEGP_04242

otherwise with any awk for any number of columns:

$ cat tst.awk
BEGIN { FS="," }
NR==1 {
    for (i=1; i<=NF; i++) {
        out[i] = $i ".txt"
        printf "" > out[i]
        close(out[i])
    }
    next
}
{
    for (i=1; i<=NF; i++) {
        print $i >> out[i]
        close(out[i])
    }
}
3
awk -F, 'NR==1{ split($0, tmp, ","); next }
              { for(col=1; col<=NF; col++){ print $col >>tmp[col]; close(tmp[col])} }' infile

In NR==1{ split($0, tmp, ","); next }; where NR in awk represent the total Number of Records read/seen so far; so for the first line NR would be 1 and we are checking it with NR==1, if it's first line then its following block will be executed and we do split() that line into pieces separated by a comma , and store the pieces in array tmp. and read next line;

In the second block we loop over the Number of Fields in the current input record and print (append) the corresponding column by $col into the related fileName from the tmp array where the key is same with that column number.

0
0

Or with bash and cut and tr

#!/bin/bash

id=0
while read filename; do
  id=$((id+1))
  tail -n +2 list | cut -d"," -f $id >> "$filename"
done < <(head -1 list.csv | tr "," "\n")
0

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.