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

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

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.

 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.

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

modified your coode

awk -F\,  '{if($5==""){$5=99999};print $1,$2,$3,$4,$5}'  inputfile

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?

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

i think it is because of blank lines .
try like this if u can delete blank lines before processing the data.

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

or
you can do like this,

 awk -F\,  '$1>0{if($5==""){$5=99999};print $1,$2,$3,$4,$5}' inputfile

Thanks so much pravin27
That's great. It's perfectly working now.

I also tested the code by dragon.1431
It's adding the missing values to the end of all rows.

IMHO, it should work as you expected.

$ 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:

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

output:

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.

sed '/^$/d' infile will delete empty lines, but not whitespace-only lines..

Hi all,

I am trying to insert missing value (99999) in the blanks which are always in the last field.
Could someone please kindly tell me why this code

awk -F\,  '{if($17==""){$17=99999};print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17}' input file

which is working flawlessly with my previous file (sample in the first page) is not working for this new file?

The original file with blanks:

110050 99999 99999 1986 02 26 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999
110050 99999 99999 1986 02 27 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999
110050 99999 99999 1986 02 28 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999
110050 99999 99999 1986 03 1 99999 99999 99999 99999 99999 99999 0 99999 99999 99999
110050 99999 99999 1986 03 2 99999 99999 99999 99999 99999 99999 0 99999 99999 99999
110050 99999 99999 1986 03 3 99999 99999 99999 99999 99999 99999 0 99999 99999 99999
110050 99999 99999 1986 03 4 99999 99999 99999 99999 99999 99999 0 99999 99999 99999
110050 99999 99999 1986 03 5 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999
110050 99999 99999 1986 03 6 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999
110050 99999 99999 1986 03 7 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999

After using the code, 99999 is inserting at the end of all lines

110050 99999 99999 1986 02 26 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999                99999
110050 99999 99999 1986 02 27 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999                99999
110050 99999 99999 1986 02 28 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999                99999
110050 99999 99999 1986 03 1 99999 99999 99999 99999 99999 99999 0 99999 99999 99999                  99999
110050 99999 99999 1986 03 2 99999 99999 99999 99999 99999 99999 0 99999 99999 99999                  99999
110050 99999 99999 1986 03 3 99999 99999 99999 99999 99999 99999 0 99999 99999 99999                  99999
110050 99999 99999 1986 03 4 99999 99999 99999 99999 99999 99999 0 99999 99999 99999                  99999
110050 99999 99999 1986 03 5 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999                99999
110050 99999 99999 1986 03 6 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999                99999
110050 99999 99999 1986 03 7 99999 99999 99999 99999 99999 99999 0 99999 9999.9 99999 99999                99999

Thanks very much for your time and help in advance.

Verify your input file format. First post fields are separated by ","

so you can use

 awk  '{if($17==""){$17=99999};print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17}' inputfile

awk 'NF<17{$0=$0 FS 99999}1' infile

Thank you very much pravin27 and Scrutinizer. I learned from you

I like this one

awk 'NF<17{$0=$0 FS 99999}1' infile

awk 'NF<17{$(NF+1)=99999}1' infile