Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Find Null values in Columns and fail execution by displaying error message

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Find Null values in Columns and fail execution by displaying error message
# 1  
Old 04-06-2015
Linux Find Null values in Columns and fail execution by displaying error message
Hi All,

I am new to shell scripting. I have a requirement as part of my job to find out null/empty values in column 2 and column 3 from a CSV file and exit the further execution of script by displaying a simple error message.

I have developed a script to do this by reading various articles from this forum but ended up with no luck, each time I run the script it is going to else part of the loop and printing "All records are Good" and Line no 17 -F,:not found syntax error.

PFB my script and sample file which I am using for testing,

Sample file: pf_20120406.csv
Code:
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,,,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098

pf_validations.sh
Code:
#!/usr/bin/sh
#
#filename=`ls -ltr /var/datastage/FRPDEVL/work/source/landing/dspf/pf_*.csv`
#
for fname in /var/datastage/FRPDEVL/work/source/landing/dspf/pf_*.csv;do
filename=`basename $fname`
fdate=`echo $filename|tr -dc '[:digit:]'`

  #Validation 1: Column B and C should not have a null value they are of varchar type.
  if [ $filename = 'pf_'$fdate'.csv' ]
  then
  echo "------------------------------------------------------------------------------------"
  echo "Checking Specific Validations for File: pf_$fdate.csv"
  echo "------------------------------------------------------------------------------------"
  sed '1d;$d' pf_$fdate.csv >pf_$fdate.tmp
  #grep -lE '(,,|,$)' *.csv
  value=`awk -F"," '{if ( $2 ~ /^ *$/ ) printf("102") if ( $3 ~ /^ *$/ ) printf("104") printf("\n")}' pf_$fdate.tmp`
  #while read line;do
  if [[ "$value" == "102" || "$value" == "104" ]]
  then
  echo "Null value present in file"
  echo "Hence exiting the Job"
  rm -f pf_$fdate.tmp
  exit 16  
  else
  echo "All records are good"  
  fi
  #done <pf_$fdate.tmp
  echo "------------------------------------------------------------------------------------"
  echo "Specific Validations check for File: pf_$fdate.csv completed"
  echo "------------------------------------------------------------------------------------"  
  fi
  
  rm -f pf_$fdate.tmp
 
done

Please help me how can I achieve my requirement and help me with the correct code snippet.

It would be of great help. Thanking you all in advance

With Regards,
TPK.
# 2  
Old 04-06-2015
$value will never equal 102 nor 104; for every line in your input file there will be a line in awk's output, possibly empty, and all of this will be assigned to value. So the comparisons will always fail, and the else branch will be taken.

And, you could do all this more efficiently. You're using sed, and awk anyhow; why not do all the logics in awk alone, saving the effort of running multiple external commands and creating temp files? And, have ls serve the relevant files to you, as opposed to listing all .csv files and then discriminate them in an if construct.
# 3  
Old 04-06-2015
Linux
Hi Rudic,

Thank you for providing your insights and your expert suggestions. Can you please show the code how we can achieve every thing in awk.

Please do the needful.

With Regards,
TPK
# 4  
Old 04-06-2015
Not sure I can do that; I don't understand everything you do in your script.
- What files do exist, and which files do you want to in-/exclude?
- Why do you strip their first and last lines from the files?
- Do you want to differentiate between $2 missing vs. $3 missing?
# 5  
Old 04-07-2015
Linux
Hi All,

Can any one help me with the code to achieve the requirement I am trying to achieve which I mentioned in Post 1

The script will loop through all files that start with pf_YYYYMMDD.csv files and remove the header and trailer record in each iteration and create a temp file with same name as of the original file name but with .tmp extension.

Now I am trying to check for any null values in Column 2 and Column 3 and trying to assign 102 or 103 value if either of the column value is null in any row.

After that i check for the value that is coming from the variable $value in the IF condition and if it matches then print simple echo message remove the .tmp file and exit the job with out further processing for next files in the loop. If there are no null values in the file for which it is processing it will go to else part and print "All records are good".

The above explained is what the script will be doing, but even though as you can see below in the file though there are null values present in column 2 and column 3 at row number 3,6,8 it is printing all records are good.

Please help me how can I check for null values and exit the script execution with out further processing. Please help me.
Test file name: pf_20120406.tmp
Code:
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,,,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,des,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098
D,xsn,,1.23,3.43,34.9203,234.23423,12.345,234.987,987.342,789.345,679.0987,345.657,345.987,987.098

With Regards,
TPK

---------- Post updated at 04:08 AM ---------- Previous update was at 12:00 AM ----------

Hi All,

Find after lot of trials with different combinations, I resolved my issue and my script is working as expected.

PFB the correct code snippet

