Compare 2 Excel Files by primary key column

I have 2 excel files which I read through Read Range with data tables dt1 and dt2.
I have assigned primary key column “Account”
Created 2 Datatables for output “dtSame” “dtDiff”

The system should be able to read primary key from first file and compare with second file. If is it not found in 2nd file, the row should be added to a CSV file called “Difference”. If it is the same as 2nd file, it should be added to CSV called “Same”.

What should I use as condition? Please help.

![32|690

For each row in dt1
{
if( dt2.Select("[Account] = ‘"+row(“Account”).ToString+"’).Length > 0 )
{
Add Data row - dtSame;
} //With Match
Else
{
Add Data row - dtDiff;
}//Without match
}

@Emman_Pelayo hi, I have written the code, but it is telling me that a ToString should end with " . When I put the double quote, it says that ‘)’ is expected. What should I do?

Can you please look at the xaml for me?
Thanks

ReadingExcel.zip (15.9 KB)

1 Like

Update this on your If statement

dt2.Select("[Account] = ‘"+row(“Account”).ToString+"’").Length >0

Also, don’t forget to to add another column for your dtSame and dtDiff since your data source has 3 columns.

2 Likes

@Emman_Pelayo thank you thank you, you’re a lifesaver!

1 Like

Hey @Emman_Pelayo @Feryial
I have requirement as same.
I did the same but got an error
i have 2 excel file instead of csv.
is Assign activity needed?
and also got an error in if condition
Capture1

Assign activity is needed if you wish to compare through a primary key.

As for the error, I cannot see any mistake, maybe Emman can help you.

Hi,
Please make sure that you are using singlq quote ( ’ ) not ( ` )

2 Likes

Hey @Emman_Pelayo @Feryial

Thanks For Your Help.It works fine

If i have to compare multiple columns,what is the if condition?

Thanks

Something like

dt2.Select("[Account] = ‘"+row(“Account”).ToString+"’ AND [Name] = ‘"+row(“Name”).ToString+"’ ").Length >0

3 Likes

Hey @Emman_Pelayo

Thanks for your quick reply

i’ll Do & let you know

Thanks

1 Like

@Emman_Pelayo if one of the files is not an Excel. Can I put a passer which will convert any type of file to an Excel?

What are the file types are we looking at? Coz we have also read csv activity which returns datatable.

@Emman_Pelayo It is in txt format in a Notepad.

You can check if the data of your text file is comma separated so can use the read csv activity.
If ever that your data in the text file is not standard, you might do some string manipulations to clean and format your data then adding it in a datatable.

@Emman_Pelayo thanks!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.