combining files ignoring duplicates

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
2 posts Page 1 of 1
Post Reply

monahanks

Posts: 41
Joined: Wed Mar 30, 2011 10:19 am
Post by monahanks » Thu May 21, 2015 2:47 pm
Quote
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
Top

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
Post by Support_Rick » Tue May 26, 2015 11:43 am
Quote
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.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Top
Post Reply
2 posts Page 1 of 1
Return to “Community Forum”