Code:
#!/usr/bin/sh
#filename=`ls -ltr /var/datastage/FRPDEVL/work/source/landing/dspf/pf_*.csv`
#
for fname in /var/datastage/FRPDEVL/work/source/landing/dspf/pf_*.csv;do
filename=`basename $fname`
fdate=`echo $filename|tr -dc '[:digit:]'`

  #Validation 1: Column B and C should not have a null value they are of varchar type.
  if [ $filename = 'pf_'$fdate'.csv' ]
  then
  echo "------------------------------------------------------------------------------------"
  echo "Checking Specific Validations 1 for File: pf_$fdate.csv"
  echo "------------------------------------------------------------------------------------"
  sed '1d;$d' pf_$fdate.csv >pf_$fdate.tmp
  val=`cat pf_$fdate.tmp|awk -F, '$2=="" || $3=="" {print "1"}'|wc -l`
  if (( "$val" >= "1" ))
  then
  echo $val" Null value present in file. Hence exiting the Job"
  rm -f pf_$fdate.tmp
  exit 16  
  else
  echo "All records are good"  
  fi  
  echo "------------------------------------------------------------------------------------"
  echo "Specific Validations check for File: pf_$fdate.csv completed"
  echo "------------------------------------------------------------------------------------"  
  fi
  
done

Thank you all for your help.

With Regards,
TPK.
# 6  
Old 04-07-2015
Without deleting first and last lines, and not discriminating special dates, this might do sort of what you need:
Code:
awk     'function PR()  {print FILENAME, V?": Null values " V:": All records good."}

         !$2            {V=102}
         !$3            {V=103}

         FNR==1 && NR>1 {PR(); if (V) EX=16; V=0}

         END            {PR(); exit EX}
        ' FS="," pf_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].csv
pf_20150313.csv : Null values 103
pf_20150314.csv : All records good.
pf_20150314.csv : Null values 103

echo $?
16

This User Gave Thanks to RudiC For This Post:
tpk
# 7  
Old 04-08-2015
Linux
Hi Rudic,

Thank You Very much for providing an excellent solution. But there is a problem here, The reason why I am truncating Header and Trailer records is for header and trailer records for column 2 or Column 3 we may get null values.

So in your awk function though the actual data I am interested in doesn't have any null values excluding header and trailer in the file it is displaying Null Values message as the trailer record has null values for column 2 and column 3.

Is there any way that you can tweak the function to exclude header and trailer and check the file.

I am sorry for this, I should have mentioned clearly initially only why I am excluding the Header and trailer records.

And your output is a bit confusing, My requirement is is there are 3 files, after processing 1st file if the function finds null values in second file then and there it should exit the function with out further processing and further processing next file also, and it seems by seeing your o/p if in first file it found null values then it is processing next files also.

And at this point after the function execution is completed if we do echo $?, the o/p will be always 0 which means the command is executed successfully, My requirement is if we find null values in 1st file itself then it should exit with 16 and should not process further files and when we do an echo $? the status should be printed as 16.


With Regards,
TPK.
Last edited by tpk; 04-08-2015 at 04:27 AM.. Reason: Updated with correct information
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Multiple columns replace with null values.

I am trying to replace the partcular columns(Col3,col5,col20,col44,col55,co56,col59,col60,col61,col62,col74,col75,col88,col90,col91,col93,col94,col95) with empty Input file Col1,col2,col3,col4,col5------,col100 1,2,3,4,5,---------,100 3,4,5,6,7,---------,300 Output : ... (3 Replies)
Discussion started by: onesuri
3 Replies

3. Shell Programming and Scripting

Find for line with not null values at nth place in pipe delimited file

Hi, I am trying to find the lines in a pipe delimited file where 11th column has not null values. Any help is appreciated. Need help asap please. thanks in advance. (3 Replies)
Discussion started by: manikms
3 Replies

4. UNIX for Dummies Questions & Answers

Eliminate error message (/dev/null)?

I am trying to eliminate an error message from a script. This is the error message: find: stat() error /usr/openv/netbackup/db/images/KUMAX: No such file or directory if ]; then runthiscommand=`su nxadm -c "ssh -t $new3 exec /bin/sh -s">/tmp/filew3 2>/tmp/error.txt<<EOF ... (1 Reply)
Discussion started by: newbie2010
1 Replies

5. UNIX for Dummies Questions & Answers

Find duplicated values in two columns out of three

hi! could u help in the following? I have the data (long list!) that looks like (three coumns white space separated): rs3094315 0.0665173 742429 rs12562034 0.0738998 758311 rs3934834 0.396449 995669 rs9442372 0.402693 1008567 rs3737728 0.406271 1011278 rs6687776 0.435429 1020428 rs9651273... (4 Replies)
Discussion started by: kush
4 Replies

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

7. Shell Programming and Scripting

How to list Matching Directories OR NULL w/o error message?

I want to be able to get all the directories in a path into a variable array, BUT if there ARE NO directories I want the Variable to be NULL and not echo any error message! If there ARE directories, this will get the list of the directories whose name begins with the string "20":... (6 Replies)
Discussion started by: pgorbas
6 Replies

8. Shell Programming and Scripting

find error?? find / -name "something.txt" 2>/dev/null

why is this giving me errors? i type this in: find / -name "something.txt" 2>/dev/null i get the following error messages: find: bad option 2 find: path-list predicate-list :confused: (5 Replies)
Discussion started by: magiling
5 Replies

9. Shell Programming and Scripting

Need to return fail or pass from shell script on the basis of pl/sql code execution

Hi guys, I am quite new in shell scripting. I am tring to promote some oracle jobs into control-M. In control-M, I am calling a script which establishes a connection with database and execute some procedures. Now I want if that PL/sql Block got failed script should return failure to... (2 Replies)
Discussion started by: alok1301
2 Replies

10. 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
Login or Register to Ask a Question