compare two files excel

Mahdoucch03 — Wed, 23 Nov 2022 08:31:56 GMT

Hello

i have folder that contained two excel files .

first : 10-10-2021.xlsx filed : ID

second : 11-10-2021.xlsx filed :ID

-I want to compare number of ID in the first file with the second file.

-I want to count the number of ID in the second files where not exists in the first file.

-I want to count the number of ID in the first file and not exists in the second file

thank you.

Re: compare two files excel

Vegar — Wed, 23 Nov 2022 10:03:35 GMT

You need somehow add information on which file a row comes from in your load script. You could use

filebasename() as SourceFile when loading your excel file.

Consider my simplified example. Both "files" contains unique IDs except ID=3 that exists in both source files.

Data: LOAD *, '10-10-2021.xlsx' as SourceFile inline [ ID 1 2 3 ]; LOAD *, '11-10-2021.xlsx' as SourceFile inline [ ID 3 4 5 6 ];

The expressions used to calculate your three questions are visible in the image below.