Yes... it's a mess, and you just reminded me of one of the reasons I
started scripting directly to XLSX instead of CSV :) With PHPExcel you
can tell Excel what the format of a cell is (i.e. Text) to avoid the
translations. I first noticed this when spitting out unit addresses to
CSV, such as:
"12DE","12DF","12E0","12E1"
The first two are fine, but the next 2 are interpreted as exponential
notation regardless of the quotes. One way around this is to make the
value ="12E0" once it arrives in Excel, but even that is difficult in
CSV - something like this might be required:
"=""12DE""","=""12DF""","=""12E0""","=""12E1"""
On 3/24/2021 6:35 AM, Charles Mills wrote:
For example, be sure to single quote numeric strings, if they are really
strings, not integers. Otherwise you risk having Excel turn the phone number
18774266006 into 1.88E+10.
Charles
-----Original Message-----
From: IBM Mainframe Discussion List [mailto:[email protected]] On
Behalf Of Arthur
Sent: Wednesday, March 24, 2021 4:15 AM
To: [email protected]
Subject: Re: Creating XLS file
On 23 Mar 2021 22:10:26 -0700, in bit.listserv.ibm-main
(Message-ID:<[email protected]>)
[email protected] (Gadi Ben-Avi) wrote:
>If it's just text, creating a csv file shouldn't be too
difficult, and Excel will be able to read it with no
problems.
>Separate the fields with commas, and if a field has
spaces, surround it with ".
>Format dates and time as something excel will understand
as a date or time.
It's obvious that the easiest way is to create a CSV file.
But it might not be the best way. You have to worry about
data corruption if Excel misinterprets your fields. See,
for instance:
https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-exce
l-misreading-dates
https://www.youtube.com/watch?v=yb2zkxHDfUE
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
