Re: Multiple file formats, same fields to data structures -- RPG400-L

Charles,

Create a field equivalence table:

File_Name Table_field Input_field
FILE01 FLD01 INDFLD01
FILE01 FLD02 INDFLD01
FILE02 FLD01 INDFLD01

And so on …

Then read all the field names of the table you want to process, and create a
dynamic SQL INSERT statement, something like (*very* rough idea):

SQL_INSERT_1 = 'INSERT INTO ' + FILE_NAME + '(' ;
Clear VAR_ST;
Clear VAL_ST;

READ TAB_FIELDS;
DoU %eof(TAB_FIELDS);
IF VAR_ST = *BLANKS;
VAR_ST = VAR_ST + Table_field;
VAL_ST = VAL_ST + ':' + Input_field;
ELSE;
VAR_ST = VAR_ST + ', ' + Table_field;
VAL_ST = VAL_ ST + ', :' + Input_field;
ENDIF;
READ TAB_FIELDS;
ENDDO;

SQL_ST = SQL_INSERT_1 + VAR_ST + ') ';
SQL_ST = SQL_ST + 'VALUES(';
SQL_ST = SQL_ST + VAL_ST + ')';

// SQL_ST has now your dynamic SQL statement…
You could "pre-fill" your Table with data extracted from QADBIFLD.

HTH,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--

On Thu, Jul 14, 2011 at 12:00 PM, Versfelt, Charles <CVERSFELT@xxxxxxxxx>wrote:

Hi all,

I'm working on a project and I have a challenge.
I know I can do this "the hard way" (over-coded, hard-to-modify) but
there's got to be a very easy, short way to do it.

I have a list of files. Let's say they're called "FILE01" through
"FILE40". They all have different formats, but there is a list of fields
that are standard to all the files, though not necessarily in the same
order, and possibly separated by other fields unique to each file. Let's
call those, "FLD01" - "FLD15." In the physical files, the common fields
have the exact same field names.

I have an input file, coming in as a CSV spreadsheet that I'm bringing in
(CPYFRMSTMF in my CL) and I have to parse out with column headings pointing
to each of these standard fields. (say the column headings are *INFLD01 -
*INFLD15).

My RPG program has an input parameter, telling me which format I want.
(Say, FILE02, or FILE27).

I have to take the file and put it in the format of the file passed by
parameter, then send it back to a new CSV in the format of the file
requested. I already know how to take the original CSV and turn it into a
file and get to all the input fields. I also know how to put them in a data
structure and send them back to CSV.

The thing is, to do this for 40 files, I need 40 data structures for 40
file formats... and since they share some field names, the compiler won't
let me simply create the data structures without prefixing. So I'd end up
with something like this

d @FILE01 e ds extname(FILE01) prefix(F01)
d @ FILE02 e ds extname(FILE02) prefix(F02)
.
.
.
d @ FILE40 e ds extname(FILE40) prefix(F40)

Then, to load my data structures prior to writing to output, (let's say my
input file has INFLD01 - INFLD15) I'd need 40 subroutines (or a long select
statement subroutines, whatever, I'll do it here as a select) to say:

Select
When infile = 'FILE01';
F01FLD01 = INFLD01;
F01FLD02 = INFLD02;
...
F01FLD15 = INFLD15;
When infile = 'FILE02';
F02FLD01 = INFLD01;
F02FLD02 = INFLD02;
...
F02FLD15 = INFLD15;
...
...
When infile = 'FILE40';
F40FLD01 = INFLD01;
F40FLD02 = INFLD02;
...
F40FLD15 = INFLD15;
Endsl;

This seems like a lot of work to move 15 fields into 1 format (which may be
any of 40) when all the files have the same field names.

I can't help thinking there's got to be an easier way to do it, like with
some kind of likeds or something.
Some way to just drop the fields into the file format I want, without
coding 40 different sets of 15 evals.

It's easy to code the "long way" of course. Just repeatedly copy paste the
code sections and change the F01 to F02... to F40, and I'm done. Which is
great if the program never changes. But if I have to add a field later I
have to add it in 40 places. I can imagine this being a bear to modify.

There just has to be an easier way. Ideas?
Thanks in advance for any suggestions.

Oh, just to make things a touch more complicated: A few of the format
files may be missing one or two of the standard fields.
In that case, if the field was in my input file I'd have to format the
fields I can and write to report the total number of records with INFLDxx
although FLDxx doesn't exist in FILEyy.

Charlie :)

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.