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

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