Re: Exporting DB2/400 records . . .

On Fri, Jun 5, 2015 at 2:05 PM, <rob@xxxxxxxxx> wrote:

Where [CPYTOIMPF] fell short exporting into a csv file, and
then into excel, was a pure number in a character column

What you describe isn't so much a CPYTOIMPF issue as it is an
interaction-between-CSV-and-Excel issue.

It's understandable: Most people don't know the intricacies that well.
IBM certainly didn't for a long time (or just didn't care), which is
why CPYTOIMPF sucked so badly for so long.

For this particular situation, the thing you have to understand is
that CSV doesn't have any way (that Excel honors) to specify what's
numeric and what's text. Surrounding quotes are completely
ineffectual. You can actually force Excel to *calculate* your
text-consisting-solely-of-digits as text by making the CSV cell a
formula which evaluates to text. It's a pretty obscure trick, and is
not without its own undesirable side-effects (such as the fact that a
formula shows up in the interactive formula bar in Excel, instead of
just a value).

and in Excel I have:
MYDEC MYCHAR
5.3 7.2
1 1"
2 1,3
3 1,"3

Note: the 7.2 appears right adjusted, like a number. Not left adjusted
like a character column.

It's right adjusted like a number because it **IS** a number. At least
as far as Excel is concerned.

However, if I do this:
SQL2XLS SQLSTMT('select * from rob/export')
TOXLS('/rob/export2.xls')
COLHDRS(*FLDNAM)

Then it remains a character column.

SQL2XLS doesn't behave the same because it doesn't use CSV at all.
Likewise, using POI or the Python packages I keep mentioning will also
not involve CSV at all. These can all utilize Excel's own data types
directly, and thus can write text as text, even if that text's
contents look numeric.

John Y.