How can I change a country name into an ISO code in Talend?

Question

I need to take information from excel worksheets and input it into custom objects in Salesforce. tFileList -> tFileInputExcel -> tMap -> tSalesforceOutput

So far everything works, but the only problem is that Salesforce requires the country ISO code instead of the full country name. How might I change the full name of the country into an ISO code and pass it to Salesforce?

I need the values in the excel sheet to not change. So in Talend, I need to somehow pass the ISO code to the Salesforce component without affecting the original sheet.

Example Input:

Name  /  Country  /  Phone
"Bob" / "Germany" / "99999"

Example output to Salesforce:

"Bob" / "DE" / "99999"

Answer 1

This is pretty simple but you'll need a list of country names vs ISO codes such as this one from Wikipedia.

Get that list in some format that can be read into Talend such as putting it in a delimited file, an Excel spreadsheet or a database table.

From there you simply need to join the data in a tMap component by joining on the country name and then make sure to use the ISO code in your tSalesforceOutput flow.

A simple, example job might look something like this:

Where our Excel data looks like:

Notice the fifth line. Great Britain is not an ISO country name (instead it's United Kingdom).

We then throw that ISO2 code list from Wikipedia into a CSV in Excel:

Make sure to properly configure your tFileInputDelimited to cater for CSVs. You'll need the default escaping and quoting options to allow for commas in the data (such as "Palestine, state of").

And then the join in the tMap is done like this:

Here we're inner joining the data on the country name fields and outputting the successful joins to the Salesforce flow. We also take an alternate flow to grab any inner join rejects for rows that don't have an ISO2 country name. This could output to another Excel file or somewhere else for manual correction or to help develop some standardisation routines that need to be applied to the Excel data prior to being joined in the future (something like a tReplace looking for "Great Britain" and replacing it with "United Kingdom").

I've outputted these flows to 2 tLogRow components to throw it to the stdout, formatted as a table: