Power Platform Community Forum Thread Details

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?

Categories:

Power Automate Desktop

Hi @Ronyn77 ,

If you are aware of excel as database that will be more easy for you.

you can store all data into one excel i mean country name and code.

Then you write simple query

Here tblExceldata variable will contain your country name, now you will get Country name .

SET strCountryode TO $'''this came from regex'''
@@copilotGeneratedAction: 'False'
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Riyaz\\Downloads\\Excel A.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Statement: $'''SELECT Country name FROM [sheet1$]
where country code =\'%strCountryode%\'''' Timeout: 30 Result=> tblExcelData

Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Center

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

Regards,
Riyaz