Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace null values with dot using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace null values with dot using awk
# 1  
Old 08-17-2016
Replace null values with dot using awk
Using awk I am trying to replace all blank or null values with a . in the tad delimited input. I hope the awk is close. Thank you Smilie.

input
Code:
name    test
sam      1

liz         2
al
             1

awk
Code:
awk 'BEGIN{FS=OFS="\t"}{for(i=1;++i<NF;)$i=$i?$i:"."}1'input
awk 'BEGIN { FS = OFS = "\t" } { for(i=1; i<=NF; i++) if($i ~ /^ *$/) $i = 0 }; 1' input

desired output
Code:
name    test
sam      1
.            .
liz         2
al          .
.            1

---------- Post updated at 11:18 AM ---------- Previous update was at 09:48 AM ----------

This awk seems to work:

Code:
awk -F"\t" '{ for (i=1;i<=NF;i++) { if ($i=="") { $i="." } } OFS="\t";print }' file

Thank you Smilie.
Last edited by cmccabe; 08-17-2016 at 12:36 PM.. Reason: added awk
# 2  
Old 08-17-2016
Hello cmccabe,

You haven't told us if you want to keep the same spaces(as shown in Input_file) into your post or not. Considering if you have only 2 fields and you want to maintain the spaces as it is(though I tried hard to keep the space same), following may help you in same.
Code:
awk -F"[[:space:]]+" 'function quick(Q){match(Q,/[[:space:]]+/);W=substr(Q,RSTART,RLENGTH);return W} NF==1{print $1 quick(Q) ".";Q=$0;next} NF==0{print "." quick(Q) ".";Q=$0;next} NF==2{if($0 ~ /^[[:space:]]+/){print "." quick($0) $2} else {print}}{Q=$0}'  Input_file

Output will be as follows.
Code:
name    test
sam      1
.      .
liz         2
al         .
.             1

If your requirements are different than the shown post, please post complete details and expected output too into your next post.
EDIT: Adding non-one liner form of solution now on same.
Code:
awk -F"[[:space:]]+" 'function quick(Q){
                                        match(Q,/[[:space:]]+/);
                                        W=substr(Q,RSTART,RLENGTH);
                                        return W
                                       }
                      NF==1            {
                                        print $1 quick(Q) ".";
                                        Q=$0;
                                        next
                                       }
                      NF==0            {
                                        print "." quick(Q) ".";
                                        Q=$0;
                                        next
                                       }
                      NF==2            {
                                        if($0 ~ /^[[:space:]]+/){
                                                                        print "." quick($0) $2
                                                                }
                                        else                    {
                                                                        print
                                                                }
                                       }
                                       {
                                        Q=$0
                                       }
                      '   Input_file

Thanks,
R. Singh
Last edited by RavinderSingh13; 08-17-2016 at 02:24 PM.. Reason: Added a non-one liner form of solution successfully now.
This User Gave Thanks to RavinderSingh13 For This Post:
cmccabe
# 3  
Old 08-17-2016
Code:
sed 's/^ *\t/.\t/; s/\t *\t/\t.\t/g; s/\t$/\t./' input

This User Gave Thanks to rdrtx1 For This Post:
cmccabe
# 4  
Old 08-17-2016
Hello cmccabe,

Seems you have edited your post, so if you are trying to get number of fields from very first line(as it may be heading) and trying to put values of those fields which are having less number of fields as compare to heading, then following could do.
Let's see following is Input_file:
Code:
ame    test  test1  test2  test3  test4  test5
sam      1
liz         2
al
            1

Then following is the one.
Code:
awk 'NR==1{Q=NF;print} NR>1{for(i=1;i<=Q;i++){if(!$i){$i="."}};print}'  Input_file

Output will be as follows.
Code:
name    test  test1  test2  test3  test4  test5
sam 1 . . . . .
. . . . . . .
liz 2 . . . . .
al . . . . . .
1 . . . . . .

As you could see number of fields set by very first line to 7, similarly you could set number of fields too in case you don't want to take them from heading(very first line) too.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
cmccabe
# 5  
Old 08-17-2016
Thank you very much Smilie.
# 6  
Old 08-17-2016
Quote:
Originally Posted by rdrtx1
Code:
sed 's/^ *\t/.\t/; s/\t *\t/\t.\t/g; s/\t$/\t./' input

That will work as long as:
  1. you never have two adjacent fields only containing zero or more <space> characters (other than in a line only containing one <tab>),
  2. you never have only one or more <space> characters in the last field on an input line,
  3. you are using a version of sed that allows you to separate substitute commands with a <semicolon> character,
  4. you are using a version of the sed utility that recognizes \t in an RE as a <tab> character, and
  5. you are using a version of the sed utility that recognizes \t in a replacement string as a <tab> character
(all of the last three of which produce behavior that is not specified the standards).

The first problem can be fixed by repeating the 2nd substitute command:
Code:
sed 's/^ *\t/.\t/; s/\t *\t/\t.\t/g; s/\t *\t/\t.\t/g; s/\t$/\t./' input

