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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to check Null values in a file column by column if columns are Not NULLs
# 1  
Old 04-19-2007
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 newbie to Unix and any help will be appreciated.


Regards,
Mandab
# 2  
Old 04-19-2007
Quote:
Originally Posted by Mandab
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 newbie to Unix and any help will be appreciated.


Regards,
Mandab
Code:
awk -F"|" ' { 
       if ( $2 ~ /^ *$/ ) printf("102 ")
       if ( $4 ~ /^ *$/ ) printf("104")
       printf("\n")
} ' file

# 3  
Old 05-02-2007
I tried with the sample data but it is not giving the exact output:
The sample data is :
545689512<tab>20070424<tab>20070414<tab>456.25<tab>20061121<tab>pqr
<tab>20060726<tab>20060524<tab>800.12<tab><tab>abc
24<tab><tab>05242006<tab>22.15<tab>20050815<tab>xyz
57<tab>20040425<tab>20041214<tab><tab>20040628<tab>stv

Data will be from 3rd row in the file, the following is the script I tried:

#!/bin/ksh

awk -F"|" 'NR>=3 { ## data is from 3rd row onwards
if ( $2 ~ /^ *$/ ) printf("102")
if ( $4 ~ /^ *$/ ) printf("104")
printf("/n")
}' $1 ## filename

The out put I got is:
$ test6.ksh test1.txt
102104/n102104/n102104/n102104/n$
# 4  
Old 05-02-2007
Mandab,

Try replacing the -F"|" with -F"<tab>" where <tab> is the tab character.
Also printf("/n") should be printf("\n")
# 5  
Old 05-02-2007
Thank you for your quick response, I tried replacing as you said but still I am getting errors. The script is :
#!/bin/ksh

awk -F"<tab>" 'NR>=3 {
if ( $2 ~ /^ *$/ ) printf("102")
if ( $4 ~ /^ *$/ ) printf("104)
printf("\n")
}' $1

Errors I am getting:
$ test7 test1.txt
awk: newline in string near line 3
awk: syntax error near line 4
awk: illegal statement near line 4
# 6  
Old 05-02-2007
Quote:
Originally Posted by Mandab
awk -F"<tab>" 'NR>=3 {
I had intended that <tab> be replaced literally by the tab character - CTNL(H).

Quote:
Originally Posted by Mandab
if ( $4 ~ /^ *$/ ) printf("104)
You are missing a close " after 104.

I am confused. Is your file delimited by the tab character or by set of characters "<tab>" shown in your post?
# 7  
Old 05-02-2007
Excellent !! its working fine now.
But there is one small problem, If there is an error in field one and also field two then I should have only error to be printed as "102". but not both "102" and "104" to be printed, basing on the serial order of fields like field1, field2. Also can I use the error value in a variable so that I can use it for different purpose in my script(outside awk)?

Here is the script:
#!/bin/ksh

awk -F"," 'NR>=3 {
if (length($2)!= 8 || $2 ~ /[^0-9]/ || $2 ~ /^ *$/ ) {var1="102"}
if ( $4 ~ /^ *$/ ) {var1="104"}
{printf var1}
printf("\n")
}' $1


The output:


102
104
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

2. UNIX for Beginners Questions & Answers

Check for null values in a columns. I have dozen of CSV files in a directory.

Hi Folks, I'm trying to write a simple file sanity check script. I have a directory with dozen CSV files containing id,edname,firstname,lastname,suffix,email. I like to write a awk script to check if first field contain a number and is not empty. and fields number 3,4 & 6 are not empty and... (3 Replies)
Discussion started by: dc34684
3 Replies

3. UNIX for Dummies Questions & Answers

Check for not null column in a pipe delimited file

Hi, I have a requirement where I have to check whether the mandatory columns in a pipe delimited file is null and print error message. For eg, I have to check if the 3rd,5th,6th,7th and 8th column are null and print the message "<column name> is null". The data file will have aroung 100,000... (6 Replies)
Discussion started by: reshma15193
6 Replies

4. 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

5. Shell Programming and Scripting

Script to check for null values in a column

Hi Guys, I am new to shell script.I need your help to write a shell script. I have a csv file which has 13 columns separated by , I have written below script to fetch required 5 columns. awk -F, '(NR==1){h3=$3;h4=$4;h8=$8;h9=$9;h13=$13;next}(NF>1) \ {print... (5 Replies)
Discussion started by: Vivekit82
5 Replies

6. Shell Programming and Scripting

Check null values column

hi, I had a small question.I had a file from which i need to extract data. I have written the below script to check if the file exists and if it exists extract requierd columns from the file. IFILE=/home/home01/Report_1.csv OFILE=/home/home01/name.csv.out1 if #Checks if file exists... (1 Reply)
Discussion started by: Vivekit82
1 Replies

7. 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

8. Shell Programming and Scripting

Check for null values in columns

Hi , I have below data with fixed with of 52 bytes having three columns value data. 01930 MA GLOUCESTER 02033 02025 COHASSET 01960 MA ... (3 Replies)
Discussion started by: sonu_pal
3 Replies

9. UNIX for Dummies Questions & Answers

Check for null values in a column

Hi All, I have a file with 10 columns and get the required data for nine columns properly except 8th. In 8th column i have both NULL and NON NULL values...i.e certain records have values for all the columns including 8th column and certain records have 8th column as NULL.My requisite is,without... (20 Replies)
Discussion started by: ganesh_248
20 Replies
Login or Register to Ask a Question