Re: CpytoIMPF to a CSV File

On 13-Feb-2014 10:33 -0800, John Yeung wrote:

On Thu, Feb 13, 2014 at 11:09 AM, Audrey Guardino wrote:

Is there a way to run this command and keep the integrity of Char fields
that start with a 0?

Below is the command I am executing, when opening the CSV file the data
formats itself in a weird notation, and also removes the last digit
(happens to be a check digit in this case).

CPYTOIMPF FROMFILE(TESTFILE) TOSTMF('\path\test.csv') MBROPT(*ADD)
STMFCCSID(*STDASCII) STMFAUT(*DFT) RCDDLM(*CRLF) STRDLM(*NONE)
NULLIND(*YES)

The field is a UCC id which is defined in the file as 20 Char and
the value of the field is:
00007292380143329052
<<SNIP>>

You didn't specifically say that you're opening the file with Excel,
but we can guess from your symptoms that's what you're doing.

The most direct answer is: No, there isn't any way to create a CSV
that preserves leading zeros using CPYTOIMPF. This is because CSVs
inherently do not store type information, and certainly not type
information that is honored by Excel. Excel aggressively tries to
interpret everything as a number if it can. And every number in
Excel is (roughly) a double-precision float, so it doesn't have
enough precision to store your "number" precisely, even after
disregarding the leading zeros.

There are kludges. If you can write a program to create the CSV,
then you have a few options at your disposal. The main thing, if you
absolutely must stick to CSV, and the file will be opened in Excel,
is to turn your data into a formula which evaluates to a string:

="00007292380143329052"

<<SNIP>>

If exporting the character data in that manner is effective then...

A SQL SELECT, thus a SQL VIEW also, could easily effect the above /editing/ of the data; irrespective the original data type being numeric or character.

So for example, the OP might test the effects from:

create table qtemp/testfile (ucc_id char(20))
;
insert into qtemp/testfile values('00007292380143329052')
;
create view qtemp/testview as
select '="' concat ucc_id concat '"' as ucc_id
from qtemp/testfile
;
CPYTOIMPF FROMFILE(qtemp/TESTview) TOSTMF('\path\test.csv')
MBROPT(*ADD) STMFCCSID(*STDASCII) STMFAUT(*DFT) RCDDLM(*CRLF)
STRDLM(*NONE) NULLIND(*YES)