Using gawk to get the ceiling of the fourth column of a pipe delimited txt file

junzph · 08-28-2017, 03:04 PM

Input.txt
ABC111|8/28/2017|15:00|4.00|Z000
ABC222|8/28/2017|15:00|4.01|Z001
ABC333|8/28/2017|15:00|4.05|Z002
ABC444|8/28/2017|15:00|4.06|Z003
ABC555|8/28/2017|15:00|4.09|Z004

Output.txt
ABC111|8/28/2017|15:00|4.00|Z000
ABC222|8/28/2017|15:00|5.00|Z001
ABC333|8/28/2017|15:00|5.00|Z002
ABC444|8/28/2017|15:00|5.00|Z003
ABC555|8/28/2017|15:00|5.00|Z004

I used the statement below in Windows to get the ceiling of a one column txt file but I want to convert the fourth column
gawk "{printf("%s \t %d\n",$0, $0+=$0<0?0:0.9)}"

Please help. Thank you in advance

Sefyir · 08-28-2017, 07:02 PM

Code:

ABC555|8/28/2017|15:00|4.09|Z004
-> 
ABC555|8/28/2017|15:00|5.00|Z004

If I understand right.. you want to take the 4th column and round up?

I don't know gawk, but with python I'd do the below:
Demo of code (jupyter_notebook): https://gist.github.com/anonymous/a0...rt_value-ipynb

Code:

import csv
import math

with open('roundupo') as _f:
    for row in csv.reader(_f, delimiter='|'):
        row[3] = math.ceil(float(row[3]))
        row[3] = float(row[3])
        print(*row, sep='|')

syg00 · 08-28-2017, 07:36 PM

Quote:

Originally Posted by junzph

Please help.

How much effort have you put into understanding that code ?. Just getting another answer from us that you don't understand is not really helping. The pipe can also be specified as separator in awk - see the documentation.

sundialsvcs · 08-29-2017, 07:22 AM

Actually, for such tasks I normally use perl. (Perl-5.)

The original concept of Perl was that it was a "really souped-up awk," and I find that it simply works better for most purposes. Also, of course, it has a vast contributed library of code that you can simply install and use to do damn-near anything.

In the case at bar, you would simply loop over the file, "split" the line into an array based on the pipe-character, and choose the highest value that you find. (Source-code example not included.)

syg00 · 08-29-2017, 07:32 AM

Nope, the most minor of adjustments to the original code will work fabulously. awk is all that is needed; no need to muddy the waters with loop(s) and/or learning perl.

Turbocapitalist · 08-29-2017, 07:53 AM

Quote:

Originally Posted by syg00

Nope, the most minor of adjustments to the original code will work fabulously. awk is all that is needed; no need to muddy the waters with loop(s) and/or learning perl.

Yes, the hint for that would be to set the Field Separator and Output Field Separator to the character you need, which is a pipe. See "man awk" for the full reference for the awk language and scroll down to the section "Builtin-variables" You can set both either in a BEGIN { } statement or by appending them to your command line invocation of awk.

pan64 · 08-29-2017, 08:05 AM

you can specify the delimiter in awk, just google http://www.theunixschool.com/2012/07...iles-with.html

MadeInGermany · 08-30-2017, 01:14 PM

It is straight forward but not trivial.
You can assign a value to column $4; this reassambles $0 (the whole line), so OFS must be set.
Define a function, then it's readable and re-usable.
In the function I used int(); the attempt you gave in post #1 would denote sprintf() - but you should add 0.999 at least.

Code:

awk '
  BEGIN { FS=OFS="|" }
  function ceil(v) { return (v==int(v)) ? v : int(v)+1 }
  { $4=ceil($4); print }
' Input.txt

junzph · 09-05-2017, 02:50 PM

I finally got it going by using the statement below

gawk -F"|" "{printf($1 ""|"" $2 ""|"" $3 ""|"" "%d.00"|""$5" \n", $4+=$4<0?0:0.9)}" temp.txt

and running it on a command prompt, I get a result,

ABC111|8/28/2017|15:00|4.00|Z000
ABC222|8/28/2017|15:00|4.00|Z001
ABC333|8/28/2017|15:00|4.00|Z002
ABC444|8/28/2017|15:00|4.00|Z003
ABC555|8/28/2017|15:00|4.00|Z004

but when I save it into a batch file i.e. test.bat, I get an error that shows

gawk: {printf(1 "|" $2 "|" $3 "|" "0.9)}
gawk: ^ unternimated string

Any advise please. Thanks.

junzph · 09-05-2017, 02:51 PM

By the way the caret sign ^ unterminated string is pointing at the double quotes just before the 0.9

MadeInGermany · 09-05-2017, 03:34 PM

An awk scripts is better put in 'ticks'

Code:

gawk -F"|" '{printf "%s|%s|%s|%d.00|%s\n", $1, $2, $3, $4+=$4<0?0:0.9, $5}' temp.txt

If you put variables into a printf format string, a percent in the variables will be interpreted specially; normally you put %s place holders and add them as further arguments.