Compare 2 Excel Files by primary key column

Help
1

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

32
321688×951 93.7 KB
00
001712×947 96.7 KB
14
141689×901 95.4 KB
24
241671×917 90 KB
34
341715×859 68.3 KB

2

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
}

3

@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
4

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
5

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

1 Like
6

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

7

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.

8

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

2 Likes
9

Hey @Emman_Pelayo @Feryial

Thanks For Your Help.It works fine

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

Thanks

10

Something like

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

3 Likes
11

Hey @Emman_Pelayo

Thanks for your quick reply

i’ll Do & let you know

Thanks

1 Like
12

@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?

13

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

14

@Emman_Pelayo It is in txt format in a Notepad.

15

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.

16

@Emman_Pelayo thanks!

1 Like
17

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