How do I extract specific column in multiple csv files?

UNIX for Beginners Q & A
1

file1:

Name,Threshold,Curr Samples,Curr Error%,Curr ART
GETHome,100,21601,0.00%,47
GETregistry,100,21592,0.00%,13
GEThomeLayout,100,30466,0.00%,17

file2:

Name,Threshold,Curr Samples,Curr Error%,Curr ART
GETHome,100,21601,0.00%,33
GETregistry,100,21592,0.00%,22
GETPublish,100,21583,0.00%,110

file3:

Name,Threshold,Curr Samples,Curr Error%,Curr ART
GETHome,100,21601,0.00%,40
GETPublish,100,21583,0.00%,144
GEThomeLayout,100,30466,0.00%,251

similarly i have 21 csv files.i need to extract specific coloumn from all csv files and print in to a csv file.

if i mention Curr ART
example output:

Name,Curr ART,Curr ART,Curr ART,
GETHome,47,33,40
GETregistry,13,22,NA
GEThomeLayout,17,NA,251
GETPublish,NA,110,144

can anyone help me on this..!

2

Try

awk -F, '
NR == 1         {for (i=1; i<=NF; i++) if ($i == SRCH) COL=i
                 HD = $1
                }
FNR == 1        {FCNT++
                 HD = HD OFS SRCH
                 next
                }
! EX[$1]++      {NM[++ROWCNT] = $1
                }
                {T[$1,FCNT] = $COL
                }
END             {print HD
                 for (i=1; i<=ROWCNT; i++)      {TMP = NM
                                                 printf "%s", TMP
                                                 for (j=1; j<=FCNT; j++) printf ",%s", T[TMP,j]?T[TMP,j]:"NA" 
                                                 printf RS
                                                }
                }
' SRCH="Curr ART" OFS=, file[1-3]
Name,Curr ART,Curr ART,Curr ART
GETHome,47,33,40
GETregistry,13,22,NA
GEThomeLayout,17,NA,251
GETPublish,NA,110,144
1 Like
3

Thanks its working for three files:) ..need to check for my 24 files..i will check it and let u know..!

4

Hi Rudic ..
i have 24 files and i have tried giving

' SRCH="Curr ART" OFS=, file[1-24]

it is not working then
it is working for only [1-9]
can u help me for 24 files??

EDITED:
i tried like complete command like file1 file2 file3........file24
the it worked

' SRCH="Curr ART" OFS=, file1 file2 file3 file4............file24
5

In shell "pattern matching",

(cf man bash ). So, [1-24] matches 1, 2, and 4. For your case, you might want to try "brace expansion" (cf man bash ):

echo file{1..24}
file1 file2 file3 file4 file5 file6 file7 file8 file9 file10 ... file23 file24

so, in lieu of explicitly listing each and every file name, have the shell do it for you.

1 Like
6

And, BTW, you might want to consider combining the solutions in this thread with the ones in your other thread?

1 Like
8

Just using standard shell features, one could also try:

' SRCH="Curr ART" OFS=, file[1-9] file1[0-9] file2[0-4]

or, assuming that there aren't any other filexx files where xx are decimal digits:

' SRCH="Curr ART" OFS=, file[1-9] file[12][0-9]

or, if there aren't any other files with names starting with file :

' SRCH="Curr ART" OFS=, file? file??

.