Chuck,
Thanks I w'd try below and let you know how it goes. I'm on V6R1.
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Sunday, 6 January 2013, 1:34
Subject: 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)
midrange.com
