perl : number to date conversion in CSV file

Shell Programming and Scripting
1

I have a CSV file in the below format.
while generating CSV file from excel sheet , date in excel sheet(Format :Mon 8/28/2012) got converted into the below format with numbers 41148,41149 so on.

Could anyone please let me know how to the convert the numbers(41148,41149 so on.) to its actual date(Format :Mon 8/28/2012).

abc.net1,BW: 1.07 M,,,,,,,,,,,,,,,,,,,,,,,
Hrly Avg (IN / OUT),0:00,1:00,2:00,3:00,4:00,5:00,6:00
41148,,,,,,,,,,,,,,,,,,,,,,,,
41149,,,,,,,,,,,,,,,,,,,,,,,,
41150,,,,,,,,,,,,,,,,,,,,,,,,
41151,,,,,,,,,,,,,,,,,,,,,,,,
41152,,,,,,,,,,,,,,,,,,,,,,,,
xyz1,BW: 1.07 M,,,,,,,,,,,,,,,,,,,,,,,
Hrly Avg (IN / OUT),0:00,1:00,2:00,3:00,4:00,5:00,6:00
41148,,,,,,,,,,,,,,,,,,,,,,,,
41149,,,,,,,,,,,,,,,,,,,,,,,,
41150,,,,,,,,,,,,,,,,,,,,,,,,
41151,,,,,,,,,,,,,,,,,,,,,,,,
41152,,,,,,,,,,,,,,,,,,,,,,,,

Expected format

abc.net1,BW: 1.07 M,,,,,,,,,,,,,,,,,,,,,,,
Hrly Avg (IN / OUT),0:00,1:00,2:00,3:00,4:00,5:00,6:00
Mon 08/27/2012,,,,,,,,,,,,,,,,,,,,,,,,
Tue 08/28/2012,,,,,,,,,,,,,,,,,,,,,,,,
Wed 08/29/2012,,,,,,,,,,,,,,,,,,,,,,,,
Thu 08/30/2012,,,,,,,,,,,,,,,,,,,,,,,,
Fri 08/31/2012,,,,,,,,,,,,,,,,,,,,,,,,

---------- Post updated at 10:50 PM ---------- Previous update was at 10:34 PM ----------

from CPAN modules, I got that DateTime::Format::Excel can do the work for me.
But not sure how to proceed with this module.

   use DateTime::Format::Excel;     my $datetime = DateTime::Format::Excel->parse_datetime( 37680 );     print $datetime->ymd();     # prints 2003-02-28

---------- Post updated 09-18-12 at 02:50 AM ---------- Previous update was 09-17-12 at 10:50 PM ----------

Could anyone please help me in solving this issue ?

Thanks in advance...

Regards,
GS

2

Microsoft stores Excel dates as the number of days since January 0, 1900 (yes, Jan 0).
Humans think of it as December 31 1899. This is the Epoch for MS Excel.

Those numbers are days since the start of the Epoch date. Most UNIXes use Jan 1, 1970.

You are using the perl code correctly.

#!/bin/bash
get_date()
{
  perl -e '
   use DateTime::Format::Excel;     
   my $datetime = DateTime::Format::Excel->parse_datetime( ARGV[1] );     
   print $datetime->ymd(); '  "$1"
}

new_date=$( get_date 41148)
3

If you don't need to deal with dates outside the unix time (68 years around 1970), the excel module is an overkill. You can just shift the days by 70 years (70*365.25+1):

perl -MPOSIX -ple's/^\d{5}/strftime("%a %m\/%d\/%Y",gmtime(86400*($&-25569)))/e' csvfile