How to check if a column is having a numeric value or not in a file?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to check if a column is having a numeric value or not in a file?
# 1  
Old 03-29-2010
Question How to check if a column is having a numeric value or not in a file?

Hi,

I want to know, how we find out if a column is having a numeric value or not.

For Example if we have a csv file as

Code:
ASDF,QWER,GHJK,123,FGHY,9876
GHTY,NVHR,WOPI,623,HFBS,5386

we need to find out if the 4th and 6th column has muneric value or not.

Thanks in advance

Keerthan

Last edited by radoulov; 03-29-2010 at 11:52 AM.. Reason: Please use code tags!
# 2  
Old 03-29-2010
This will print only the records with numeric fourth and sixth fileds:

Code:
perl '-MScalar::Util qw/looks_like_number/' '-F, -ane
    print if looks_like_number $F[3] 
      and looks_like_number $F[5]
    ' infile

If you want all of them:

Code:
perl '-MScalar::Util qw/looks_like_number/' '-F, -lane
    print $_, " -> ", (looks_like_number $F[3] 
	  and looks_like_number $F[5])? "valid" : "invalid"
	' infile

# 3  
Old 03-29-2010
Code:
#!/bin/ksh or bash or dash or ...
cat <<EOF > $0.txt
ASDF,QWER,GHJK,123,FGHY,9876
GHTY,NVHR,WOPI,623,HFBS,5386
GHTY,NVHR,WOPI,A623,HFBS,5386
EOF

num()
{
   val="$1"
   # replace numbers with nothing, rest chars are something else
   otherchars="${val//[0-9]/}"
   [ "$otherchars" != "" ] && return 1 # not num
   return 0 # it's num
}
############################

oifs="$IFS"
cat $0.txt | while read line
do
        IFS=","
        flds=($line)
        IFS="$oifs"
        f4=${flds[3]}
        f6=${flds[5]}
        num "$f4" || echo "not num $f4"
        num "$f6" || echo "not num $f6"
done

# 4  
Old 03-29-2010
Question

WHats the keyword to find out if its a string instead of a number.
# 5  
Old 03-29-2010
Another approach:
Code:
awk -F, 'match($4,"[a-zA-Z]")||match($6,"[a-zA-Z]"){print $0 " -> invalid";next}1' file

# 6  
Old 03-29-2010
Try this , which will show lines with only numbers 4th and 6th columns


Code:
awk -F, 'int($4)==$4 && int($6)==$6' file

# 7  
Old 03-29-2010
And yet another Perl script -

Code:
$
$ cat f0
ASDF,QWER,GHJK,123,FGHY,9876
GHTY,NVHR,WOPI,623,HFBS,5386
ABCD,EFGH,IJKL,23A,MNOP,9999
PQRS,TUVW,XYZA,999,BCDE,489X
$
$ perl -F, -lane 'print if $F[3]=~/^\d+$/ && $F[5]=~/^\d+$/' f0
ASDF,QWER,GHJK,123,FGHY,9876
GHTY,NVHR,WOPI,623,HFBS,5386
$
$

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Check whether input is numeric

Hello there, find below for my code first: $pdp_asaba=`cat /tmp/temp_total | grep asaba | sed 's/*//g'` if ]] then pdp_asaba=0 fi $pdp_abuja=`cat /tmp/temp_total | grep abuja | sed 's/*//g'` if ]] then pdp_abuja=0 fi $pdp_ojota=`cat /tmp/temp_total | grep ojota | sed 's/*//g'` if ... (3 Replies)
Discussion started by: infinitydon
3 Replies

2. Shell Programming and Scripting

Sorting file based on a numeric column

Hi, I use UBUNTU 12.04. I have a file with this structure: Name 2 1245787 A G 12 14 12 14 .... Name 1 1245789 C T 13 12 12 12..... I would like to sort my file based on the second column so to have this output for example: Name 1 1245789 C T 13 12 12 12..... Name 2 1245787 A G 12 14... (4 Replies)
Discussion started by: Homa
4 Replies

3. Shell Programming and Scripting

check if a string is numeric

I checked all the previous threads related to this and tried this. My input is all numbers or decimals greater than zero everytime. I want to check the same in the korn shell script. Just validate the string to be numeric. This is what I am doing. var="12345" if ) -o "$var" !=... (14 Replies)
Discussion started by: megha2525
14 Replies

4. Shell Programming and Scripting

How to check for a Numeric Value?

Using shell, I have a variable, how can I check that variable for a numeric value such as "41.0"? My program needs to do one things if the numeric value is found, and another if something else such as a string of letter is found. is there a specific character that denotes a numeral? The... (2 Replies)
Discussion started by: chagan02
2 Replies

5. Shell Programming and Scripting

How to check if the file contains only numeric values

How to check if the file contains only numeric values. I don't want to read entire file it eats lot of cpu Or any way which consumes less memory n cpu.. Please suggest -S (2 Replies)
Discussion started by: sunilmenhdiratt
2 Replies

6. Programming

check the given string is numeric or not.

Hi, how to check the given string is numeric or not , without converting ( using strtol...). for ex: if string is C01 - non-numeric data if string is 001 - numeric data TIA (11 Replies)
Discussion started by: knowledge_gain
11 Replies

7. Shell Programming and Scripting

check whether it is a non-numeric character

Below is the abstract of the script which is working fine. if ] then error_process "Invalid month format." return 1 fi I am doing validation for month and it errors if the value is > 12 or < 0. In addition, I want to add another condition to error if it... (2 Replies)
Discussion started by: sony_dada
2 Replies

8. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies

9. Shell Programming and Scripting

to check variable if its non numeric

if test $b -ne then echo "\n\n\n\tPassword reset has been done successfully" else echo "\n\n\n\tAn error occurred" fi i want to check whether $b is non-numeric so how to do that? (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

10. Shell Programming and Scripting

How to check a column contain numeric or char data type ??

I have a file called clientname_filename.csv whose contents are like col1|col2|col3|col4| 510|abc|xxx|450| 510|abc11|yyy|350 510|pqr99|zzz| 670 512|222|439|110 Here i have check the contents of column for data type. i have a constraints that col1 always contain Numeric value column 2... (12 Replies)
Discussion started by: jambesh
12 Replies
Login or Register to Ask a Question