I have a XLSX file with multiple sheets (15) in and I need to get the data from each one. Is this possible within a mapper as I can see now I can pass down a variable for the sheet name but it seems within the mapper it’s only ever 1 sheet you can read from?
I’m wondering if it’s possible to get a list of all the sheet names within workflow and then put a loop on and pass in the sheet name each time?
You can take advantage of the fact that you can specify a dynamic sheet index instead of relying on the sheet names.
So let’s say you create a Runtime Parameter named SheetIndex and you set your DM configuration to use the value of that parameter as the dynamic sheet name.
In Workflow, this value is stored in JobInfo 9. So in Workflow’s Data Mapping task, you pass JobInfo 9 as the SheetIndex runtime parameter:
Now you can’t easily know how many sheets are in an XLSX file from within Workflow, but you can get around that issue fairly easily. Create a branch with a Loop plugin. Set the number of loops to a value that you know is greater than the maximum number of sheets you’ll ever run into (say, 100!).
Then use the Math Operations plugin to store the loop iteration index +1 in JobInfo 9 (you have to do this because the %i system variable is 0-based while the sheet index value are 1-based).
Last thing to do is to set the On Error properties for the Data Mapping task to “Exit branch”. That way, once the plugin reaches a sheet number that doesn’t exit in this specific data file, an error will be logged and the processing will be transferred back to the trunk.
Brilliant, thanks Phil
