Get unique values from multiple files without joining

Question

I have two types of files in the same directory that I will use for sending out letters. These two types of files have ELS and ILS in their filenames.

Before sending out letters, I must make sure that there're no duplicate records in these files. I must find out unique values from these files without joining them together because the two types of files have different layouts yet they must go through the same workflow to output letters.

Below are examples of the ELS and ILS files:

ELS_group_1.xlsx

CODEFirst_NameLast_NameProfession10238JohnWhiteTeacher12049KellyKrenDean17253Hsiu-wenChenStudent

ILS_group_1.xlsx

CODECareersLastNameFirstName39880StudentLandsbergAlan10238TeacherWhiteJohn15042ArtistDanielsLindsey

Suppose that before I have these files go through the flows that output letters, I want to ensure that there're no duplicate records in these files, distinguished by CODEs (note 10238 in both files). There could be 20 files in the directory with different numbers of ELS and ILS files. What can I do?

apathetichell · Answer

oh. interesting. Forgot the 20 file part. union is kind of the way to go. You can union and then summarize/count to see what's repeated. so what i'd do is create a field to signify what the potential workflow is and then do a groupby/count by code and see which are 2 or more.

saveeshkumar · Answer

Hi @45179902 ,

Please find this logic to get unique records and output.

step 1

step 2

With this output join to the xlsx file to get output.

Simple batch to read xlsx file .

Hope this help you.

ArtApa · Answer

@45179902 - You can use "*" or "?" masks in your Input Data tool. The easiest way to achieve that replace filename with ILS_*.xlsx and ELS_*.xlsx and then simply use a Union tool.