combining files ignoring duplicates

[monahanks]

Hi,
is there a way or an example) of doing the following:
I have 2 text files with 3 columns each - the date, a store number, and a count
I need to combine these 2 files into a single (third) text file, but I cannot have duplicate store number records in the result file.
My first thought is to load all data from one file into the new file, and then load data from the second file but ignore any duplicate store records. So basically, the third file should have the store number as the "key".

Also, if I can, the transaction being discarded (the duplicate) needs to be written to an error file.

Any quick thoughts or hints? would it be easier to have the third file as an .xlsx file?

TIA,
Kevin

[Support_Rick]

Kevin,

There are a couple of options here ...

Since you're connected to an iSeries, you should be able to take advantage of QTemp and SQL to get this done. The following pseudo code should give you what you're looking for.

Code: Select all

---

Read File1 (rowset.File1)
Read File2 (rowset.File2)

SQL -
   Create Table QTemp.File1 as (c1, c2, c3)
   Create Table QTemp.File2 as (c1, c2, c3)
   Create Table QTemp.File3 as (c1, c2, c3)
   Create Table QTemp.Errors as (c1, c2, c3)
   Insert rowset.File1 into QTemp.File1
   Insert rowset.File2 into QTemp.File2
   Insert into QTemp.File3 (Select * from QTemp.File1)
   Insert into QTemp.Errors (Select * from QTemo.File2 where "key" exists in QTemp.File1)   
   Insert into QTemp.File3 (Select * from QTemp.File2 where "key" does not exist in QTemp.File1)

Select * from QTemp.File3 (Final File)
Create CSV/Txt/Excel, etc...


Or If at least one of the files is under 1000 records, Write the larger file to your third file. Read the smaller file, then in a for Each loop - use the search and replace to check the third file for the current filename. If it's there, skip, otherwise add it to the third file. It is up to you to determine the file size you feel comfortable using as a sizelimit. Keeping in mind, this will be some intensive looping and might take a bit of time to complete, depending on file size.