Varkh has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

This is my first pray for you guys, as I'm a Perl beginner. First of all, thank you for all the useful advices that I found in this website.

We are currently using a home-made monitoring for our Windows Servers, using VBScript. I am trying to convert it into Perl, and so far it's working.

But I'm facing a little problem : our system is using the Excel timestamp.

The output is something like "41792,4854976852", where 41792 is the number of days since the epoch, and the other part is the time.

When I use the "time" function in perl, I get something like "1401797571". I need to get the Excel timestamp format as an output.

As if it wasn't complicated enough, we can't add any module to perl.

I don't know if it will be useful considering my issue, but here's my code (it's not finished yet and it only works with AIX OS, so don't worry if it doesn't work for you) : 

use strict;
use warnings;
use Cwd qw();
use 5.010;
use Net::SMTP;
use MIME::Lite;
my $path = Cwd::cwd();

# Recovering General.ini parameters
my $filename = "$path/General.ini";
chomp $filename;
my $fh;
my $val;
my $parameter;
my $Customer;
my $Server;
my $expediteur;
my $destinataire;
my $SMTP;
my $svmonouput;
my $alerte = 0;

open($fh,'<',$filename) or die "Can't open the file $filename as read-
+only.";
my @generalsparameter=<$fh>;

foreach my $line (@generalsparameter)
{
    my @values = split(/=/,$line);
    $parameter = $values[0];
    $val = $values[1];
    if ($parameter eq "Customer")
    {
        $Customer = $val;
        chomp($Customer);
    }
    elsif ($parameter eq "Server_name")
    {
        $Server = $val;
        chomp($Server);
    }
    elsif ($parameter eq "From_email")
    {
        $expediteur = $val;
        chomp($expediteur);
    }
    elsif ($parameter eq "Dest_email")
    {
        $destinataire = $val;
        chomp($destinataire);
    }
    elsif ($parameter eq "SMTP")
    {
        $SMTP = $val;
        chomp($SMTP);
    }
}
close($fh);


# Using "svmon -P" to get RAM usage for a process
$svmonouput = `svmon -P | grep $ARGV[2]`;
my @ParsingOutput = split(/ {1,}/,$svmonouput);
my $result = $ParsingOutput[2] * 4 / 1024;

# Showing the result (will write this line into a file, later)
print "$Customer;$Server;$ARGV[2];Memory;" . time . ";$ARGV[1];$result
+";

[download]

As you can see, I use "time" in the final "print".

Is there any way to convert it into Excel timestamp (or to use anything else to get Excel timestamp format) ?

Thank you in advance !

Edit : I almost forgot to tell you : I am using Perl v5.8.8 built for aix-thread-multi.

Varkh

Replies are listed 'Best First'.
Re: Using Excel timestamp with perl
by Corion (Patriarch) on Jun 03, 2014 at 12:27 UTC

    The easiest way would be to just install Spreadsheet::ParseExcel::Utility and use its LocaltimeExcel() and ExcelLocaltime functions together with Time::Local to convert between epoch times and Excel times.

    If you really, really cannot install modules, you can just copy the relevant code out of the module and use it in your code.

    I recommend to try to get that module installed.

[reply]
[d/l]
[select]

      Thank you for the fast reply.

      Unfortunately, we really can't install any modules, as it is a script that must work for different clients that doesn't want any change on their servers (and I mean, ANY change ... :( ).

      I will try this and will keep you updated !

      Thanks,

      Varkh.

[reply]

        Here's a couple of ideas about how to use additional modules without "installing" them on the clients' systems.

        If you need to keep the script as Perl script (i.e. distribute as a .pl file to be run the interpreter), you can use App::FatPacker to pack in all of the needed modules into one big script.

        If you install PAR::Packer, you can use the pp utility to create a stand-alone executable that you could distribute to clients. In this case, the clients would not even need to install Perl on their systems. There are other packager utilities that you could use too (CavaPackager, Perl2Exe, PerlApp from ActiveState's Perl Development Kit or Pro Studio). (Some of these are free and others you will need to purchase).

[reply]
        clients that doesn't want any change on their servers (and I mean, ANY change ...
        ... including your script????
[reply]
        You can load modules to an external directory and just reference that dir on startup. 
        #!/usr/bin/perl
use strict;
use warnings;
push(@INC, "/jet/prod/include");
use MyPersonalModule;

#code here

        [download]
[reply]
[d/l]
Re: Using Excel timestamp with perl
by Varkh (Initiate) on Jun 03, 2014 at 15:09 UTC

    Thanks to Corion, I managed to do it. Here is what I needed to get an Excel format timestamp (I added some comment, to make it easier to understand) :

    # This function will return 1 if it is a leap year, or 0 if it's not

sub LeapYear 
{
    my ($iYear) = @_;
    return 1 if ( $iYear == 1900 );
    return ( ( ( $iYear % 4 ) == 0 )
            && ( ( $iYear % 100 ) || ( $iYear % 400 ) == 0 ) )
        ? 1
        : 0;
}

sub LocaltimeExcel 
{
    my ( $iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay, $iMSec ) 
+= @_;
    
    # Changing year and month to the right format
    $iMon++;
    $iYear += 1900;
    
    # Time calculation
    my $iTime;
    $iTime = $iHour;
    $iTime *= 60;
    $iTime += $iMin;
    $iTime *= 60;
    $iTime += $iSec;
    $iTime += $iMSec / 1000.0 if ( defined($iMSec) );
    $iTime /= 86400.0;    #3600*24
    my $iY;
    my $iYDays;
    
    # Date calculation
    $iY     = 1900;
    $iYDays = 366;
    
    while ( $iY < $iYear ) 
    {
        $iTime += $iYDays;
        $iY++;
        $iYDays = ( LeapYear($iY) ) ? 366 : 365;
    }
    for ( my $iM = 1 ; $iM < $iMon ; $iM++ ) 
    {
        if (   $iM == 1
            || $iM == 3
            || $iM == 5
            || $iM == 7
            || $iM == 8
            || $iM == 10
            || $iM == 12 )
        {
            $iTime += 31;
        }
        elsif ( $iM == 4 || $iM == 6 || $iM == 9 || $iM == 11 ) 
        {
            $iTime += 30;
        }
        elsif ( $iM == 2 ) 
        {
            $iTime += ( LeapYear($iYear) ) ? 29 : 28;
        }
        print LeapYear($iYear) . "\n";
    }
    $iTime += $iDay;
    return $iTime;
}

    [download]

    These are the modified functions I copied in Spreadsheet::ParseExcel::Utility. You can use them like the following (for example) :

    ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time
+);
my $timestamp = &LocaltimeExcel( $sec, $min, $hour, $mday, $mon, $year
+ );
$timestamp =~ s/\./\,/;

print $timestamp;

    [download]

    Here, I convert the current localtime into what I need. I hope this will be useful for someone else.

    Thanks Monks !

    Varkh

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom