How to compare a csv file and excel file to identify duplicates?

I am new to Alteryx. I wanted compare a csv file with 136 characters with an excel worksheet to identify lines with common identifier. The output should be similar to the csv input file. But it should contain only the details of common identifiers. Please help. I am attaching samples.

As per the sample : there is a common identifier between csv SAMPLE and excel sample that is '00012-0123-45 with name ABCDE. So the output should be the eliminated csv file with no second line that is:

QTY0001205789622021STUVW00000001.23400000000000123.0000000000009999.99000000020000000002379.670000000000000.000000000002345.78.

The text file should only contain first line of SAMPLEs.

Workflow

Accepted answers

@Sunithaprdp you need to take the common fields from the excel sheet and check against the .csv fields using the below filter formula

Contains([Field_1], [NAME]) && Contains([Field_1], [IDENTIFIER])

commonfield_Compare.yxmd

@Sunithaprdp Please make sure the First Row Contains Field Names is unchecked .

@Sunithaprdp please check your output tool configuration

1, you are getting the output in the same folder because the CSVOutputName is given as ""./Output/"+[FileName]+"_Final.csv""

give the full path by replacing ./Output

2. getting your output in xlsx format check your output settings

All comments

@Sunithaprdp using a join tool will solve your problem. If you provide some sample data we can help you on this.

Yes, I have provided a sample csv and sample excel worksheet. Also has mentioned the desired output. I have edited my question. Now would this be helpful to provide a workflow that suits the output. The output should be in .csv format

@Sunithaprdp please provide some more data, may be redesign of the workflow based on the data might solve the issue

Sure I will modify the sample sheets and resend again. thanks a lot for your helping hands.

I am providing three sample data.

1) Sample worksheet

2)Sample text file

3) Desired output text file (in which i have removed unique data from the text file and only common NDC 11 data compared with the sheet has kept - manually)

Requirement: Sample worksheet and sample text file should be compared. There is a common identifier named NDC-11 . We should compare that. We should only get data of common NDC-11 in the desired output. In short we should identify duplicates among the worksheet as well as text file and should delete off the unique values from the text file which would be the output.

Thanks in advance!

Desired Output.txt

SAMPLE - Copy.txt

@Sunithaprdp can you add a unique tool. let me know if it is still not working , will re-design the workflow

commonfield_Compare.yxmd

No unfortunately its not working. multiple lines are not getting but we are not receiving desired output wherein which the common NDCs are picked up. we are receiving all lines as such in the input text file with a header Field 1

@Sunithaprdp Do you have duplicate entries in your text input file?

@Sunithaprdp please try this option and let me know

commonfield_Compare_FindReplace.yxmd

Thank you so much it worked! but it is not coming up quarter wise.

like 22021 together and 32021 together. Instead it is coming up in the jumbled format. Also in the output file we are getting a header Field1 .

Is it something which we can do?

Can you please help me understand how to do and the workflow pattern.

Though it is jumbled the output is desired one. can we find solution to this too? Please advise.

@Sunithaprdp added a sort tool and fixed the heading issue

commonfield_Compare_FindReplace.yxmd

I tried this. However the result is the same:

Field_1 : it is there in the first line of text file and quarters we are receiving like as such :

22021

32021

32021

22021

It would have been better if we get the result without Field_1 in the beginning and quarters in the lines as

22021

22021

32021

32021 likewise

@Sunithaprdp the header "Field1" will not appear in the output csv file. I also updated the workflow for ordering the output file. Please try now

commonfield_Compare_FindReplace.yxmd

@Sunithaprdp Please make sure the First Row Contains Field Names is unchecked .

Sure I will try and then message the result.

It is successful! Thank you so much ! I am going to test with some more additional data.

Will try to learn why each tool is used and get back to you for help.

Thank you so much for helping me with this.

I have a question, in case if we have multiple files for different states. If we want to keep each file to a shared INPUT folder and we need alteryx to pick those files one by one and then provide an output respectively. What can be done in such a case?

