Visit Our UNIX and Linux User Community


Find and replace blank in the last field


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find and replace blank in the last field
# 1  
Old 11-19-2010
Find and replace blank in the last field

Hi all,

I have a huge file and I need to get ride of the fields 6-11 and replace the blanks in field 5 with a missing value(99999).

Code:
159,93848,5354,343,67898,45,677,5443,434,5545,45
677,45545,3522,244,
554,54344,3342,456,
344,43443,2344,444,23477
234,24446,4545,344,34576,65,424,3332,434,4324,34
546,78432,2345,566,
545,45666,8453,344,34455
434,34432,4244,298,

I'd like to have this
Code:
159,93848,5354,343,67898
677,45545,3522,244,99999
554,54344,3342,456,99999
344,43443,2344,444,23477
234,24446,4545,344,34576
546,78432,2345,566,99999
545,45666,8453,344,34455
434,34432,4244,298,99999

For the first part, I'm using the following code. I know it's not the best code for doing this, but it just does what I need.
Code:
 awk -F\,  '{print $1,$2,$3,$4,$5}'  input

Now, the blanks are in the last field and I am not able to replace them with missing values using sed. I do appreciate your help on this.
Code:
999 93848 5354 343 67898
677 45545 3522 244
554 54344 3342 456
344 43443 2344 444 23477
234 24446 4545 344 34576
546 78432 2345 566
545 45666 8453 344 34455
434 34432 4244 298

# 2  
Old 11-19-2010
modified your coode
Code:
awk -F\,  '{if($5==""){$5=99999};print $1,$2,$3,$4,$5}'  inputfile

# 3  
Old 11-19-2010
Thank you very much pravin27.

It's working very well, however it's creating a missing value in the last row too. How can I get ride of this?
Code:
999 93848 5354 343 67898
677 45545 3522 244 99999
554 54344 3342 456 99999
344 43443 2344 444 23477
234 24446 4545 344 34576
546 78432 2345 566 99999
545 45666 8453 344 34455
434 34432 4244 298 99999
    99999

# 4  
Old 11-19-2010
i think it is because of blank lines .
try like this if u can delete blank lines before processing the data.

Code:
sed '/^$/d' infile | awk -F\,  '{if($5==""){$5=99999};print $1,$2,$3,$4,$5}'

# 5  
Old 11-19-2010
or
you can do like this,
Code:
 awk -F\,  '$1>0{if($5==""){$5=99999};print $1,$2,$3,$4,$5}' inputfile

This User Gave Thanks to pravin27 For This Post:
# 6  
Old 11-19-2010
Thanks so much pravin27
That's great. It's perfectly working now. Smilie

I also tested the code by dragon.1431
It's adding the missing values to the end of all rows.
# 7  
Old 11-19-2010
IMHO, it should work as you expected.

Code:
$ cat infile
159,93848,5354,343,67898,45,677,5443,434,5545,45
677,45545,3522,244,
554,54344,3342,456,
344,43443,2344,444,23477
234,24446,4545,344,34576,65,424,3332,434,4324,34
546,78432,2345,566,
545,45666,8453,344,34455
434,34432,4244,298,
                                    

Last,45666,123,344,One

code used:
Code:
sed '/^$/d' infile | awk -F\,  '{if($5==""){$5=99999};print $1,$2,$3,$4,$5}

output:
Code:
159 93848 5354 343 67898
677 45545 3522 244 99999
554 54344 3342 456 99999
344 43443 2344 444 23477
234 24446 4545 344 34576
546 78432 2345 566 99999
545 45666 8453 344 34455
434 34432 4244 298 99999
Last 45666 123 344 One

before last data "Last 45666 123 344 One" i had two blank lines and it was deleted in output.

Previous Thread | Next Thread
Test Your Knowledge in Computers #69
Difficulty: Easy
Grep stands for 'global regular expression process'.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find max length of the field and then replace zero

hai guys, pick the 1st field and calculate max length. if suppose max length is 2, then compare the all records if <2 then add zero's prefix of the record. for ex: s.no,sname 1,djud 37,jtuhe in this max length of the 1st field is 2 right the output wil be s.no,sname 01,djud... (6 Replies)
Discussion started by: Suneelbabu.etl
6 Replies

2. Shell Programming and Scripting

Find a blank field and replace values to NA

Hi All, i have a file like col1 col2 col3 13 24 NA 12 13 14 11 12 13 14 22 NA 18 26 NA in this file if i found "NA" other values in the line are also replace by NA Could you help me! (7 Replies)
Discussion started by: Shenbaga.d
7 Replies

3. Shell Programming and Scripting

To find char field and replace null

hi, i having a file with | seperated in which i need to search char in 3rd column and replace with null. i need to replace only the coulmn where character occurs in 3rd field for eg: file1.txt xx|yy|xx|12 output file: xx|yy||12 (5 Replies)
Discussion started by: rohit_shinez
5 Replies

4. Shell Programming and Scripting

Find a blank field

Find a blank field Hi I have set of fields that have some blank values, how to find that and get its line noumbers in output file. Ex: Col1 col2 col3 11 ss 103 12 104 13 105 14 se 106 (2 Replies)
Discussion started by: Shenbaga.d
2 Replies

5. Shell Programming and Scripting

replace blank field in file 2 with content of file 1

Something like vlookup in excel, column 2 in file 2 is blank and should be replaced by column 2 in file 1 based on comparing column 1 in both files. file1 Code: 1234~abc~b~c~d~e~f~g~h~09/10/09 5678~def~b~c~d~e~f~g~h~12/06/10 8910~hij~b~c~d~e~f~g~h~03/28/13... (1 Reply)
Discussion started by: sigh2010
1 Replies

6. Shell Programming and Scripting

Find field count and replace

Hello All, I have a file with contents like apple|ball|charlie|David| England|France|Germany| Ireland|Japan|King|London| Man|Nancy|Orange| here the column delimiter is | so if any of the lines/rows in the file has 3 only records (last field is empty), i want to place a | at the end of... (4 Replies)
Discussion started by: vinredmac
4 Replies

7. UNIX for Dummies Questions & Answers

Find and replace a field in the last line

I have a file 'test.out' with contents: 1|1|10|10|I|asdf| 2|1|10|10|I|sdfg| 4|1|10|10|I|hgfj| 34|0|10|10|I|sdg| I want to modify the fifth column with value 'I' to 'A' for only the last line. Below is what I expect to see: 1|1|10|10|I|asdf| 2|1|10|10|I|sdfg| ... (3 Replies)
Discussion started by: ChicagoBlues
3 Replies

8. Shell Programming and Scripting

awk: find and replace in certain field only, help needed

I got a sample file like this. $ cat test 12|13|100|s 12|13|100|s 100|13|100|s 12|13|100|s I want to replace all 100 by 2000 only in 3rd field using "awk" This is replacing all 100's :-( $ awk -F "|" '{gsub( /100/,"2000");print}' test 12|13|2000|s 12|13|2000|s 2000|13|2000|s... (5 Replies)
Discussion started by: jkl_jkl
5 Replies

9. Shell Programming and Scripting

find pattern and replace another field

HI all I have a problem, I need to replace a field in a file, but only in the lines that have some pattern, example: 100099C01101C00000000059394200701CREoperadora_TX 100099C01201C00000000000099786137OPERADORA_TX2 in the example above I need to change the first field from 1 to 2 only if... (3 Replies)
Discussion started by: sergiioo
3 Replies

Featured Tech Videos