I have 2 excel files - one with extracted data from PDF invoices (EXC1) and another one which is a CSV downloaded from a invoice manager (EXC2). Both of these have 2 corresponding columns to identify each row/invoice, so the key of each row is like a compound key made of both of these columns (“Comerciante” & “Nº Fatura”).
I think I have the logic of the sequence, I just don’t know what activities are best suited for this particular use case:
(1st IF) I want to read the data from EXC2 and check if the same row exists on EXC1:
- if it doesn’t, I want to add that row to a new EXC3
- if it does, I need to read each cell of that row in EXC1 and check if there are any empty/null cells (2nd IF)
(2nd IF) When iterating through the row cells:
- if the cell is empty or null, I want to fill it with the data of the same column of EXC2
- if it’s not empty, I need to check if the data of EXC1 cell is equal to EXC2 cell (3rd IF)
(3rd IF) Checking if cell data is equal:
- if it is equal, do nothing
- if it is different, update/replace with cell data of EXC2
After iterating all the cells in If (1), I want to add that EXC1 row to EXC3 aswell.
I may need to color code from which IF the rows were added, pointing out that for example row1 was added from EXC1 and row2 from EXC2, etc… Also may need to do the same to the updated cells on the 3rd IF.
EXC1 has a set of columns and EXC2 has a different set of columns but most of them match.
What I pretend is this type of JOIN as if Table A was EXC1 and Table B was EXC2, the intersection is basically the matching columns:
Here’s what I have so far:
- Compararecorrigir.xaml (26.0 KB)
And here’s some files with dummy data if needed:
- EXC1.xlsx (9.7 KB)
- EXC2.csv (10.0 KB) (Keep in mind this file is supposed to be a .csv, the format is not supported when uploading to this thread so I put it as a .xlsx)
Thanks in advance, still trying to learn Studio’s activites.