@Sunithaprdpquestions

1. What about the lookup excel file? should that also will be separate for each input file?

2. Are all the .csv files are having the same schema (same columns)?

3. Should the output file also created separate for each input file?

Actually we do have excel sheets for different programs say :
ProgramA, ProgramB, ProgramC.

Similarly text files would be for those programs.

However as I have mentioned on samples there is no such field wherein which the program names are mentioned.

But we should get output as such for each programs with specific names like for eg:

1Q2021 Program A, 1Q2021 Program B...

My question is : Is it possible to save all text files into a common Text File Input Folder and all excel sheet to a common Excel Input Folder and to receive an output specific to each program with respective naming convention?

@Sunithaprdp yes you can use the directory tool to get the files names from the folder. To get only the .csv files you need to configure the directory like below. Same for the .xlsx files

Sorry I didn't understand. So in the existing workflow we have to put directory before Output Icon?

How to get it specific for each programs as the program name is not mentioned on the input text file as well as the input excel worksheet?

So is it possible to navigate the output to a folder named OUTPUT? If then what should be the file name that we should give there as we need multiple output files for each programs with a single workflow?

@Sunithaprdp to answer your questions

1. So in the existing workflow we have to put directory before Output Icon? No

the directory tool is to read all the input files. so you need to use them instead of the input tool, this required some additional changes to the existing workflow as well. is there any mention of the programs in the names of the input files? or how you are identiying the given files is from which program?

2.So is it possible to navigate the output to a folder named OUTPUT? yes you can,

3.if you can identify these files based on the program name, then I think we can use those names for the output files as well

Yes there is a standard naming convention:

For eg:

In case of excel it would be : 1Q21 Program A Worksheet.xlsx

In case of text file it would be : 1Q21 Program A _ 12345 and 1Q21 Program A_45678

for Program A : excel worksheet would be the same though there would be different text files.

Likewise we would have 3 Programs or more.. wherein which respective excel files would be compared with multiple text files with different labelers for a specific program to get desired output for that program

@Sunithaprdp using one program we can make read all the input text files and the excel files then do the compare based on the requirements. This can be achieved using some other tools. The program names can be extracted from the file name. All these options available in the directory tool. If you provide some test files and the expected output files I can help you on that

Hi,

I am attaching few samples. Please consider this as two programs likewise we would get multiple programs. So what I am looking onto is that : There would be an 'INPUT' folder which consists of two subfolders wherein which in one subfolder we can copy all desired excel worksheets and to the other subfolder we can copy all text files.

So in short 2 subfolders named : Worksheet and Text (For example)

Then we will have one 'OUTPUT' folder wherein which we would receive desired output files with respective program names.

Samples attached :

Considering as 2 different programs AB PROGRAM & CD PROGRAM with 2 unique program numbers and one specific worksheet to compare

Wanted to get desired output for each program , each program number like 1Q21 AB PROGRAM FINAL_12345/ 1Q21 AB PROGRAM FINAL 56798/ 1Q21 CD PROGRAM FINAL _12345/ 1Q21 CD PROGRAM FINAL _56798

1Q21 AB PROGRAM WORKSHEET.xlsx

1Q21 AB PROGRAM_56798.txt

1Q21 CD PROGRAM _56798.txt

1Q21 CD PROGRAM WORKSHEET.xlsx

1Q21 AB PROGRAM_12345.txt

Hi,

I am attaching few samples. Please consider this as two programs likewise we would get multiple programs. So what I am looking onto is that : There would be an 'INPUT' folder which consists of two subfolders wherein which in one subfolder we can copy all desired excel worksheets and to the other subfolder we can copy all text files.

So in short 2 subfolders named : Worksheet and Text (For example)

Then we will have one 'OUTPUT' folder wherein which we would receive desired output files with respective program names.

Samples attached :

