Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Extract file records based on some field conditions

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extract file records based on some field conditions
# 1  
Old 11-30-2010
Extract file records based on some field conditions
Hello Friends,

I have a file(InputFile.csv) with the following columns(the columns are pipe-delimited):
ColA|ColB|ColC|ColD|ColE|ColF

Now for this file, I have to get those records which fulfil the following condition:
If "ColB" is NOT NULL and "ColD" has values one of the following ('A','B','C') the show the records

If the aforementioned condition is satisfied then I need to have those records only in a separate file.

Please help me in this! Thanks in Advance!!

with warm regards
# 2  
Old 11-30-2010
Quote:
awk -F"|" ' $2!="" && $4 ~ /^[ABC]$/ ' InputFile.csv > outfile
This User Gave Thanks to kevintse For This Post:
mehimadri
# 3  
Old 11-30-2010
Hello kevintse,

Sorry I failed to mentioned that the values I have to check are not 'A','B','C' but string values like 'Asia','Casablanca','Brando'

So the condition should be:
If "ColB" is NOT NULL and "ColD" has values one of the following ('Asia','Casablanca','Brando') the show the records

Sorry for the goof-up.
In this case how will teh awk command be..

Thanks in Advance!!
# 4  
Old 11-30-2010
Code:
awk -F"|" 'BEGIN { B["Asia"]=B["Casablanca"]=B["Brando"]=1; }  $2 && ($4 in B) ' InputFile.csv > outfile

If you have many more than 3 criteria (our you may change them) you should consider loading the criteria from another file:

Code:
awk -F"|" 'NR==FNR { B[$1]=1; Next }  $2 && ($4 in B) ' CritFile.txt InputFile.csv > outfile

This User Gave Thanks to Chubler_XL For This Post:
mehimadri
# 5  
Old 11-30-2010
Hi,

When I tried the command I received the following error:

$ cat InputFile.csv
ColA|ColB|ColC|ColD|ColE|ColF
1|A|B|Asia|E|F
1||C|Asia|E|F
2|B|C|Casablanca|E|F
3|B|C|Brando|E|F

$ awk -F"|" 'BEGIN { B["Asia"]=B["Casablanca"]=B["Brando"]=1; } $2 && ($4 in B) ' InputFile.csv > outfile
awk: syntax error near line 1
awk: bailing out near line 1

Please help me in this. Thanks in Advance!!
# 6  
Old 11-30-2010
Code:
nawk -F\| '$2&&$4~/^(Brando|Casablanca|Asia)$/' InputFile.csv > outfile

Last edited by Scrutinizer; 11-30-2010 at 03:56 AM..
This User Gave Thanks to Scrutinizer For This Post:
mehimadri
# 7  
Old 11-30-2010
Code:
x=($(sed '=' infile | sed -n 'N;s/\n/ /;p'|sed -n 's/[^ ]|*[^|]*|*[^ ]|*\(Asia\|Casablanca\|Brando\).*/\1/p'|sed 's/[^0-9]\|//g;/^$/d') )
for i in ${x[@]} ; do sed -n "$i p" infile ; done
1|A|B|Asia|E|F
2|B|C|Casablanca|E|F
3|B|C|Brando|E|F
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to assign points to variables based on conditions and update specific field

I have been reading old posts and trying to come up with a solution for the below: Use a tab-delimited input file to assign point to variables that are used to update a specific field, Rank. I really couldn't find too much in the way of assigning points to variable, but made an attempt at an awk... (4 Replies)
Discussion started by: cmccabe
4 Replies

2. Shell Programming and Scripting

Calculate the total 4 field based on the conditions

Please help me to write a script Match with ACNO & NAME if it matched calculate the total val1 val2 val3 and val4 and GT is total of ACNO wise.please check the output Table ----------------- 1005|ANDP|ACN|20|50|10|30 1005|ANDP|ACN|20|10|30|40 1001|AND|NAC|40|50|40|50... (22 Replies)
Discussion started by: kalia4u
22 Replies

3. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file based on certain conditions

Hi Guys, 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.... (1 Reply)
Discussion started by: Vivekit82
1 Replies

4. Shell Programming and Scripting

Extract error records based on specific criteria from Unix file

Hi, I look for a awk one liner for below issue. input file ABC 1234 abc 12345 ABC 4567 678 XYZ xyz ght 678 ABC 787 yyuu ABC 789 7890 777 zxr hyip hyu mno uii 678 776 ABC ty7 888 All lines should be started with ABC as first field. If a record has another value for 1st... (7 Replies)
Discussion started by: ratheesh2011
7 Replies

5. Emergency UNIX and Linux Support

[Solved] Extract records based on a repeated column value

Hi guys, I need help in making a command to find some data. I have multiple files in which multiple records are present.. Each record is separated with a carriage return and in each record there are multiple fields with each field separated by "|" what i want is that I want to extract... (1 Reply)
Discussion started by: m_usmanayub
1 Replies

6. Shell Programming and Scripting

extract data in a csv file based on a certain field.

I have a csv file that I need to extract some data from depending on another field after reading info from another text file. The text file would say have 592560 in it. The csv file may have some data like so Field 1 Field2 Field3 Field4 Field5 Field6 20009756 1 ... (9 Replies)
Discussion started by: GroveTuckey
9 Replies

7. Shell Programming and Scripting

filtering records based on numeric field value in 8th position

I have a ";" delimited file.Whcih conatins a number fileds of length 4 charcters in 8th position But there is a alphanumeric charcters like : space, ";" , "," , "/" , "23-1" , "23 1" , "aqjhdj" , "jun-23" , "APR-04" , "4:00AM" , "-234" , "56784 ", "." , "+" "_" , "&" , "*" , "^" , "%" , "!"... (2 Replies)
Discussion started by: indusri
2 Replies

8. Shell Programming and Scripting

using awk to count no of records based on conditions

Hi I am having files with date and time stamp as the folder names like 200906051400,200906051500,200906051600 .....hence everyday 24 files will be generated i need to do certain things on this 24 files daily file contains the data like 200906050016370 0 1244141195225298lessrv3 ... (13 Replies)
Discussion started by: aemunathan
13 Replies

9. Shell Programming and Scripting

validating a file based on conditions

i have a file in unix in which the records are like this aaa 123 233 aaa 234 222 aaa 242 222 bbb 122 111 bbb 122 123 ccc 124 222 In the output i want only the below records aaa ccc The validation logic is 1st column and 2nd column need to be considered if both columns values are... (8 Replies)
Discussion started by: trichyselva
8 Replies

10. Shell Programming and Scripting

extract lines based on few conditions

Hi, I need to extract lines based on some conditions as explained below: File format details: notes: 1. each set starts with AAA only 2. number of columns is fixed 3. number of rows per set may vary (as one set is upto DDD - 4 rows) Now, if any BBB's 5th column is blank then then... (4 Replies)
Discussion started by: prvnrk
4 Replies
Login or Register to Ask a Question