Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Extract CSV records using NAWK?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extract CSV records using NAWK?
# 1  
Old 02-11-2010
Question Extract CSV records using NAWK?
Example CSV:

Code:
$ cat myfile
HDR
COL_A,COL_B,COL_C
X,Y,Z
Z,Y,X
...
X,W,Z

In this example, I know that column names are on the second line. I also know that I would like to print lines where COL_A="X" and COL_C="Z". In this simple example, I know that COL_A = $1 and COL_C = $3, and hence the following would suffice:

Code:
$ nawk -F, '{
  if ( $1 == "X" && $3 == "Z" ) {
    print $0
  }
}' myfile
X,Y,Z
X,W,Z

...but what if I want to use the column names in place of field numbers? I know that the following prints field numbers alongside field values for the row containing column names:

Code:
$ nawk -F, 'NR==2 {for (i=1; i<=NF;i++) print $i "=" i; exit}' myfile
COL_A=1
COL_B=2
COL_C=3

...so I was hoping to use a combination of the above to produce the required script. Attempts thus far have failed miserably:

Code:
$ nawk -F, '{NR==2; for (i=1; i<=NF;i++) $i=i} {
  if ( $COL_A == "X" && $COL_C == "Z" ) {
    print $0
  }
}' myfile
nawk: illegal field $()
 input record number 1, file myfile
 source line number 2

Any suggestions? TIA
# 2  
Old 02-11-2010
Hi, this is your input

Code:
COL_A,COL_B,COL_C
X,Y,Z
Z,Y,X
...
X,W,Z

simply what is desired output? i couldnt understand, please post it.
# 3  
Old 02-11-2010
You can do something like:
Code:
awk -v COL_A=1 -v COL_C=3 '
$COL_A == "X" && $COL_C == "X"
' myfile

# 4  
Old 02-12-2010
Quote:
Originally Posted by Franklin52
You can do something like:
Code:
awk -v COL_A=1 -v COL_C=3 '
$COL_A == "X" && $COL_C == "X"
' myfile

Thanks, but I was wondering if the variables can be set programatically from within a single (n)awk script, e.g.

Quote:
Originally Posted by Pseudo Code
Using nawk, take the second line of the file and store each field value as a variable containing the field number. Then, iterate over the whole file, printing lines who match an "IF" statement containing one or more "AND" statements using the previously stored variables.
# 5  
Old 02-12-2010
If the fieldnames are known you can use the code as I provided but if the fieldnames are unknown how would you hardcode the fieldnames in the script?

Or am I missing something?
# 6  
Old 02-12-2010
Quote:
Originally Posted by Franklin52
Or am I missing something?
...not at all; perhaps I just haven't been clear enough. Imagine a user knows the exact field names, but not the field numbers, and there are hundreds of columns. Rather than having to know that "EXAMPLE_COLUMN" is $154, I would like to simply reference $EXAMPLE_COLUMN. Clearly this can be broken down by first listing out all the column names and associated numbers first, but I am simply curious to know if this can be done using n(awk)...!
# 7  
Old 02-12-2010
Something like this? This is an example with one column:
Code:
awk 'NR==2 {
  for(i=1;i<=NF;i++) {
    if($i=="EXAMPLE_COLUMN") }
      EXAMPLE_COLUMN=i
    }
  }
  next
}
$EXAMPLE_COLUMN=="X" 
' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

1. This will insert the records into db table by reading from ta csv file

I have this code with me but the condition is If any of the mandatory columns are null then entire file will be rejected. LOAD DATA infile ' ' #specifies the name of a datafile containing data that you want to load BADFILE ' ' #specifies the name of... (1 Reply)
Discussion started by: raka123
1 Replies

2. UNIX for Beginners Questions & Answers

Filtering records of a csv file based on a value of a column

Hi, I tried filtering the records in a csv file using "awk" command listed below. awk -F"~" '$4 ~ /Active/{print }' inputfile > outputfile The output always has all the entries. The same command worked for different users from one of the forum links. content of file I was... (3 Replies)
Discussion started by: sunilmudikonda
3 Replies

3. Shell Programming and Scripting

Extract records from list

Hi Gents, I have a file 1 like this 1 1000 20 2 2000 30 3 1000 40 5 1000 50 And I have other file 1 like 2 1 I would like to get from the file 1 the complete line which are in file 2, the key to compare is the column 2 then output should be. 2 2000 30. I was trying to get it... (5 Replies)
Discussion started by: jiam912
5 Replies

4. Shell Programming and Scripting

removing duplicate records comparing 2 csv files

Hi All, I want to remove the rows from File1.csv by comparing a column/field in the File2.csv. If both columns matches then I want that row to be deleted from File1 using shell script(awk). Here is an example on what I need. File1.csv: RAJAK,ACTIVE,1 VIJAY,ACTIVE,2 TAHA,ACTIVE,3... (6 Replies)
Discussion started by: rajak.net
6 Replies

5. Shell Programming and Scripting

problem in redirecting records using nawk

I am trying to redirect record to two files using nawk if-else. #Identify good and bad records and redirect records using if-then-else nawk -F"|" '{if(NF!=14){printf("%s\n",$0) >> "$fn"_bad_data}else{printf("%s\n",$0) >> $fn}}' "$fn".orig "$fn".orig is the source file name bad... (7 Replies)
Discussion started by: siteregsam
7 Replies

6. Shell Programming and Scripting

Nawk script to compare records of a file based on a particular column.

Hi Gurus, I am struggling with nawk command where i am processing a file based on columns. Here is the sample data file. UM113570248|24-AUG-11|4|man1|RR211 Alert: Master Process failure |24-AUG-11 UM113570624|24-AUG-11|4|man1| Alert: Pattern 'E_DCLeDAOException' found |24-AUG-11... (7 Replies)
Discussion started by: usha rao
7 Replies

7. Shell Programming and Scripting

Nawk, creating a variable total from multiple lines(records)

Good Morning/Afternoon All, I am having some trouble creating a variable called "total" to display the sum of the values in a specific field, $6 for example. The data I am working on is in the following form: John Doe:(555) 555-5555:1:2:3 Jane Doe:(544) 444-5556:4:5:6 Moe Doe:(654)... (2 Replies)
Discussion started by: SEinT
2 Replies

8. Shell Programming and Scripting

nawk -- separation of records on basis of number of fields

A file file1.txt exists having records like The delimiter being "|" X|_|Y|_|Z|_| (number of fields 7) A|_|B|_| (number of fields 5) X|_|Z|_|H|_| (number of fields 7) A|_|D|_|S|_| (number of... (4 Replies)
Discussion started by: centurion_13
4 Replies

9. Shell Programming and Scripting

Replace a particular field in all records in a csv file

hi, i have various csv files, the file format is as follows Entry: "1",4,2010/08/15-10-00-00.01,,"E",,,,,,,,,120,0,"M4_","C","KEW-011-5337140-20100916163456-540097","1234567890","N N 0 ",,,"NUK 800100200",,,"NN",,,,,,,,,,,,"0000000001|0001|20150401... (2 Replies)
Discussion started by: niteesh_!7
2 Replies

10. Shell Programming and Scripting

Compare Records between to files and extract it

I am not an expert in awk, SED, etc... but I really hope there is a way to do this, because I don't want to have to right a program. I am using C shell. FILE 1 FILE 2 H0000000 H0000000 MA1 MA1 CA1DDDDDD CA1AAAAAA MA2 ... (2 Replies)
Discussion started by: jclanc8
2 Replies
Login or Register to Ask a Question