Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Transformers
  • Want to read in a varying number of excel sheets with different schemas then write out into a single excel workbook, fanning out by dataset. The dynamic reading is the issue.

I have a report coming out of an out-of-the-box Vertigis Studio workflow that is written out as multiple excel files. Each has the title Results (number) e.g. Results (1), Results (2). (The dataset name is exposed as the tab name, for some reason and not the file name). If I were reading a group of spreadsheets with the same structure, I would just set the reader to read *.xlsxw, and it would be non problem. However - here I have differing attribute schemas.

 

The datasets called by each report vary depending on a spatial interaction, but come from a finite list of possibilities. And as I say, the attribute schemas can vary.

 

I need to read all files of the output, expose attributes and write out into a single excel sheet, fanned out by dataset (presumably using the FME basename - is it possible to grab the tab title instead?)

 

I have done the reading thus far using a Reader set as pic attached ...however, this isn't dynamic. Moreover, this workspace needs to be usable by a relative FME novice.

 

Then I need to write out to separate excel tabs. I have a writer set to fanout by fme basename. Although the translation succeeds, there is no output file.

Would welcome any hints as to how to proceed a) to make the reading able to cope with changing number of files and b) to get writing to work.

Hi @paul_c_2​, getting the Excel Reader working in a dynamic workflow takes a couple additional steps.

When adding a new Excel Reader to your workspace, go to the parameters. In the window that opens, click on the Select button below the Sheets to Read section and select None. This will allow you to read in all sheets from all incoming excel files.

image 

If we didn't select this parameter, then the reader would scan your first excel file's schema and only read in sheets with the corresponding name.

At the bottom of the parameters window, you can also set Additional Attributes to Expose to fme_basename. In this case, fme_basename = the source excel file name and fme_feature_type = the excel sheet name/ tab title.

 

If you've set everything up correctly and run your reader, you will see that all attributes from all your excel files/sheets are read in at the same time, indicating a successfully configured Dynamic Excel Reader,.

image 

In your Excel Writer, you will want to set the parameters as follows:

  • Sheet Name: fme_basename
  • Schema Sources: leave as the default
  • Schema Definition Name: fme_feature_type
  • Attributes to Remove: fme_basename (otherwise it will get output into your excel file)

image 

I've attached a simple workspace demonstrating this configuration. Hopefully that helps! Let me know how that goes and if you run into any issues getting it working.

 

Regards,

Dan

Hi @paul_c_2​, getting the Excel Reader working in a dynamic workflow takes a couple additional steps.

When adding a new Excel Reader to your workspace, go to the parameters. In the window that opens, click on the Select button below the Sheets to Read section and select None. This will allow you to read in all sheets from all incoming excel files.

image 

If we didn't select this parameter, then the reader would scan your first excel file's schema and only read in sheets with the corresponding name.

At the bottom of the parameters window, you can also set Additional Attributes to Expose to fme_basename. In this case, fme_basename = the source excel file name and fme_feature_type = the excel sheet name/ tab title.

 

If you've set everything up correctly and run your reader, you will see that all attributes from all your excel files/sheets are read in at the same time, indicating a successfully configured Dynamic Excel Reader,.

image 

In your Excel Writer, you will want to set the parameters as follows:

  • Sheet Name: fme_basename
  • Schema Sources: leave as the default
  • Schema Definition Name: fme_feature_type
  • Attributes to Remove: fme_basename (otherwise it will get output into your excel file)

image 

I've attached a simple workspace demonstrating this configuration. Hopefully that helps! Let me know how that goes and if you run into any issues getting it working.

 

Regards,

Dan

Thanks Dan. I note I could only get that to work by selecting all files in the source location and not just using a wildcard, which surprised me. The number and the selection of FME basename/feature type of source files will vary. What I want to do is use a published parameter for the folder location and get a Reader or Feature Reader to grab all files there without having to edit the parameters on canvas or in Navigator.

Hi @paul_c_2​, getting the Excel Reader working in a dynamic workflow takes a couple additional steps.

When adding a new Excel Reader to your workspace, go to the parameters. In the window that opens, click on the Select button below the Sheets to Read section and select None. This will allow you to read in all sheets from all incoming excel files.

image 

If we didn't select this parameter, then the reader would scan your first excel file's schema and only read in sheets with the corresponding name.

At the bottom of the parameters window, you can also set Additional Attributes to Expose to fme_basename. In this case, fme_basename = the source excel file name and fme_feature_type = the excel sheet name/ tab title.

 

If you've set everything up correctly and run your reader, you will see that all attributes from all your excel files/sheets are read in at the same time, indicating a successfully configured Dynamic Excel Reader,.

image 

In your Excel Writer, you will want to set the parameters as follows:

  • Sheet Name: fme_basename
  • Schema Sources: leave as the default
  • Schema Definition Name: fme_feature_type
  • Attributes to Remove: fme_basename (otherwise it will get output into your excel file)

image 

I've attached a simple workspace demonstrating this configuration. Hopefully that helps! Let me know how that goes and if you run into any issues getting it working.

 

Regards,

Dan

One more question, Dan - is there an upper limit on the number of excel workbooks / sheets that FME can read in?

One more question, Dan - is there an upper limit on the number of excel workbooks / sheets that FME can read in?

@paul_c_2​ apologies for the delayed reply. To answer your first question regarding selecting all files in a source location, this should be possible with a wildcard. You can first setup a File/Folder User Parameter that allows the user to select a folder containing their excel files.

image 

You can then add a FeatureReader and configure the dataset path to your User Parameter + the *.xlsx wildcard so that all excel files within the selected folder are read. If you opt to use the FeatureReader over the regular Excel Reader you'll need to set your Output Ports parameter to Single Output Port. See the screenshot below for an example.

image 

As for limits on the number of excel workbooks / sheets that FME can read, the only limitations would be your machine's memory & storage capacity as well as the limitations set by Microsoft on the Excel sheets (I believe you can have a max of 255 sheets in a single Excel File).

Reply


Community Stats

30,108
Posts
114,295
Replies
37,743
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.