I need to take information from excel worksheets and input it into custom objects in Salesforce. tFileList -> tFileInputExcel -> tMap -> tSalesforceOutput
我需要从excel工作表中获取信息并将其输入到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?
到目前为止，一切正常，但唯一的问题是Salesforce需要国家ISO代码而不是国家全称。我怎样才能将国家的全称变成一个ISO代码并将其传递给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.
我需要excel表中的值不变。因此，在Talend中，我需要以某种方式将ISO代码传递给Salesforce组件，而不影响原始表。
Example Input:
示例输入:
Name / Country / Phone
"Bob" / "Germany" / "99999"
Example output to Salesforce:
Salesforce的示例输出:
"Bob" / "DE" / "99999"
1 个解决方案
#1
1
This is pretty simple but you'll need a list of country names vs ISO codes such as this one from Wikipedia.
这很简单，但是你需要一个国家名称和ISO代码的列表，比如这个来自*。
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.
以某种格式获取该列表，可以将其读入Talend，例如将其放入带分隔符的文件、Excel电子表格或数据库表中。
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.
从这里开始，您只需在tMap组件中加入国家名，然后确保在tSalesforceOutput流中使用ISO代码。
A simple, example job might look something like this:
一个简单的示例工作可能是这样的:
Where our Excel data looks like:
我们的Excel数据如下:
Notice the fifth line. Great Britain is not an ISO country name (instead it's United Kingdom).
注意到第五行。大不列颠并不是一个ISO国家名称(而是英国)。
We then throw that ISO2 code list from Wikipedia into a CSV in Excel:
然后我们将*中的ISO2代码列表放到Excel中的CSV中:
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").
请确保正确配置您的tFileInputDelimited以满足CSVs的需求。您将需要默认的转义和引用选项，以允许在数据中使用逗号(例如“巴勒斯坦国”)。
And then the join in the tMap is done like this:
然后tMap的连接是这样的:
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").
在这里，我们将内部连接国家名称字段的数据，并将成功的连接输出到Salesforce流。我们还使用另一个流来获取没有ISO2国家名的行的任何内部连接拒绝。这可以输出到另一个Excel文件或其他地方进行手工修正，或者帮助开发一些标准化例程，这些例程需要在将来加入Excel之前应用到Excel数据中(类似于寻找“大不列颠”并将其替换为“联合王国”的tReplace)。
I've outputted these flows to 2 tLogRow components to throw it to the stdout, formatted as a table:
我已经将这些流输出到2个tLogRow组件，以将其抛出到stdout，格式化为表:
#1
1
This is pretty simple but you'll need a list of country names vs ISO codes such as this one from Wikipedia.
这很简单，但是你需要一个国家名称和ISO代码的列表，比如这个来自*。
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.
以某种格式获取该列表，可以将其读入Talend，例如将其放入带分隔符的文件、Excel电子表格或数据库表中。
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.
从这里开始，您只需在tMap组件中加入国家名，然后确保在tSalesforceOutput流中使用ISO代码。
A simple, example job might look something like this:
一个简单的示例工作可能是这样的:
Where our Excel data looks like:
我们的Excel数据如下:
Notice the fifth line. Great Britain is not an ISO country name (instead it's United Kingdom).
注意到第五行。大不列颠并不是一个ISO国家名称(而是英国)。
We then throw that ISO2 code list from Wikipedia into a CSV in Excel:
然后我们将*中的ISO2代码列表放到Excel中的CSV中:
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").
请确保正确配置您的tFileInputDelimited以满足CSVs的需求。您将需要默认的转义和引用选项，以允许在数据中使用逗号(例如“巴勒斯坦国”)。
And then the join in the tMap is done like this:
然后tMap的连接是这样的:
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").
在这里，我们将内部连接国家名称字段的数据，并将成功的连接输出到Salesforce流。我们还使用另一个流来获取没有ISO2国家名的行的任何内部连接拒绝。这可以输出到另一个Excel文件或其他地方进行手工修正，或者帮助开发一些标准化例程，这些例程需要在将来加入Excel之前应用到Excel数据中(类似于寻找“大不列颠”并将其替换为“联合王国”的tReplace)。
I've outputted these flows to 2 tLogRow components to throw it to the stdout, formatted as a table:
我已经将这些流输出到2个tLogRow组件，以将其抛出到stdout，格式化为表: