MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » January 2013

Re: cpyfrmimpf


On 05 Jan 2013 16:52, A Paul wrote:
Below is one record of my CSV file downloaded into AS/400 as a flat
file, I'm trying to load this file into externally described physical

date1,client id, principal, date2, balance, type, date3, net amount

No mention of Version\Release information, but per mention of "AS/400", presumably a very old system is being used? And while there may be some newer release support for delimited numeric data, there never had been in older releases... so the target field definitions would need to be Character instead of a Numeric data type.

Additionally some of the numeric data shown, has thousand separators, which is invalid for numeric representation. Another reason Character data type would be required for those fields.

Finally, the Date format is *USA, not the default of *ISO, so the DATFMT(*USA) may be required on the CPYFRMIMPF if not change to have that as default.


Only RCDDLM(*EOR) is valid for a FROMFILE that is a /QSYS.LIB file system; i.e. a database [flat, or better, a source] file. The invalid specification perhaps is just ignored when using FROMFILE instead of FROMSTMF.?

With Character columns in the TOFILE being used for the delimited numeric date in the FROMFILE, the STRDLM('"') is probably a more appropriate specification. That string delimiter specification allows recognition by the import feature of non-delimited *and* strings delimited with the double-quote character.

When I did the above command CPYFRMIMPF I got just first field in
LOCALPF with error, Data from file FTPCSVF in DEVMN truncated to 1

At this point the definition of LOCALPF was undefined, so the described effect is somewhat irrelevant.

Then I build DDS and defined schema for 8 fields in physical
file (LOCALPF) and did CPYFRMIMPF, data doesn't sit properly
due to principal, balance and net amount fields carry double
quotes. I would appreciate your help if you could provide
some solution to this.

Still the LOCALPF is left undefined. Here is an example of a SQL DDL to define the LOCALPF that will allow the CPYFRMIMPF request that follows that SQL, to properly import the data [row that was given]:

create table DEVLIB/localpf
( date1 date, client_id varchar(30), principal varchar(12)
, date2 date, balance varchar(15) , type char(10)
, date3 date, net_amount varchar(15) )


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