compare two excel files

Five Stars

compare two excel files

Hello everyone, I will need your help please, I have two excel file in input, I must check for each line of the first excel file the line which corresponds to it in the line of the second excel file and that one compares with the contents of each columns knowing that I have 8 columns with compare I tried with the tmap but I can not get the right result

 

Thank you in advance for your help

Community Manager

Re: compare two excel files

Do you have a key in both the files? Something like an Id field? Are you guaranteed to have every row in both files? Is there one file that will definitely have all rows and another file that will have a subset of those row? Or could the files have very different rows (for example, file 1 has rows 1,3,5,7 and file 2 has rows 2,4,6,8)? If you can give us a bit more info, that would really help

Five Stars

Re: compare two excel files


Yes I have an identification key from this key I compare the other columns of the same line,
I'm sure to have each line in both files

I hope I am clearer

thank you for your reply
Five Stars

Re: compare two excel files

 
Community Manager

Re: compare two excel files

OK, well one way you can do it is to use a tMap. The tMap works by having a Main flow and lookup flows. One of your files will be the main flow, the other will be a lookup flow. Now this method is a simple way of doing it and will only work so long as the the main flow file has every row that is in the lookup flow. This is because the flow of data is driven by the main flow. You will want to join on your key field. Then the 8 fields you want to compare will need to be compared in tMap variables (the variables box in-between the input and output tables). These comparisons will have to be written according to the type of data and what result you want. Maybe you want simply a true for a match and false for a non-match? Maybe you want to set an overwrite priority (file 1 overwrites file 2 values)? You will need to think about that. 

 

The first thing to do is probably just to ensure you have created the join appropriately by simply outputting all columns to a tLogRow to see that everything is how you expect.

Five Stars

Re: compare two excel files


Hello,

so the best solution for you is to use the tmap ??

I do not want to make a crash, I just want to get the line as soon as I find that the value of one of the columns are not equal

thank you for your reply
Community Manager

Re: compare two excel files

Why do you think it would crash? Have you tried this and experienced a crash?

 

From what you have said, the way I would do this would be to use the tMap variables to check each column and return true for match and false for a non match. Then, in the output table, filter it with the result of the tMap variables.

 

!Var.Column1 || !Var.Column2 || !Var.Column3 || !Var.Column4 || !Var.Column5 || !Var.Column6 || !Var.Column7 || !Var.Column8

 

This will return rows which have at least one column that does not match

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog