I didn’t understand exactly your issue but maybe there is some missing information that’s why some country codes are not been updated. If you want to share you project I can try to help you out!
I would suggest you to use a different optimized approach using a dictionary that stores country names and their respective codes. By using a dictionary, you can map all country names to their corresponding codes in a clean, readable, and scalable way. This will avoid any hardcoded conditions like If(countryName = "united states", "US", countryName)
Suggested Steps:
Step 1: Initialize the Dictionary
You’ll need to create the dictionary to map country names to their respective country codes.
- Assign Activity: Use an “Assign” activity to initialize the dictionary of country names and their codes.
- In the To field, create a variable
countryCodes of type Dictionary(Of String, String).
- In the Value field, assign the dictionary as shown below:
countryCodes = New Dictionary(Of String, String) From {
{"united states", "US"},
{"united kingdom", "UK"},
{"canada", "CA"},
{"germany", "DE"},
{"france", "FR"},
{"italy", "IT"},
{"switzerland", "CH"}
' Add more country names and their respective codes
}
Step 2: Loop Through DataTable Rows
Next, loop through your existing DataTable to process each row and map the country names to the country codes.
- For Each Row Activity: Drag a “For Each Row” activity to loop through your DataTable (
dt).
- Input: Your existing DataTable (e.g.,
dt).
- Output: Each row (e.g.,
row).
- Within the For Each Row:
- Add an “Assign” activity to extract and format the country name:
countryName = row.Field(Of String)("Country").ToLower().Trim()
* **countryName** is a `String` variable.
- Add another “Assign” activity to find the corresponding country code from the dictionary:
countryCode = If(countryCodes.ContainsKey(countryName), countryCodes(countryName), countryName)
* **countryCode** is a `String` variable.
* If the country exists in the dictionary, you get the corresponding code; otherwise, you leave it unchanged (or apply any default value).
- Update the DataTable: Use an “Assign” activity or “Set Field” activity to update the row in the DataTable with the
countryCode value:
row("Country") = countryCode
Step 3: Add Data to New DataTable
If you want to create a new DataTable with the updated country codes:
- Create a New DataTable: Use the “Build Data Table” activity to create a new DataTable with the same structure as your original one.
- Inside the For Each Row Loop:
- After mapping the country name to the code, use an “Add Data Row” activity to add the updated row to the new DataTable:
- ArrayRow: Add the entire row to the new DataTable like this:
{row("Column1"), row("Country"), row("OtherColumn")}
* Or just the relevant fields like `row("Country")` if you are updating a specific column.
Step 4: Output or Export DataTable
Once the loop is finished and you’ve updated the DataTable:
- Write Range Activity (if you need to write to Excel):
- Use a “Write Range” activity to write the new DataTable back to an Excel file.
- Specify the target sheet and range where you want to export the updated data.
Complete UiPath Flow:
- Assign
countryCodes dictionary.
- Use “For Each Row” to loop through the DataTable.
- For each row:
- Get the country name in lowercase and trim it.
- Check against the dictionary and get the corresponding country code.
- Update the row with the country code.
- Add the updated row to a new DataTable.
- Output the result by writing it back to an Excel file or use the updated DataTable as needed.
Example in UiPath:
Here’s a visual flow of the steps:
- Assign Activity:
countryCodes (Dictionary initialization)
- For Each Row Activity: Loop through the DataTable
dt
- Assign Activity:
countryName = row.Field(Of String)("Country").ToLower().Trim()
- Assign Activity:
countryCode = If(countryCodes.ContainsKey(countryName), countryCodes(countryName), countryName)
- Assign Activity:
row("Country") = countryCode
- Add Data Row: Add the updated row to a new DataTable.
- Write Range Activity: Export the new DataTable to Excel (if needed).
