Script to find blank records in a file except for few columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script to find blank records in a file except for few columns
# 1  
Old 05-25-2015
Script to find blank records in a file except for few columns

I have a file with the following format:
Code:
X|High|2|GIC|DM||XHM|||6 Months  
X|Moderate|2|GIC|DM||XHM|||6 Months  
X|High|2|GCM|DM||XSF|||6 Months  
X|Med|2|GCM|DM||XSF|||6

Here there are ten columns but I need to print rows having blank records in any of the rows (except for 6th,8th and 9th column which will remain blank by default in each row(s)). Records are separated by "| " delimiter

I tried this :
Code:
awk -F'|' '{ 
for (i=1; i<=NF; ++i) 
{ 
if($i == 6 || $i == 8 || $i == 9) 
{ 
$i++;continue; 
} 
elseif ($i ~ /^[[:space:]]*$/) 
{print NR, $0; next}}' file.txt

But it says that "awk" is unable to parse the command. Can someone help in modifying it?

Last edited by Scrutinizer; 05-25-2015 at 12:01 PM.. Reason: additional code tags
# 2  
Old 05-25-2015
RedHat

Code:
perl -ne '@arr = split(/\|/,$_,-1);
for($i=0;$i<@arr;$i++) {
next if $i =~ /^(5|7|8)$/ ;
$flag = 1 if ($arr[$i] eq "");
}
print join("|",@arr) if $flag;
' file.txt

This User Gave Thanks to sam05121988 For This Post:
# 3  
Old 05-25-2015
Hi Sam

Thanks for your input but unfortunately the script is printing all the rows.
# 4  
Old 05-25-2015
Hello chatwithsaurav,

As per your 1st post columns 6th, 8th and 9th are going to be always empty, so I haven't consider that in script, try following and let me know if this helps.

Code:
awk -F"|" '{for(i=1;i<=NF;i++){if($i==""){A++}};if(A>3){print $0};A=""}'  Input_file

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 5  
Old 05-25-2015
Sam's solution works fine for me. None of the 4 rows you have provided in your sample data will be printed as they contain no blanks except for in columns 6,8,9 which you want to exclude. Another awk solution, probably much more elegant ones out there:
Code:
awk -F"|" '{
f=0
for (x=1;x<=NF;x++) {
    if( x==6 || x==8 || x==9 ) continue
    if (length($x)==0)
    f++
    }
}f' file

This User Gave Thanks to pilnet101 For This Post:
# 6  
Old 05-25-2015
You have some $i where it's i not field i
Code:
if (i == 6 || i == 8 || i == 9) {continue}
else if ...

Better readable is
Code:
if (i != 6 && i != 8 && i != 9) if ...


Last edited by MadeInGermany; 05-25-2015 at 10:22 AM..
# 7  
Old 05-25-2015
Anothere variation:
Code:
awk -F\| '{for(i=1; i<=NF; i++) if(i!~/[689]$/ && $i=="") {print; next}}' file

for fun, you could also do this, but it is less readable:
Code:
awk -F\| '{for(i=1; i<=NF; i=i==5?7:i==7?10:i+1) if ($i=="") {print; next}}' file

or this:

Code:
awk -F\| 'BEGIN{S[6]; S[8]; S[9]} {for(i=1; i<=NF; i++) if (!(i in S) && $i=="") {print; next}}' file


Last edited by Scrutinizer; 05-25-2015 at 12:21 PM..
This User Gave Thanks to Scrutinizer For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to filter records in a zip file that contains matching columns from another file

Not sure if this is the correct forum for this question. I have two files. file1.zip, file2 Input: file1.zip col1, col2 , col3 a , b , 0:0:0:0:0:c436:9346:d40b x, y, 0:0:0:0:0:880:39f9:c9a7 m, n , 0:0:0:0:0:80c7:9161:fe00 file2.txt col1 c4:36:93:46:d4:0b... (1 Reply)
Discussion started by: anil.v
1 Replies

2. Shell Programming and Scripting

To check Blank Lines, Blank Records and Junk Characters in a File

Hi All Need Help I have a file with the below format (ABC.TXT) : ®¿¿ABCDHEJJSJJ|XCBJSKK01|M|7348974982790 HDFLJDKJSKJ|KJALKSD02|M|7378439274898 KJHSAJKHHJJ|LJDSAJKK03|F|9898982039999 (cont......) I need to write a script where it will check for : blank lines (between rows,before... (6 Replies)
Discussion started by: chatwithsaurav
6 Replies

3. Shell Programming and Scripting

Remove blank columns from a tab delimited text file

Hello, I have some tab delimited files that may contain blank columns. I would like to delete the blank columns if they exist. There is no clear pattern for when a blank occurs. I was thinking of using sed to replace instances of double tab with blank, sed 's/\t\t//g' All of the examples... (2 Replies)
Discussion started by: LMHmedchem
2 Replies

4. Shell Programming and Scripting

Inserting blank columns in already present CSV file

Hi, i have a csv file which have headers and values of it like below : headers --> CI Ref SerialNumber LastScanDate values --> VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000 i want to have a above csv in below format : headers --> CI Name CI Description CI Ref... (6 Replies)
Discussion started by: omkar.jadhav
6 Replies

5. Shell Programming and Scripting

How to sort a text file if certain columns are blank?

Dear all, I am trying to sort a text file based on column 3, 10, 11 and 12. But certain column are blank for some lines. Column 3 has to be in ascending order after sorting. Part of my input file is as follows: CN727990 1 A01 4703 5083 73.28 - A_scaffold000011 4365605 4365985 73.28 +... (10 Replies)
Discussion started by: huiyee1
10 Replies

6. Shell Programming and Scripting

awk based script to find the average of all the columns in a data file

Hi All, I need the modification for the below mentioned code (found in one more post https://www.unix.com/shell-programming-scripting/27161-script-generate-average-values.html) to find the average values for all the columns(but for a specific rows) and print the averages side by side. I have... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. Shell Programming and Scripting

Awk script to replace null columns with blank

I have a file with contents "08011"||20080812 "23348"|20080827|20080924 "23387"|20080829|20080915 "23581"|20081003|20081028 "23748"|20081017|20090114 "24095"|20080919|20081013 "24105"|20070723|20070801 "24118"|20080806|20081013 "24165"|20080820|20080912 "24221"|20080908|20080929 i... (3 Replies)
Discussion started by: sonam273
3 Replies

8. Shell Programming and Scripting

copying selected records from two columns to another file

Hey guys I have got a tab-separated file and I want to copy only selected records from two columns at a time satisfying specified condition, and create a new file. My tab separated file is like this ID score ID score ID Score ID score ID score 1_11 0.80 2_23 0.74 2.36 0.78 2_34 0.75 A_34... (9 Replies)
Discussion started by: jacks
9 Replies

9. Shell Programming and Scripting

Extract columns from a file if the name dont exist put blank

Hi, I am very new to Unix script. Suppose i have a file with column header: NAME1 NAME2 Address Tel And I always need to make a file with column header: ID NAME1 NAME2 EMail Address Tel For the columns that do not exist in the file, I would still like to make a column with blank. ... (11 Replies)
Discussion started by: nightrider
11 Replies

10. Shell Programming and Scripting

Removing blank columns from a file

Hi, I have a sample file as shown below -- id parent name dba -----------------------------------... (7 Replies)
Discussion started by: sumirmehta
7 Replies
Login or Register to Ask a Question