Re: Copying a CSV file to iSeries as bar delim flat file

On 11-Jan-2012 08:19 , Versfelt, Charles wrote:

I have a CL program that copies a CSV file

¿ that copies a Comma Separated Values *stream* file *to* either an program described or source physical database file.mbr ?

an iSeries flat file using a simple CPYFRMSTMF statement.

CPYFRMSTMF FROMSTMF('/HOME/CHARLIE/ARAMEX.CSV')
TOMBR('qsys.lib/qtemp.lib/IL201UFL.file/IL201UFL.mbr')
MBROPT(*REPLACE)

This seems to suggest a copy from stream to either SRC-PF or a program described [or as noted above, a database "flat" file]; i.e. the CPYFRMSTMF does not support copying to an externally described database file [excepting the TYPE(*SOURCE)].

The program then calls an RPG that parses the flat file, using
column headings to map.
It works... as long as there are no commas in the data! But, alas,
there are commas in the data. So parsing is an issue...

It was suggested I switch to bar delimiters, but as far as I can
tell CPYFRMSTMF doesn't have that option.
When I try to do a CPYFRMIMPF instead...

CPYFRMIMPF FROMSTMF('/HOME/CHARLIE/ARAMEX.CSV')
TOFILE(QTEMP/IL201UFL IL201UFL) RCDDLM(*CRLF)
FLDDLM('|') RPLNULLVAL(*FLDDFT) MBROPT(*REPLACE)

The CPYFRMIMPF is a database file import utility that would allow moving the data directly to a described database file. There is no point [generally] in copying the data to a flat file; certainly not being used for the intent for which the utility is provided.

I get garbage.

In a can ;-) About as good as "does not work".... and leads Rob and chorus to chime in, whining about how all their dependencies on defects in the old utility, without having taken any time to actually consider the described scenario. More seriously, that request told the import utility that the data was bar-delimited, but the data was really comma-delimited... so why would there be any expectation for good results?

I've tried playing with the RCDDLM which has not
helped. Is it not translating the *ASCII to *EBCDIC?

If copied to a program described file, the utility would *correctly* not translate the data, because the CCSID for a non-described\flat file is designated-as and restricted-to 65535, aka *HEX, which means "do not translate". If necessary to copy to a[n effective] flat file, then use a source physical file instead; i.e. CRTSRCPF QTEMP/IL201UFL RCDLEN(as_appropriate_plus_12) MBR(IL201UFL) instead of CRTPF QTEMP/IL201UFL RCDLEN(as_appropriate) MBR(*FILE)

Suggestions?

Re-Export the data using the bar character [or tab], then use the CPYFRMIMPF designating the chosen delimiter, to import the data directly into the destination\target database file; i.e. let the import utility do the work, instead of the RPG.

Regards, Chuck