Comparison of Two Excel Files

Help
1

Hi,

I need to match two files based on a key, say key1. I am able to match them, say read file1 output dt1. Then in a loop, I read file2 output dt2. For every row in dt2, I find a corresponding row in file 1 (dt1) and append column status = ‘1’ in file2. So far so good.

The challenge is, I am not able to append another column, say ‘found’, when I need to indicate if the corresponding row in dt1 meets the condition of dt1.columnD > 0.

That is, I want to add an indicator in file2 to track if a certain column in file1 satisfies a certain condition.

Can somebody advise me. Thanks !

2

You can use the if activity inside the for each row activity, to check whether your condition satisfies and then you can append the column with whatever value you want to, using the row index.

3

Hi @HareeshMR

Thanks. I have tried doing the IF activity inside For Each Row earlier. However, there was error if I tried to use an expression to read d2 and associate with d1.column after matching d2.key2 to d1.key1.

I tried the following expresion after reading 2nd file followed by the for loop -
for each row
If dt1.rows(RowNumber).item(“ColD”).tostring > 0 while reading file2 (dt2). That is, associate d2.key2 with d1.key1. Error points late bind variable.

The output screenshot will provide a clearer picture of the expected

Capture
Capture1693×340 32.7 KB
outcome highlighted in blue. The highligted yellow column is the search key to file1 (dt1).

Thanks again.

PS : Added file1 input too (test.xlsx)

Capture-testxlsx
Capture-testxlsx1415×510 57.7 KB

4

@Learning

can you share you xaml file.

how can you compare the String with integer like ‘0’ in your example.

before the for each loop you have read the another excel sheet.

5

Thanks @venkat4u for taking up the challenge.

I have attached the program. Note that matching of file2.key2 in dt2 with file1.key1 in dt1 works. You will see status = ‘1’ for every record matched in dt2 (output_expected.xlsx).

The challenge is in the reading of 2nd file loop, to also introduce and indicator in output_ecpected after loop to determine if file1.columnD > 0 in dt1 for the corresponding row in file1.

Capture
Capture1693×340 32.7 KB
Capture-testxlsx
Capture-testxlsx1415×510 57.7 KB
updateexcel-test.xaml (25.2 KB)

6

@Learning,

if i am understood correctly, you want to generate the email if the staffid and Column_1 both are matched else empty.

i just added try catch in your file. but can not test. since i dont hve the test file.
here is the file and test.

updateexcel-test.xaml (28.4 KB)

7

Hi @venkat4u.

Thanks for the follow up.

To clarify, the program is able to match the the Column_1 delimited in test.xlsx (file1 - table d1) highlighted in yellow with staff_id in baseline.xlsx (file2 - table d2). The matching and appending the status and email columns are all ok.

The challenge is how to append a column to indicate in file2 the row meets the matching row in file2 whose “columnD > 0”

In layman’s terms, it is actually, match two files and indicate in 2nd file a staus of the corresponding row in 1st file whose column D is greate than zero.

I have attached the two input files for your reference too. Please see also highligted column in BLUE in output_expected file. That’s the part that the program is unable to do as yet. Thanks !

baseline.xlsx (9.6 KB)
output_expected.xlsx (10.2 KB)
Test.xlsx (10.0 KB)

8

please find the xaml file and the output file…
updateexcel-test.xaml (34.5 KB)
abc.xlsx (8.2 KB)
output_expected.xlsx (7.8 KB)

9

Thanks @venkat4u.

I have checked the output. Attached is the output that should be the outcome.

The column highlighted in blue to append yet another column to indicate if the colD in text.xls is greater than zero for matching row in baseline.xlsx is not in the program logic. That’s the challenge I am facing. Thanks !

Capture
Capture1693×340 32.7 KB