The second problem can be fixed by adding a * to the RE in the last substitute command:
Code:
sed 's/^ *\t/.\t/; s/\t *\t/\t.\t/g; s/\t *\t/\t.\t/g; s/\t *$/\t./' input

The third problem can be fixed by replacing the <semicolon> characters with <newline> characters:
Code:
sed 's/^ *\t/.\t/
 s/\t *\t/\t.\t/g
 s/\t *\t/\t.\t/g
 s/\t *$/\t./' input

And the last two problems can be fixed by replacing every occurrence of \t in the above sed command with a literal <tab> character.
# 7  
Old 08-17-2016
Your 2nd solution is better structured than your working solution.
Code:
awk 'BEGIN { FS=OFS="\t" } { for (i=1; i<=NF; i++) if ($i~/^ *$/) $i="." } 1' input

It treats space characters as being empty, unlike your last solution that checks for being empty
Code:
awk 'BEGIN { FS=OFS="\t" } { for (i=1; i<=NF; i++) if ($i=="") $i="." } 1' input

---------- Post updated at 15:45 ---------- Previous update was at 15:12 ----------

Don's sed solution, after replacing \t with ${T} and putting the code in double-quotes, so the shell can substitute each ${T}.
For general safety I put the braces and escaped the $ (both not really required here).
Code:
T=$'\t'; sed "
s/^ *${T}/.${T}/
s/${T} *${T}/${T}.${T}/g
s/${T} *${T}/${T}.${T}/g
s/${T} *\$/${T}./
" input

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Count null values in a file using awk

I have the following a.txt file A|1|2|3|4|5| A||2|3|0|| A|1|6||8|10| A|9|2|3|4|1| A|0|9|3|4|5| A||2|3|4|5| A|0|av|.9|4|9| I use the following command to count null values for 2nd field awk -F"|" '!$2 { N++; next } END {print N}' a.txt It should give the result 2, but it is giving... (2 Replies)
Discussion started by: RJG
2 Replies

2. Shell Programming and Scripting

Printing null values in awk

Hi, I have a csv file with given details abc.txt 123,ra,point,,there 232,ba,points,home,pheer I want to get those values and store them in different variables: Code: while read line do echo $line |awk -F"," '{print $1" "$2" "$3" "$4" "$5"}'|read dbt_acct val_dt crncy AMT... (11 Replies)
Discussion started by: rahulsk
11 Replies

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

4. Shell Programming and Scripting

Replace null values in csv with zero

Hi, i have another issue: i have three files: FILE 1 ServiceEventHandler, Processed,Percentage 5285337,100% FILE 2 Wallet, Processed,Percentage 5285337,100% (1 Reply)
Discussion started by: reignangel2003
1 Replies

5. Shell Programming and Scripting

Handle null values-awk

I am using below code to validate the source file,code working fine but if any column contains null value then below code throwing error actually it should not.how to customize the below code to handle null null values also. When I run the script with below source data getting “date error”, as... (2 Replies)
Discussion started by: srivalli
2 Replies

6. Shell Programming and Scripting

Replace a field where values are null in a file.

Hi, I've a pipe delimited file and wanted to replace the 3rd field to 099990 where the values are null. How can I achieve it using awk or sed. 20130516|00000061|02210|111554|03710|2|205069|SM APPL $80-100 RTL|S 20130516|00000061|02210|111554|03710|2|205069|SM APPL $80-100 RTL|S... (12 Replies)
Discussion started by: rudoraj
12 Replies

7. Shell Programming and Scripting

Selective Replace awk column values

Hi, I have the following data: 2860377|"DATA1"|"DATA2"|"65343"|"DATA2"|"DATA4"|"11"|"DATA5"|"DATA6"|"65343"|"DATA7"|"0"|"8"|"1"|"NEGATIVE" 32340377|"DATA1"|"DATA2"|"65343"|"DATA2"|"DATA4"|"11"|"DATA5"|"DATA6"|"65343"|"DATA7"|"0"|"8"|"1"|"NEG-DID"... (3 Replies)
Discussion started by: sdohn
3 Replies

8. Shell Programming and Scripting

How to replace quote symbol(") and dot(.) with some other values!!

Hi , I have below input file 1.order number is useful. 2.vendor_id is produced. 3.the vandor name is "malawar". I want output file like 1. order number is useful. 2. vendor_id is produced. 3. the vandor name is VmalawarV. in input file line number 1.order number there is no... (4 Replies)
Discussion started by: vinothsekark
4 Replies

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

10. Shell Programming and Scripting

Find and replace a column that has '' to NULL in a comma delimited using awk or sed

Hi this is my first time posting ever. I'm relatively new in using AWK/SED, I've been trying many a solution. I'm trying to replace the 59th column in a file where if I encounter '' then I would like to replace it with the word NULL. example 0 , '' , '' , 0 , 195.538462 change it to 0... (5 Replies)
Discussion started by: gumal901
5 Replies
Login or Register to Ask a Question