This works well for formats like shape, DWG, and geojson, but it doesn't work for Excel because the FME script that gets sent the parameter doesn't know which sheet name to open. I can't seem to pass the sheet name as a parameter, and the sheet names are different for each Excel that I sent to this script so I can't hard code it. Is there a way to send that sheet name to the FME script and have it open the Excel and read from the desired sheet name?
Thanks
You can pass the desired sheet name (feature type name) to the second workspace through the eFEATURE_TYPES] parameter, which should be published automatically when you added the Excel reader to the second workspace.
Takashi
Hi @takashi
I'm struggling to do this. I'm using the Schema Reader in one Workspace to get the file names of all the XLS files in a folder and the sheet name in each XLS (there's only one sheet in each XLS). Then I want to use the file name and sheet name as parameters in a WorkspaceRunner. However I can't find a way of making the sheet name a parameter in the second Workspace.
I've looked at the article linked to below where it refers to "Dynamic Schema" and has a screenshot showing "Workflow options" with "Static Schema" and "Dynamic Schema" but I can't find this in FME 2016.1
https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/xlsx/XLSX_reader.htm
If I edit the XLS Reader parameters in the second Workspace, I have ticked "Scan schema in dynamic workflow" under "Advanced Parameter". Is this the same thing.
If I run the 2 Workspaces, it says it's completed successfully but the outputs are not created. The log for the second Workspace says that zero features were written. I think this is because it's still using the sheet names from the XLS files I selected when building the Workspace which are different from the sheet names of the XLS files being supplied as parameters in the WorkspaceRunner.
If I just run the second Workspace, it completes and says "Translation was successful" but then FME crashes and "Error running translation" appears in the log. The output files are not generated. I tracked this down to an Inspector - I don't get the crash when I disable this but I don't know why this caused a problem. In any case, while disabling the Inspector in the second Workspace means it runs successfully on its own, it still fails when called from the first Workspace.
Please can you expand on your advice above?
I think I might have solved it, at least partially. Add the Excel Reader as a Resource (Readers > Add Reader as Resource). You can then expand Workspace Resources > [your XLS] > Parameters > Features to Read then right-click on Feature Types and select Create User Parameter. This then appears in the WorkspaceRunner parameters section in the other Workspace and you can specify the sheet name. For info, I extracted the sheet name by using Schema Reader in the first Workspace - the sheet name is in the fme_feature_type_name attribute.
However, while the Workspace reads the correct data, the log says it's failed to route it, so nothing goes to the first transformer. I'll keep trying until I find an answer or someone posts it here.
And I still can't find the mysterious "Static Schema" / "Dynamic Schema" Workflow options choice shown in the Excel Reader documentation.
OK. Using FeatureReader with the XLS name parameter as the "Dataset" value and the sheet name parameter as the "Feature Types to Read" value works, but I have to manually enter the list of columns in the "Attributes to Expose" section. Fortunately there are only 4 columns! I'll use this method unless/until a better solution is posted.