Script to check field value from a file records


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script to check field value from a file records
# 1  
Old 05-01-2018
Script to check field value from a file records

I need a script to check the records in a file , if any value match transfer the record in error.txt file.


1- If any of the any field value is NULL(nothing in this field)

Code:
Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

Example:

Code:
11111111|22222222|NULL|12|444|27042018|27042018 (Record3 is null)

99999999|88888888|007172FD|NULL|975|02052018|27042018 (Record4 is null)


2- If any of the field value is set as 0, except Record4

Example:

Code:
Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

Record1 = 0 or ‘00000000’
Record2 = 0 or ‘0000000000’
Record3 = 0 or ‘0000000000’

Code:
11111111111|000080809643|00000000|64|374|02052018|27042018
11111111111|000000000000|45678987|64|374|02052018|27042018
0000000000|000082079914|0071170B|1|308|27042018|27042018

3- If Next DATE1/DATE2 values are not in DDMMYYYY format

4- If Record5 value is negative

Code:
11111111111|000045672345|45678987|64|-222|02052018|27042018


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 05-01-2018 at 05:48 PM.. Reason: Added CODE tags.
# 2  
Old 05-01-2018
Assuming NULL means "empty field", try
Code:
awk -F\| '
NR > 1  {for (i=1; i<=3; i++) if (!$i)  {print; next}
         if ($4   == "")        {print; next}
         if ($5+0 <= 0)         {print; next}
         for (i=6; i<=7; i++) if (!($i ~ /[0-3][0-9][01][0-9]20[12][0-9]/))     {print; next}
        }
' file > error.txt

The date format check may need to be refined with a more detailed regex...

Last edited by RudiC; 05-01-2018 at 06:13 PM..
This User Gave Thanks to RudiC For This Post:
# 3  
Old 05-03-2018
required following

If any of the field value is set as 0, except Record4

Example:
Record1|Record2|Record3|Record4|Record5|DATE1|DATE2
Record1 = 0 or ‘00000000’
Record2 = 0 or ‘0000000000’
Record3 = 0 or ‘0000000000’

If Record5 value is negative

Code:
11111111111|000045672345|45678987|64|-222|02052018|27042018

---------- Post updated 05-02-18 at 06:43 PM ---------- Previous update was 05-01-18 at 08:18 PM ----------

Thanks above script worked

---------- Post updated 05-03-18 at 01:44 PM ---------- Previous update was 05-02-18 at 06:43 PM ----------

HI , i my case format DDMMYYY or MMDDYYYY not create any problem, i am using following AWK script in some files it works & in some files it is not working.


Code:
awk -F'|' -v OFS='|' ' $6 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" || $7 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" '


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 05-03-2018 at 04:00 PM.. Reason: Added CODE tags.
# 4  
Old 05-03-2018
Please take the time to carefully AND unambiguously phrase your request.

Is your problem solved ("Thanks above script worked")?
Or has another quirk occurred ("in some files it works & in some files it is not working"? How could a script be working and not working? Show WHAT works, and WHAT doesn't. Supply data samples and error conditions & messages!
# 5  
Old 05-03-2018
DATE Format check

Sorry for that I convert files dos2unix it worked.

i use following awk command:

Code:
awk -F'|' -v OFS='|' ' $6 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" || $7 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[12])[0-9]{4}$" '



but its not matching the date for given record:

Code:
Record1|Record2|Record3|Record4|Record5|DATE1|DATE2

108899999|0000222222|00AAAAA|-228|60|29102017|27042018


if DATE1 having 10th month its not matched with awk script.


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 05-03-2018 at 05:55 PM.. Reason: Added CODE tags.
# 6  
Old 05-03-2018
Of course not - you didn't cover the 10 in the regex.
# 7  
Old 05-03-2018
working now

Thanks , i use following awk command to check date format DDMMYYYY, it works:

Code:
 awk -F'|' -v OFS='|' ' $6 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])((19|20)[0-9][0-9])$" || $7 !~ "^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])((19|20)[0-9][0-9])$"'


Last edited by Don Cragun; 05-03-2018 at 07:21 PM.. Reason: Add CODE tags again.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Command/script to match a field and print the next field of each line in a file.

Hello, I have a text file in the below format: Source Destination State Lag Status CQA02W2K12pl:D:\CAQA ... (10 Replies)
Discussion started by: pocodot
10 Replies

2. Web Development

Javascript to check field is empty then execute rest of script

I have found this bit of code that nearly does what I want. Basically 3 input fields, I want to copy t2 to t3 as it's typed but only if t1 contains data AND t3 is empty: <input type="text" id="t1" /> <input type="text" id="t2" /> <input type="text" id="t3" /> <script> var t2 =... (4 Replies)
Discussion started by: barrydocks
4 Replies

3. Shell Programming and Scripting

Read File and check records for length

I need a script that will run in unix to: 1) Read and input file with 1 column that contains for ex: 0123456789 1234567890 ...etc 2) Checks the first column if it is: a. Numeric from 0 - 9 b. if it is not less... (4 Replies)
Discussion started by: mrn6430
4 Replies

4. Shell Programming and Scripting

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... (9 Replies)
Discussion started by: mehimadri
9 Replies

5. Shell Programming and Scripting

Replace a particular field in all records in a csv file

hi, i have various csv files, the file format is as follows Entry: "1",4,2010/08/15-10-00-00.01,,"E",,,,,,,,,120,0,"M4_","C","KEW-011-5337140-20100916163456-540097","1234567890","N N 0 ",,,"NUK 800100200",,,"NN",,,,,,,,,,,,"0000000001|0001|20150401... (2 Replies)
Discussion started by: niteesh_!7
2 Replies

6. Shell Programming and Scripting

How to check field formatting of input file?

Hi, I had input file with below data, abcdefghij;20100903040607;1234567891;GLOBAL; Having values of fields with seperated by semi-colon (;) and ended with line feed (\n). Through shell script, how can I check the field formatting? Thanks in advance. (18 Replies)
Discussion started by: Poonamol
18 Replies

7. Shell Programming and Scripting

simplify the script, check field match to value in a file

Hi Everyone, Below is the script, i feel there should be more simple way to do the same output, my one works, but feel not nice. like using index i feel it is slow (image my file is very large), maybe awk can do one line code? Please advice. # cat 1.txt 1 a 2 b 3 cc 4 d # cat 1.pl... (6 Replies)
Discussion started by: jimmy_y
6 Replies

8. Shell Programming and Scripting

how to check if a file has zero records

I want to do conditional logic on whether a file has 0 bytes or more than 0 bytes. what is the easiest way to do this? (2 Replies)
Discussion started by: guessingo
2 Replies

9. Shell Programming and Scripting

Based on num of records in file1 need to check records in file2 to set some condns

Hi All, I have two files say file1 and file2. I want to check the number of records in file1 and if its atleast 2 (i.e., 2 or greater than 2 ) then I have to check records in file2 .If records in file2 is atleast 1 (i.e. if its not empty ) i have to set some conditions . Could you pls... (3 Replies)
Discussion started by: mavesum
3 Replies

10. Shell Programming and Scripting

Check valid records in really big file with one commend..

Hi, I have a 5 gig file, no record terminators, field terminators are newline. The record length is 768 and I would like to check that every 768th byte is a newline and print out the byte position if it isn't. I would like to do this going either forward or backwards with one command if... (3 Replies)
Discussion started by: vtischuk@yahoo.
3 Replies
Login or Register to Ask a Question