Here is a brief overview of what I am trying to achieve: extracting data from a PDF and matching the text in specific Excel cells, while also incorporating some logic. This step is necessary because, unfortunately, the text cannot be directly matched. In the invoice section listing the items, we have lines like :
1312.124.222.001 10 10.50
8080.080.221.002 5 19.25
7070.321.111.003 1 25.43
where the last three digits of the item number (1312.124.222.001) signify the country of origin.
At the bottom of the invoice, the information is presented in separate rows, such as
Germany 001
Italy 002
France 003
For instance, 002 corresponds to Italy. I have created a datatable to store the invoice rows, for instance :
{['ItemN', 'Q.ty', 'UnitP', 'CountryO'], ['1312.124.222', '10', '10.50', '001'], ...}
I have already acomplished the first part of the logic using regular expressions, foreach loops, and other logic.
Next, I need to replace the country of origin codes in the datatable with the actual country names, like changing 001 to Germany, 002 to Italy, 003 to France, etc.
Now, I need a bit of help to finalize the logic in the simplest possible way.
Firstly, how should I proceed with the country of origin information? Would it be better to store them in another datatable like
{['CountryCode', 'Country'], ['001', 'Germany', ...]}
and then use two foreach loops, the first to cycle through the first datatable, and after extracting the CountryO information, start a second foreach loop to search the second datatable for a match? If so, given that the previous information was in multiple rows like "Germany 001", how can I write them directly inside the datatable?
Do I need to split the text somehow, or what? It seems too complex, and I want to simplify it.
Any ideas?
