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
file.

date1,client id, principal, date2, balance, type, date3, net amount
12/17/2012,FIT:20111004:3739:25:50118:3,"7777.99",12/17/2012,"154,700.00",Float,12/17/2012,"-15,879.47"

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.

CPYFRMIMPF FROMFILE(DEVlib/FTPCSVF) TOFILE(DEVLIB/LOCALPF)
MBROPT(*REPLACE) RCDDLM(*CRLF) STRDLM(*NONE) FROMRCD(2)

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
characters.

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) )

CPYFRMIMPF FROMFILE(DEVLIB/FTPCSVF) TOFILE(DEVLIB/LOCALPF) MBROPT(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM('"') FLDDLM(',') DECPNT(*PERIOD) DATFMT(*USA) FROMRCD(2 *END)