Considering as 2 different programs AB PROGRAM & CD PROGRAM with 2 unique program numbers and one specific worksheet to compare

Wanted to get desired output for each program , each program number like 1Q21 AB PROGRAM FINAL_12345/ 1Q21 AB PROGRAM FINAL 56798/ 1Q21 CD PROGRAM FINAL _12345/ 1Q21 CD PROGRAM FINAL _56798

1Q21 AB PROGRAM WORKSHEET.xlsx

1Q21 AB PROGRAM_12345.txt

1Q21 AB PROGRAM_56798.txt

1Q21 CD PROGRAM WORKSHEET.xlsx

1Q21 CD PROGRAM _56798.txt

Below is the attached pending file of a test program.

Could you please help me to figure out on this.

Thanks in advance!

1Q21 CD PROGRAM_12345.txt

@Sunithaprdp sure, I will work on the workflow and will let you know if there are any questions

Thank you so much!

@Sunithaprdp Are you expecting 4 output files based on the file name?

@Sunithaprdp Steps to follow before executing the workflow

1. update your input folder/CSV file folder in the first directory tool

2. update your xlsx input path in the second directory tool

3. for the output please mention your ourtput folder path in the formula tool

Im expecting some differences in the output. Please review and let me know what are the issues, will fix one by one

commonfield_Compare_FindReplace1.yxmd

Yes , likewise there will be multiple files with a unique number to each programs. Here with reference to the examples given we should have 4 output with the naming convention:

1Q21 AB PROGRAM FINAL_12345/ 1Q21 AB PROGRAM FINAL 56798/ 1Q21 CD PROGRAM FINAL _12345/ 1Q21 CD PROGRAM FINAL _56798

Sure I will run and then let you know the results.

@Sunithaprdp made some changes in the workflow, so that you will not get the extra fields in the output files

commonfield_Compare_FindReplace1.yxmd

Thank you so much, I will run this in some time and message the result. Thanks a lot!

I am getting an error corresponding to the second icon of both text and excel workflows. I mapped path corresponding to the first directory tool for each respectively.

I am getting an error corresponding to 'Find Replace' Tool

I am getting an error corresponding to the output icon after mapping the desired output location to the formula icon.

Could you please advise on the next action to be taken care off?

Thanks in advance!

@Sunithaprdp please make sure the direcotry tool has the correct path given, What is the output you are getting from the two direcotires ? are you seeing any output like filename, full path etc?

The directory tool is mapped with correct location, still I am receiving this error:

@Sunithaprdp can you check your find and replace has the below options selected

Hello,

I checked, my tool does not have those options . It is blank. What is the next action that I should take ?

Looking forward to your advise.

Thanks in advance!

When I run, I am getting an error like
No Fields selected to append.

@Sunithaprdp please check the FindReplace tool setting

Yes now it worked!! 🙂

But I am not getting output in csv format and it is not getting popped up in the respective output folder.

@Sunithaprdp that means there is an issue with your second directory. It should return some data. please check the below part is getting data

I again mapped second directory to the Worksheet Input folder. I am getting output in excel format and it is not popping out as a text file. Also the output (which is in excel format) is getting copied to the main folder.

@Sunithaprdp please check your output tool configuration

1, you are getting the output in the same folder because the CSVOutputName is given as ""./Output/"+[FileName]+"_Final.csv""

give the full path by replacing ./Output

2. getting your output in xlsx format check your output settings

Hi,

The configuration seems to be as shown on the screenshots above.

I am attaching screenshots of how my workflow is showing:

Let me know if this works? Thanks in advance.

Thank you so much for your valuable support on this, it is successful!!

To open two Excel files side by side, do the following:

Open the workbooks you want to compare then Go to the View tab, Window group, and click the View Side by Side button. That's it!

By default, two separate Excel windows are displayed horizontally.

To split Excel windows vertically, click Arrange All button and select Vertical:

As the result, two separate Excel windows will be arranged side by side. Thanks to .Avs Series

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703