Insert New Rows BETWEEN existing Rows

[gateleys]

I have TWO flatfiles. First, inFile.txt with a column 'LIST' which is used as the primary input, and insertFile.txt which also has a single column, 'TO_BE_INSERTED_LIST'. Sample values in the two files-

Code: Select all

inFile.txt		      insertFile.txt
LIST			TO_BE_INSERTED_LIST
====			===================
Tom			 Oh! No
Dick			Not Again
Harry			
Tarzan
Jane

The transformation logic that is to be used is-
1. If LIST = 'Harry' Then insert the records from insertFile.TO_BE_INSERTED_LIST to inFile.txt, such that the output looks like-

Code: Select all

OUTLIST
=======
Tom
Dick
Harry
Oh! No
Not Again
Tarzan
Jane

Thanks.
gateleys

[ArndW]

One cannot insert into the middle of a sequential file. When programs seem to do this they actually overwrite the original and/or use interim files.

One solution that you can use is to write your data out to a temporary hashed file, adding a numeric key column containing your @OUTROWNUM value. You can then put in the logic to include values from your other text file.

Your sample output just happens to end up in sorted format - is that a coincidence? If not you can just merge and sort your files without having to add complicated DS logic.

[gateleys]

Arnd, the included list just happened to be sorted. But, what is the purpose of including @OUTROWNUM since in my actual data, the value 'Harry' could appear anywhere, and in multiple records. Further, I haven't checked, but the inFile.txt will be a pretty big file. The good side is that my to_be_included file will contain only about 10 to 15 rows (I dunno if that helps though ).

[ArndW]

Write a job that loads your to-be-included file contents to a hashed file, using a key 'B':@INROWNUM as the key and just one column with your contents.

In the same job read your source file and write to the same hashed file using the key 'A':@INROWNUM until you hit your trigger "Harry" and then change your key to 'C':@INROWNUM.

Then have a link reading the hashed file (order by your key!) and strip out the now unnecessary hashed file key and you have your ordering.