RE: Problem with CPYFRMSTMF Using Pipe(|) Delimited Data

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Yikes! Well, here's my stab at this.

I see that the row length is 621. This is only the first 79 characters of 
each row. Do you have the number of columns to expect, as well as column sizes?

I assume you've defined the physical file properly. You need enough columns 
(the number shown here is 14, but that is not all there are, right?), and 
they all should be character columns of appropriate size - because the 
first row is all characters and cannot be numeric. They should probably all 
be NULL-capable. I assume you know what the maximum sizes should be. If 
not, just make them somewhat larger than you think you need.

The key item may be the NULL-capability. I made a file in the IFS that 
contained the lines below, then created the table (in SQL - DDS would also 
work). The following command worked:

CPYFRMIMPF FROMSTMF('imp.txt')
           TOFILE(VERN/IMP)
           RCDDLM(*ALL)
           STRDLM(*NONE)
           FLDDLM('|')
           RPLNULLVAL(*FLDDFT)

But that was with only the data shown.

Note the use of RCDDLM(*ALL) - you don't need to know what is there.

Another question - what application is this from? Too many rows for Excel, 
I think. EDI? If you can open it in Excel, then have Excel split the single 
column at the delimiter, then use the Client Access add-in to send it up to 
the 400 - the add-in will figure out data types, if you ask nice.

If you can't use Excel, you might be better off using what you started 
with, namely, CPYFRMSTMF into a flat file (CRTPF RDCLEN(621) ), then read 
each record in RPG (ignoring the first), SCAN on '|' into an array, and 
%SUBST according to the values in the array in a loop and fill the fields 
of the file you eventually want the data in. Or, if you use C/C++, try the 
strtok() function. Or use REXX, which is great for parsing this kind of 
thing and can read the flat file and output to the final destination file.

'Way too much fun!

Good luck
Vern

At 02:22 PM 1/21/2004 -0800, you wrote:
> Here's a sample of the data as it sits on OPT01:
>
> Browse : 
> /qopt/030502_0922/CONTRA~1.TXT
> Record :       1   of  339049 by  14            Column :    1    621 
> by  79
> Control 
> :
>
>
> ...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.... 
>
> ************Beginning of 
> data**************
> IPS|APN|HSENO|HSESUB|DIR|STR|SFX|UNITNO|CITY|ZIP|MAILADDR|M_HSENO|M_STR|M_CITY|
> A013|001-011-005-4|10000|||ARMSTRONG|RD||BYRON||RT 1 BOX 4A HOT SPRINGS 
> RD;BYRO
> A013|001-011-012-0|6320|||BYRON HOT PO BX 327||SP RD|BYRON||P O BOX 
> 327;BYRON C
> A013|001-011-013-8||||BYRON HOT SPRINGS|RD||BYRON||255 GLACIER DR;MARTINEZ 
> CA;%
> A013|001-011-017-9|3790|||ARMSTRONG|RD||BYRON||3790 ARMSTRONG RD;BYRON 
> CA;|3790
>
> Everything is separated by a pipe (|).