Enhancing Invoice Mapping to Excel with Country Codes in PAD: Seeking Suggestions

[Ronyn77]

Hello, I'm seeking suggestions on how to enhance my process. I'm currently mapping invoices from PDF to Excel. The invoices are complex, involving numerous foreach loops, regex expressions, and if statements. My main concern lies with determining the country of origin for each invoice item. These are represented by three-digit codes, such as 001 or 002, with a corresponding index at the bottom of the invoice (e.g., 001 for Germany, 002 for Italy, etc.). I can match these codes to map the correct country of origin for each item. However, I need to go a step further and use the ISO alpha-2 country codes instead of the country names. This means using "DE" for Germany, "IT" for Italy, and so on for each country listed. I have the list of countries and their codes in an Excel sheet, organized in two columns: the country name and its code. How can I incorporate this list into PAD to output the final result in Excel as {[item, description, quantity, unit_price, total, country_code]} for each item row? Any ideas?

[Ahammad_Riyaz]

Hello @Ronyn77 ,

2 methods are there.

1st try launch excel and loop and put condition.

2nd use excel as database and need to write execute query, this will be more efficient.

@@copilotGeneratedAction: 'False'
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%strDataFolderPath%\\BusinessConfig.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Statement: $'''SELECT name FROM [Config$]
where country code=\'001\'''' Timeout: 30 Result=> tblConfigData

Here for excel as a database if required you need to be installed oledb driver with 64bit.

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

[Deenuji]

@Ronyn77 

Would you mind providing some screenshots or a sample PDF so we can better comprehend your use case? After reviewing your data in the PDF, we can offer more appropriate suggestions.

In the meantime, you could consider the following approach: read the country code data from an Excel file and store it in a datatable. Then, when you extract the country code from the PDF, you can use it to find or replace in the datatable action. Once you find the appropriate country name, you can update your original datatable with the country code and then write the updated datatable back into the Excel file.

Thanks,
Deenuji Loganathan
🤖Automation Evangelist
Deenuji - Follow me on LinkedIn

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🌟

[Ronyn77]

For your reference, here below a couple of screenshots from the invoice : 

On the next page there is the information regarding 002

I've already extracted and matched the information, so the datatable currently displays 'Germany'. While I've considered the suggestion offered, it doesn't entirely appeal to me. Is there a quick method to convert Excel data from a 'country, countrycode' format to a structured array like `[{Country,CountryCode}, {Germany,DE}, {Italy,IT}, ...]` and integrate it into the PAD flow? This would eliminate the need to read from Excel and manually input each row.

Alternatively, I've thought about adding a helper column in Excel using a VLOOKUP or INDEX-MATCH function to fetch the country code from a second sheet. This would auto-populate the country codes next to countries like Germany, Italy, and Argentina. However, this approach requires an extra step to implement in PAD.

Ideally, I'd like to hide the country column afterwards, leaving only the country code column visible. Is it possible to hide a column in Excel via PAD? Another, slightly more complex option involves pasting all information into Excel, copying and pasting the country code column with values to remove formula dependencies, and then deleting the original country column. Can PAD accomplish these tasks?

[Deenuji]

@Ronyn77 

If you want to avoid excel then you create all country name and code in JSON format and convert as custom object then you can use power fx enabled flow to get the required country easily as I mentioned in the below example.

And regarding the column, you can use either Macros or send key actions.

Code:

