On Fri, Jun 5, 2015 at 2:40 PM, <rob@xxxxxxxxx> wrote:
Excel doesn't seem to be able to handle the timestamp generated by either [CPYTOIMPF or SQL2XLS].
Basically it makes it a character column.
This also goes back to how Excel imports things that aren't native to it.
Let's back up a step. Excel has just three underlying data types:
character, IEEE(ish) double-precision float, and Boolean. Datetimes
are just specially formatted floats.
Since CSVs do not have *any* inherent types, Excel uses some
heuristics to parse everything. Things that look like Booleans (just
the case-insensitive words "true" and "false", basically) are imported
as Boolean; things that look like numbers (including scientific
notation and datetimes) are imported as float; valid formulas are
imported as formulas; and everything else is imported as character.
Tools that don't use CSVs at all and just write directly to Excel
files have to know how to convert their dates, times, and timestamps
to the appropriate float value and formatting to be usable by Excel as
a float-interpreted-as-datetime. CPYTOIMPF happens to not give you a
"timestamp format" parameter, and the default user-facing output of a
DB2 timestamp happens to not match Excel's datetime recognition
heuristics, so you're out of luck there.
I'm a little surprised and disappointed that SQL2XLS doesn't know how
to convert its timestamps to Excel. I mean, if you can convert DB2
dates and DB2 times, then you already have all the calculations you
need to convert DB2 timestamps (you literally just add the date
portion and the time portion).
John Y.
midrange.com
