Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Convert Numeric Time to Readable Timestamp - Perl

Shell Programming and Scripting


Tags
excel file formatting, linux, perl, shell bash

Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 2 Weeks Ago
PikK45 PikK45 is offline
Registered User
 
Join Date: Jul 2012
Last Activity: 15 July 2017, 9:15 AM EDT
Location: Chennai
Posts: 581
Thanks: 51
Thanked 75 Times in 73 Posts
Linux Convert Numeric Time to Readable Timestamp - Perl

I am trying to hit an URL using below command and get the data into an excel sheet.


Code:
wget --user=<<USERID>> --pass=<<PASSWROD>> http://www.files.thatbelongstome.com/file1 -O test1.xls

Next step is to consolidate files from 1 to 10 in a single excel sheet and send to my mail. I am working on the Perl script for the same.

Now, I need to convert the below data to readable format in the same Perl. How to achieve that?

Actual Data

Code:
GMT excel datetime      Value
42927.46024305555       2,223,003,680
42927.46030092593       2,223,323,176
42927.46035879629       2,225,181,648
42927.46041666667       2,225,843,160
42927.46047453703       2,227,239,952
42927.46053240741       2,227,508,248
42927.46059027778       2,229,306,008
42927.460648148146      2,229,592,736
42927.46070601852       2,230,992,288
42927.460763888885      2,231,824,880
42927.46082175926       2,233,473,560
42927.46087962963       2,233,610,200
42927.4609375   2,234,898,400

To be Data

Code:
GMT excel datetime	Value
07/11/2017 11:02:45	2,223,003,680
07/11/2017 11:02:50	2,223,323,176
07/11/2017 11:02:55	2,225,181,648
07/11/2017 11:03:00	2,225,843,160
07/11/2017 11:03:05	2,227,239,952
07/11/2017 11:03:10	2,227,508,248
07/11/2017 11:03:15	2,229,306,008
07/11/2017 11:03:20	2,229,592,736
07/11/2017 11:03:25	2,230,992,288
07/11/2017 11:03:30	2,231,824,880
07/11/2017 11:03:35	2,233,473,560
07/11/2017 11:03:40	2,233,610,200
07/11/2017 11:03:45	2,234,898,400

Sponsored Links
    #2  
Old Unix and Linux 1 Week Ago
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 24 July 2017, 1:27 PM EDT
Posts: 2,062
Thanks: 20
Thanked 370 Times in 334 Posts
Quote:
Originally Posted by PikK45 View Post
...
...
I need to convert the below data to readable format in the same Perl. How to achieve that?

Actual Data

Code:
GMT excel datetime      Value
42927.46024305555       2,223,003,680
42927.46030092593       2,223,323,176
42927.46035879629       2,225,181,648
42927.46041666667       2,225,843,160
42927.46047453703       2,227,239,952
42927.46053240741       2,227,508,248
42927.46059027778       2,229,306,008
42927.460648148146      2,229,592,736
42927.46070601852       2,230,992,288
42927.460763888885      2,231,824,880
42927.46082175926       2,233,473,560
42927.46087962963       2,233,610,200
42927.4609375   2,234,898,400

To be Data

Code:
GMT excel datetime    Value
07/11/2017 11:02:45    2,223,003,680
07/11/2017 11:02:50    2,223,323,176
07/11/2017 11:02:55    2,225,181,648
07/11/2017 11:03:00    2,225,843,160
07/11/2017 11:03:05    2,227,239,952
07/11/2017 11:03:10    2,227,508,248
07/11/2017 11:03:15    2,229,306,008
07/11/2017 11:03:20    2,229,592,736
07/11/2017 11:03:25    2,230,992,288
07/11/2017 11:03:30    2,231,824,880
07/11/2017 11:03:35    2,233,473,560
07/11/2017 11:03:40    2,233,610,200
07/11/2017 11:03:45    2,234,898,400

As per this webpage: DATEVALUE function - Office Support
the number in the "date" column in Microsoft Excel is the number of days since 1/1/1900.
Which means that if you have a date in a cell in MS Excel and you format the cell to a Number and you see 42927.46024305555 then the date in that cell is 42927.46024305555 days after 1/1/1900.

We can use the Time::Piece core module to work backwards using the logic above.
An attempt is posted below.
I couldn't make it to work from "1/1/1900" so I've adjusted accordingly.
Check the comments.


Code:
$
$ cat data.txt
GMT excel datetime      Value
42927.46024305555       2,223,003,680
42927.46030092593       2,223,323,176
42927.46035879629       2,225,181,648
42927.46041666667       2,225,843,160
42927.46047453703       2,227,239,952
42927.46053240741       2,227,508,248
42927.46059027778       2,229,306,008
42927.460648148146      2,229,592,736
42927.46070601852       2,230,992,288
42927.460763888885      2,231,824,880
42927.46082175926       2,233,473,560
42927.46087962963       2,233,610,200
42927.4609375   2,234,898,400
$
$ perl -lne 'BEGIN {
                 # Use core module Time::Piece to set up the epoch. I am unable
                 # to use dates less than "12/17/1902" as the epoch due to an
                 # error in the module. Microsoft Excel uses "1/1/1900" as its
                 # epoch. So I convert using "1/1/1903" as epoch and then subtract
                 # $delta days from the result.
                 use Time::Piece;
                 $epoch = Time::Piece->strptime("1-1-1903","%m-%d-%Y");
                 $delta = 365 * 3 + 2
             }
             if ($. == 1) {
                 # From the header line, get the width of the first column.
                 $len = index($_, "Value");
                 $fmt = "%-${len}s%s\n";
                 print;
                 next
             }
             # Parse the data to retrieve the MS Excel date and the value.
             # Perform date arithmetic. You can only add seconds to a Time::Piece
             # object using "+" operator. So convert days to seconds by multiplying
             # with 24*60*60.
             ($dt, $val) = m/^(\S+)\s+(\S+)$/;
             $fmt_dt = $epoch + ($dt - $delta)*24*60*60;
             printf($fmt, $fmt_dt->strftime("%m/%d/%Y %H:%M:%S"), $val);
            ' data.txt
GMT excel datetime      Value
07/11/2017 11:02:45     2,223,003,680
07/11/2017 11:02:50     2,223,323,176
07/11/2017 11:02:54     2,225,181,648
07/11/2017 11:03:00     2,225,843,160
07/11/2017 11:03:04     2,227,239,952
07/11/2017 11:03:10     2,227,508,248
07/11/2017 11:03:15     2,229,306,008
07/11/2017 11:03:19     2,229,592,736
07/11/2017 11:03:25     2,230,992,288
07/11/2017 11:03:29     2,231,824,880
07/11/2017 11:03:35     2,233,473,560
07/11/2017 11:03:40     2,233,610,200
07/11/2017 11:03:45     2,234,898,400
$
$


Last edited by durden_tyler; 1 Week Ago at 03:41 PM..
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Convert UNIX timestamp to readable format in the file rahul2662 Shell Programming and Scripting 6 09-17-2015 10:29 AM
Convert epoch time stamp into human readable format Moon1234 Shell Programming and Scripting 10 03-12-2014 03:22 PM
Convert epoch to human readable date & time format Yaminib Shell Programming and Scripting 3 08-14-2010 09:57 AM
PERL:How to convert numeric values txt file to PACKED DECIMAL File? aloktiwary Shell Programming and Scripting 1 05-20-2009 09:55 AM
Unix timestamp to readable date Rhije UNIX for Dummies Questions & Answers 4 01-18-2009 03:28 PM



All times are GMT -4. The time now is 08:45 PM.