Variables.ConvertJsonToCustomObject Json: $fx'{
        "AF": "Afghanistan",
        "AL": "Albania",
        "DZ": "Algeria",
        "AD": "Andorra",
        "AO": "Angola",
        "AG": "Antigua and Barbuda",
        "AR": "Argentina",
        "AM": "Armenia",
        "AU": "Australia",
        "AT": "Austria",
        "AZ": "Azerbaijan",
        "BS": "Bahamas",
        "BH": "Bahrain",
        "BD": "Bangladesh",
        "BB": "Barbados",
        "BY": "Belarus",
        "BE": "Belgium",
        "BZ": "Belize",
        "BJ": "Benin",
        "BT": "Bhutan",
        "BO": "Bolivia",
        "BA": "Bosnia and Herzegovina",
        "BW": "Botswana",
        "BR": "Brazil",
        "BN": "Brunei",
        "BG": "Bulgaria",
        "BF": "Burkina Faso",
        "BI": "Burundi",
        "CV": "Cabo Verde",
        "KH": "Cambodia",
        "CM": "Cameroon",
        "CA": "Canada",
        "CF": "Central African Republic",
        "TD": "Chad",
        "CL": "Chile",
        "CN": "China",
        "CO": "Colombia",
        "KM": "Comoros",
        "CG": "Congo (Congo-Brazzaville)",
        "CR": "Costa Rica",
        "HR": "Croatia",
        "CU": "Cuba",
        "CY": "Cyprus",
        "CZ": "Czechia (Czech Republic)",
        "CD": "Democratic Republic of the Congo",
        "DK": "Denmark",
        "DJ": "Djibouti",
        "DM": "Dominica",
        "DO": "Dominican Republic",
        "EC": "Ecuador",
        "EG": "Egypt",
        "SV": "El Salvador",
        "GQ": "Equatorial Guinea",
        "ER": "Eritrea",
        "EE": "Estonia",
        "SZ": "Eswatini (fmr. \'Swaziland\')",
        "ET": "Ethiopia",
        "FJ": "Fiji",
        "FI": "Finland",
        "FR": "France",
        "GA": "Gabon",
        "GM": "Gambia",
        "GE": "Georgia",
        "DE": "Germany",
        "GH": "Ghana",
        "GR": "Greece",
        "GD": "Grenada",
        "GT": "Guatemala",
        "GN": "Guinea",
        "GW": "Guinea-Bissau",
        "GY": "Guyana",
        "HT": "Haiti",
        "VA": "Holy See",
        "HN": "Honduras",
        "HU": "Hungary",
        "IS": "Iceland",
        "IN": "India",
        "ID": "Indonesia",
        "IR": "Iran",
        "IQ": "Iraq",
        "IE": "Ireland",
        "IL": "Israel",
        "IT": "Italy",
        "JM": "Jamaica",
        "JP": "Japan",
        "JO": "Jordan",
        "KZ": "Kazakhstan",
        "KE": "Kenya",
        "KI": "Kiribati",
        "KW": "Kuwait",
        "KG": "Kyrgyzstan",
        "LA": "Laos",
        "LV": "Latvia",
        "LB": "Lebanon",
        "LS": "Lesotho",
        "LR": "Liberia",
        "LY": "Libya",
        "LI": "Liechtenstein",
        "LT": "Lithuania",
        "LU": "Luxembourg",
        "MG": "Madagascar",
        "MW": "Malawi",
        "MY": "Malaysia",
        "MV": "Maldives",
        "ML": "Mali",
        "MT": "Malta",
        "MH": "Marshall Islands",
        "MR": "Mauritania",
        "MU": "Mauritius",
        "MX": "Mexico",
        "FM": "Micronesia",
        "MD": "Moldova",
        "MC": "Monaco",
        "MN": "Mongolia",
        "ME": "Montenegro",
        "MA": "Morocco",
        "MZ": "Mozambique",
        "MM": "Myanmar (formerly Burma)",
        "NA": "Namibia",
        "NR": "Nauru",
        "NP": "Nepal",
        "NL": "Netherlands",
        "NZ": "New Zealand",
        "NI": "Nicaragua",
        "NE": "Niger",
        "NG": "Nigeria",
        "KP": "North Korea",
        "MK": "North Macedonia",
        "NO": "Norway",
        "OM": "Oman",
        "PK": "Pakistan",
        "PW": "Palau",
        "PS": "Palestine State",
        "PA": "Panama",
        "PG": "Papua New Guinea",
        "PY": "Paraguay",
        "PE": "Peru",
        "PH": "Philippines",
        "PL": "Poland",
        "PT": "Portugal",
        "QA": "Qatar",
        "RO": "Romania",
        "RU": "Russia",
        "RW": "Rwanda",
        "KN": "Saint Kitts and Nevis",
        "LC": "Saint Lucia",
        "VC": "Saint Vincent and the Grenadines",
        "WS": "Samoa",
        "SM": "San Marino",
        "ST": "Sao Tome and Principe",
        "SA": "Saudi Arabia",
        "SN": "Senegal",
        "RS": "Serbia",
        "SC": "Seychelles",
        "SL": "Sierra Leone",
        "SG": "Singapore",
        "SK": "Slovakia",
        "SI": "Slovenia",
        "SB": "Solomon Islands",
        "SO": "Somalia",
        "ZA": "South Africa",
        "KR": "South Korea",
        "SS": "South Sudan",
        "ES": "Spain",
        "LK": "Sri Lanka",
        "SD": "Sudan",
        "SR": "Suriname",
        "SE": "Sweden",
        "CH": "Switzerland",
        "SY": "Syria",
        "TJ": "Tajikistan",
        "TZ": "Tanzania",
        "TH": "Thailand",
        "TL": "Timor-Leste",
        "TG": "Togo",
        "TO": "Tonga",
        "TT": "Trinidad and Tobago",
        "TN": "Tunisia",
        "TR": "Turkey",
        "TM": "Turkmenistan",
        "TV": "Tuvalu",
        "UG": "Uganda",
        "UA": "Ukraine",
        "AE": "United Arab Emirates",
        "GB": "United Kingdom",
        "US": "United States of America",
        "UY": "Uruguay",
        "UZ": "Uzbekistan",
        "VU": "Vanuatu",
        "VE": "Venezuela",
        "VN": "Vietnam",
        "YE": "Yemen",
        "ZM": "Zambia",
        "ZW": "Zimbabwe"
    }' CustomObject=> JsonAsCustomObject
Display.ShowMessageDialog.ShowMessage Title: $fx'test' Message: $fx'=JsonAsCustomObject.Germany' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

Hope this helps!!

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀. If you'd like to appreciate me, please write a LinkedIn recommendation 🙏