Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to extract 4th field if numerics?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to extract 4th field if numerics?
# 1  
Old 12-20-2012
How to extract 4th field if numerics?
I have a file which contains fields comma separated & with each field surrounded by quotes. The 4th field contains either a serial number, the text ABC, the text XYZ or it's blank. I want to only extract records which have a serial number. Here's some sample data:

Code:
> cat myfile
"ABC123","00CJ","SP16","","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240095028","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","ABC","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240104067","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","XYZ","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","XYZ","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240025035","SP265","","S248","19/05/2003"
>

I thought I could do this using this command but I now realise that the .* construct selects the entire string until it finds a field which satisfies [0-9].* This means that all fields are selected.

Code:
> grep  "\".*\",\".*\",\".*\",\"[0-9].*\"" myfile
"ABC123","00CJ","SP16","","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240095028","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","ABC","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240104067","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","XYZ","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","XYZ","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240025035","SP265","","S248","19/05/2003"
>

What I want are just these records:

Code:
"ABC123","00CJ","SP16","00240095028","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240104067","SP265","","S248","19/05/2003"
"ABC123","00CJ","SP16","00240025035","SP265","","S248","19/05/2003"

There must be a simple way to select only records with a 4th field that is a numeric value. Can anyone advise please?

Thanks, Chris
# 2  
Old 12-20-2012
Code:
grep -E '^("[^"]+",){3}"[0-9]+"' infile

If your PATH defaults to a grep implementation which is not POSIX, try with egrep.
This User Gave Thanks to radoulov For This Post:
CHoggarth
# 3  
Old 12-20-2012
Try this :
Code:
awk -F',"' '{print int($4)}' file

---------- Post updated at 06:02 AM ---------- Previous update was at 06:01 AM ----------

Sorry about my previous command.

Code:
awk -F',"' '{ if (int($4) > 0) print $0}' file

# 4  
Old 12-20-2012
with awk..

Code:
awk -F '","'  '$4 ~ /^[0-9]+$/' file

Quote:
Originally Posted by Jairaj
Code:
awk -F',"' '{ if (int($4) > 0) print $0}' file

0 also numeric value.
# 5  
Old 12-21-2012
Thanks for the replies - when I started testing more fully I discivered some other issues with my data which mean I needed to do somethign slightly different.

Thansk for your help though.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extract lines whose third field is 0

Hi, I have a file with colon separated values like below. How can i get those lines whose third field is 0 (zero). In the below example, lines starting with stapler and tempo has its third field as 0 $ cat list.txt galaxy:b:5:world stapler:a:0:hello abc:a:4:stomper kepler:uic:5:jam... (8 Replies)
Discussion started by: John K
8 Replies

2. Shell Programming and Scripting

perl script to split the text file after every 4th field

I had a text file(comma seperated values) which contains as below 196237,ram,25-May-06,ram.kiran@xyz.com,204183,Pavan,4-Jun-07,Pavan.Desai@xyz.com,237107,ram Chandra,15-Mar-10,ram.krishna@xyz.com ... (3 Replies)
Discussion started by: giridhar276
3 Replies

3. Shell Programming and Scripting

How to extract 3rd,4th and 5th element

Hi All! I trying to execute this perl script to extract a particular field in a text file, luckily it works. But, right now I would like to extract 3 more fields in the text file but couldnt get the right syntax on how to do it. command is: perl -pe '$_ = (split(//)) . "\n"' TestDoc.txt... (3 Replies)
Discussion started by: VicNetIT
3 Replies

4. Shell Programming and Scripting

Extract Field from a file

I have an input file with content like : 18:51:18 | 217863|Acct 0110855565|RC 17608| 16 Subs| 1596 UsgRecs| 2 Secs| 430 CPUms| prmis2:26213 <MoveUsage d aemon needs to run on this account before it can be billed.> 23:03:30 | 896529|Acct 2063947620|RC 17608| 8 Subs| 148 UsgRecs| ... (9 Replies)
Discussion started by: Rajesh Putnala
9 Replies

5. Shell Programming and Scripting

Replace 4th field if null

Hi .. Can some one please suggest me how to replace 4th field(column) of a .csv file with "NA" if it is null. Input file data: |A|21|B1||1.1| |A|21|C|RAGH|1.1| |A|21|D1||1.1| |A|21|C|YES|1.1 Expected Output |A|21|B1|NA|1.1| |A|22|C|RAGH|1.1| |B|23|D1|NA|1.1| |A|24|C|YES|1.1| Thank... (4 Replies)
Discussion started by: pasupuleti81
4 Replies

6. UNIX for Dummies Questions & Answers

extract a field by logic

below are the contents of file 'tmp.out': 2|34|534| 1|355|54| 1|443|34| 3|43|768| 3|7|887| 1|9|990| How do I extract the 2nd and 3rd columns of file 'tmp.out' only when the first column equals '1'. Note that this file will be huge; atleast 5million+ rows, so I want to avoid looping... (4 Replies)
Discussion started by: ChicagoBlues
4 Replies

7. Shell Programming and Scripting

How to extract 3rd line 4th column of a file

Hi, Shell script: I would need help on How to extract 3rd line 4th column of a file with single liner Thanks in advance. (4 Replies)
Discussion started by: krishnamurthig
4 Replies

8. Shell Programming and Scripting

display file where 4th field = 200704

Hello I have a file which is pipe delimited. The 4 th field has value like 200704 or 200705 etc. Now i have to get only those records where 4th field is 200704 How can i do this? I have to get the whole record where 4th field = 200704 (7 Replies)
Discussion started by: vasuarjula
7 Replies

9. AIX

display file where 4th field = 200704

Hello I have a file which is pipe delimited. The 4 th field has value like 200704 or 200705 etc. Now i have to get only those records where 4th field is 200704 How can i do this? I have to get the whole record where 4th field = 200704 (4 Replies)
Discussion started by: vasuarjula
4 Replies

10. Shell Programming and Scripting

Drop records with non-numerics in field X

I have tab delimited file and need to remove all records prior to sort, that have non-numerics in the Field 2. valid/invalid field 2 data examples are: " 123" valid "1 23" invalid " NOPE" invalid I've tried this awk it does not recognize tab as the delimiter or check... (3 Replies)
Discussion started by: akxeman
3 Replies
Login or Register to Ask a Question