Fetching values in CSV file based on column name


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Fetching values in CSV file based on column name
# 8  
Old 11-08-2013
Quote:
Originally Posted by Akshay Hegde
Try by the way myself Akshay not Ajay Smilie

Code:
$ cat Header_list.txt
Field2
Field4

Code:
$ cat file
Field1,Field2,Field3,Field4,Field4
abc ,123 ,xyz ,000 ,pqr
mno ,123 ,dfr ,111 ,bbb

Code:
awk 'NR==FNR{
                Cols=Cols (Cols?"|":"")$1
                next
            }

     FNR==1{
                for (i=1;i<=NF;i++) 
                if (match($i,Cols)) 
                Ar[++n]=i
           }

           {
               for (i=1;i<=n;i++) 
               printf (i<n)? $(Ar[i])  FS : $(Ar[i])
               printf "\n"
            }' FS=","  Header_list.txt file

Resulting
Code:
Field2 ,Field4, Field4 
123,  000,  pqr 
123,  111,  bbb

Smilie Akshay :
The above code displays entire column values mentioned in the Headerlist.
But it should output the values based on certain field value.
Say I need to extract Field2 value where Field4=pqr.
The output should be in the following manner.
Code:
Field4, Field2
pqr,123

---------- Post updated at 01:36 AM ---------- Previous update was at 01:32 AM ----------

Quote:
Originally Posted by CarloM
Something like:
Code:
awk '
NR==1 {
   for (i=1;i<=NF;i++) {
      if ($i==SEARCHHDR) {
         srchfld=i;
      }
      if ($i==OUTHDR) {
         outfld=i;
      }
   }

   print $srchfld OFS $outfld
}

NR>1 && $srchfld ~ SEARCHVAL {
   print $srchfld OFS $outfld
}
' SEARCHHDR="Field2" SEARCHVAL="123" OUTHDR="Field4" FS=, OFS=, file

Hi CarloM,

The above code is filtering records based on Field2 correctly . But its displaying all fields instead of displaying only Field4 .
Can u please help me.
# 9  
Old 11-08-2013
Quote:
Originally Posted by bharathbangalor
Thanks for the reply Ajay.

But here I don't fetch values based on column position ($2==123). Instead i want to fetch value by the header name .
what is the meaning of this ??? you posted in #3 Smilie

Use this code when you post next time be specific about your requirement and make sure that others will understand your requirement.

Code:
$ cat file
Field1,Field2,Field3,Field4,Field4
abc ,123 ,xyz ,000 ,pqr
mno ,123 ,dfr ,111 ,bbb

Code:
awk '
                   NR==1{
                                 for (i=1;i<=NF;i++)
                                   {
                                     s=(match(tolower($i),tolower(Header_to_Search)))?i:s
                                     if(match(tolower($i),tolower(Column_to_print)))
                                     Ar[++n]=i    
                                   }    
                        }

NR==1 || $s~Search_value{
                                    printf $s FS
                                    for(i=1;i<=n;i++)
                                    printf (i<n)? $(Ar[i])  FS : $(Ar[i])
                                    printf "\n"         
                        }

    ' FS="," Header_to_Search="Field1" Search_value="abc" Column_to_print="Field2|Field3" file

Resulting
Code:
Field1,Field2,Field3
abc ,123 ,xyz


Last edited by Akshay Hegde; 11-08-2013 at 05:04 AM..
This User Gave Thanks to Akshay Hegde For This Post:
# 10  
Old 11-08-2013
Quote:
Originally Posted by bharathbangalor
The above code is filtering records based on Field2 correctly . But its displaying all fields instead of displaying only Field4 .
Can u please help me.
Post the code and the output you get from your sample data, please.
# 11  
Old 11-08-2013
Based on the data in your post#1, try
Code:
awk     'FNR==1         {for (n=1; n<=NF; n++) {if ($n==ID) IDCOL=n; if ($n==RES) RESCOL=n}; print $IDCOL, $RESCOL; next}
         $IDCOL==SRCH   {print $IDCOL, $RESCOL}
        '  RES=Field4  ID=Field2  SRCH=123 FS="," OFS=","  file
Field2,Field4
123   ,pqr
123   ,bbb

This User Gave Thanks to RudiC For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

2. Shell Programming and Scripting

Identify duplicate values at first column in csv file

Input 1,ABCD,no 2,system,yes 3,ABCD,yes 4,XYZ,no 5,XYZ,yes 6,pc,noCode used to find duplicate with regard to 2nd column awk 'NR == 1 {p=$2; next} p == $2 { print "Line" NR "$2 is duplicated"} {p=$2}' FS="," ./input.csv Now is there a wise way to de-duplicate the entire line (remove... (4 Replies)
Discussion started by: deadyetagain
4 Replies

3. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

4. Shell Programming and Scripting

Remove the values from a certain column without deleting the Column name in a .CSV file

(14 Replies)
Discussion started by: dhruuv369
14 Replies

5. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

6. Shell Programming and Scripting

Fetching columns from .csv file except last column

Hi, i have below list of files so i just want the name of the files in one parameter and not the timestamp. i want only GIDW_Dy_Tm_Seg_Sls_legacy_PL_0_0_ in variable of all files. GIDW_Dy_Tm_Seg_Sls_legacy_PL_0_0_20131001101800.csv GIDW_Dly_Sls_legacy_RO_0_0_20131001172001.csv ... (9 Replies)
Discussion started by: renuk
9 Replies

7. Shell Programming and Scripting

Script for extracting data from csv file based on column values.

Hi all, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.... (3 Replies)
Discussion started by: Vivekit82
3 Replies

8. Shell Programming and Scripting

Check to identify duplicate values at first column in csv file

Hello experts, I have a requirement where I have to implement two checks on a csv file: 1. Check to see if the value in first column is duplicate, if any value is duplicate script should exit. 2. Check to verify if the value at second column is between "yes" or "no", if it is anything else... (4 Replies)
Discussion started by: avikaljain
4 Replies

9. Shell Programming and Scripting

Pick the column value based on another column from .csv file

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Main imp point, in my .csv file, third column is having price value with comma (1,20,300), it has... (2 Replies)
Discussion started by: Ganesh L
2 Replies
Login or Register to Ask a Question