Can I use an excel file twice in one Talend job?

Question

I'm trying to upload data from an excel spreadsheet into Salesforce with Talend. The Excel spreadsheet has multiple sheets, which I can access through different schemas. The opportunites input works, while the accounts input does not, even though I'm using the same input filepath of ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")).

How can I restructure my job so that I can correctly use the Excel sheet twice?

Answer 1

Marcus Rickert hit the nail on the head with his comment, you've created a race condition as the lookup data is loaded at the start of the subjob.

There's a couple of solutions to this:

As per my comment, you could have an intermediary step that stages the data by pulling the data out of the file and then making that accessible to your job in a way that doesn't rely on your lookup being defined by a globalMap variable that is only defined after the lookup should be started.

Or, by far and away the easiest way but potentially impacting on performance, you could just change the lookup model in the tMap to use "Reload at each row". This will make it reload your entire resource every row coming in from the main which will obviously have some performance issues but they may be marginal or not a problem for you. My test job ran through several iterations of a dozen lines each in pretty much the same time as the default "load once" lookup model. This will probably be O(N) though so it depends on your data volumes.

You can find the lookup model option the join settings of the tMap: