MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2014

Re: CpytoIMPF to a CSV File



fixed

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)






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact