in reply to Excel to CSV datetime conversion- format changes

I will be bit clear on Date formats

source: file.xls has dates as below:

TDate 20/03/2014 08:07

VDate 26/03/2014

IDate 20/03/2014 08:07

Output of the .csv files looks as (changes the format when time exists in it).

TDate; 3-20-14 0:08

VDate 26/03/2014

IDate 3-20-14 8:07

  • Comment on Re: Excel to CSV datetime conversion- format changes

Replies are listed 'Best First'.
Re^2: Excel to CSV datetime conversion- format changes
by runrig (Abbot) on Apr 24, 2014 at 16:35 UTC
    Excel format handling is (to me) a twisty maze of passages. There's a map of format codes to default formats, which can change depending on locale (see the various Spreadsheet::ParseExcel::Fmt* libraries in the distribution). You may need to debug to see where things go off. As a workaround, you can use the ExcelFmt() function from Spreadsheet::ParseExcel::Utility in the distribution to format these cells (feeding it the value from calling the cell's unformatted() method rather than value()). You can also try posting the issue with the problem spreadsheet at the bug tracker (as indicated in the distribution). The maintainer may get around to looking at it.
[reply]

      Thanks for the reply. I have seen ExcelFmt() function but I am not able to utilize in the current code. Can you please help me where to call that ExcelFmt in the line of code.

[reply]
        if ($something) {
  print ExcelFmt('dd/mm/yyyy hh:mm', $eSheet->{Cells}[$row][$column]->
+unformatted()) . ","
} else {
  print $eSheet->{Cells}[$row][$column]->Value . ",";
}

        [download]
        You'll have to fill in the '$something' yourself, and I'm not absolutely sure about the format I've specified, and you may also need an 'elsif ($something_else)' block to print the format without the hours and minutes.
[reply]
[d/l]

In Section Seekers of Perl Wisdom