Monday, June 27, 2011

Awk - convert epoch to date in the same file


Example 1)
Input file:
$ cat test1.txt
2|Z|1219071600|AF|0
3|N|1219158000|AF|89
4|N|1220799600|AS|12
1|Z|1220886000|AS|67
5|N|1220972400|EU|23
6|R|1221058800|OC|89

Required output:
2|Z|Mon 18 Aug 2008 03:00:00 PM UTC|AF|0
3|N|Tue 19 Aug 2008 03:00:00 PM UTC|AF|89
4|N|Sun 07 Sep 2008 03:00:00 PM UTC|AS|12
1|Z|Mon 08 Sep 2008 03:00:00 PM UTC|AS|67
5|N|Tue 09 Sep 2008 03:00:00 PM UTC|EU|23
6|R|Wed 10 Sep 2008 03:00:00 PM UTC|OC|89

i.e. convert the UNIX epoch values on 3 rd field of the above file to standard human readable date format using Awk.

The Awk solution using "strftime" function:
$ awk 'BEGIN {FS=OFS="|"}{$3=strftime("%c",$3)} {print}' test1.txt

More about strftime format specifiers can be found here

I have created a python program to achieve the above output and the script is here

Example 2)
Input file:
$ cat test2.txt
2|Z|time:1219071600|AF|0
3|N|time:1219158000|AF|89
4|N|time:1220799600|AS|12
1|Z|time:1220886000|AS|67
5|N|time:1220972400|EU|23
6|R|time:1221058800|OC|89

Required output:
2|Z|time:Mon 18 Aug 2008 03:00:00 PM UTC|AF|0
3|N|time:Tue 19 Aug 2008 03:00:00 PM UTC|AF|89
4|N|time:Sun 07 Sep 2008 03:00:00 PM UTC|AS|12
1|Z|time:Mon 08 Sep 2008 03:00:00 PM UTC|AS|67
5|N|time:Tue 09 Sep 2008 03:00:00 PM UTC|EU|23
6|R|time:Wed 10 Sep 2008 03:00:00 PM UTC|OC|89

The awk solution:
$ awk 'BEGIN {FS=OFS="|"}{$3="time:"strftime("%c",substr($3,6,10))} {print}' test2.txt

Related posts:
- Awk Substr function explained
- Print first character of a field in a file using Awk
- Replace first 5 characters of a file using UNIX Sed and Awk
- Convert fixed length file to a CSV file using Awk

9 comments:

Derek Schrock said...

This can be shortened a little by using -v and -F along with 1 at the end of the awk script:

awk -F'|' -vOFS='|' {$3="time:"strftime("%c",substr($3,6,10))}1' test2.txt

Taufik Zukhan F said...

Hai Jadu,

i have file like this :

cat test1.txt
x
y
z

than i want to convert like this :

cat test1.txt
xyz


How to execute that.

Thanxs before Jadu

Jadu Saikia said...

@Taufik Zukhan F,

One quick way:

tr -d '\n' < file.txt

Please check http://unstableme.blogspot.com/2008/05/remove-or-replace-newlines-using.html
for other alternatives.

ioanaluiza said...

Hello,

I tried to apply your method for one of my files, but it's not working :(.

I need to convert the 4th column, and to replace date in unix time to "normal" date in the same file (the output I need is something like - for eg: 2011-08-09 23:45:00, but the output is the least problem :) ).

I've tried many scripts, many combinations, and still ... didn't succeed.

The file looks like:

3714809120065231|BLOCK|3|1312819795000|2800112155|

Could you please help me? :)

Thanks!

Jadu Saikia said...

@ioanaluiza, thanks for the question. What error message your are getting ?

Is it saying
function strftime never defined

?

One of the other alternative:
http://pythonstarter.blogspot.com/2009/04/apply-operation-on-field-python.html

ioanaluiza said...

Hi,

I receive an error message related to awk.

The script I use is this one:
cat script.ksh

#!/bin/ksh

awk 'BEGIN {FS=OFS="|"}{$4=strftime("%c",$4)} {print}' report.out > output.txt

And when I run it, I get "awk: syntax error near line 1
awk: illegal statement near line 1"

Thanks,
Ioana.

Jadu Saikia said...

@ioanaluiza, Which OS are you in ?

Can you try using
/usr/xpg4/bin/awk or nawk or gawk instead of awk.

ioanaluiza said...

Hi,

Thanks for your prompt answer! I used nawk and it worked!!!

Best regards,
Ioana.

priyanka said...

Hi
I have two file
file1.txt
id position
ab 5
ab 11
cd 34
cd 67
ce 90

file2.txt
>ab
ahhhsdhhfhfhfjddhfhdjhdgdjddjdjjdjddjgddhdhdhhhhhhhhhhhhhgdvdgddsbddhh
>cd
gdhsdjksffdhjdfkdfljdfhdkfdlflfdfserewtergrtghghyrthytyhtefsdfdgdfgh
>ce
gfghgghhghhthhhrttthfghghghghghh

required output
>ab
hhsdh
>ab
fhfhf
and so on

© Jadu Saikia www.UNIXCL.com