Question

Excel Reader - Cell-Named-Range getting dropped in Excel FeatureReader

  • 3 February 2019
  • 2 replies
  • 22 views
moberdries
Rank
Badge +3

I have a workspace that reads an Excel file pulling data from a specific worksheet and a specific cell-named-range. The read is implemented as a FeatureReader (Excel) but the problem I am experiencing is the same for both a FeatureReader (excel) and a Native Excel Reader,

I am passing the name of the Excel file via a Published Parameter. I eventually want to process multiple Excel files that share a common data template. My issue is that when a NEW Excel file name is passed to the FeatureReader, the cell-named-range configuration is getting dropped and I am seeing the following warning in the logs:

2019-02-04 10:08:43|   0.7|  0.0|WARN  |Excel Reader: Found named range '_xlnm__FilterDatabase' on sheet '7 day speed and Vol Summary'. This is a Microsoft Excel internal name so FME will not show it as a sheet. If you wish to read this named range, please rename it    

My preference would be to have the Excel cell-named-range configurable as a reader parameters but currently you can only set it when you connect the reader to the file, suggesting it may be hard-linked to a single specific file only?

Anyone else had experience with this?

I have attached a sample workspace that illustrates the problem:

  • Run it without any configuration pointing to the QUARRY Excel file and it works.
  • Reset Published Parameter $(FILE_XLSM) to point to another Excel file in the (Data_Input_XLSM) folder and the cell-named-range gets dropped and it fails

 

Regard

Mike

1 Attachment

2 replies

A
Rank
Badge +10

Hi @MOberdries,

Could you try setting the Reader parameter for Multiple File Parameters > Additional File Settings and set it to "Use Current Settings"?

By default the Excel Reader Schema is fixed and based on the schema defined in the reader feature type. So by setting the above parameter, when new Excel files are being read they will be read with the same parameters (field name row/data start row etc.).

For reading named ranges, these appear as Reader Feature Types so you could create a user parameter from this.

Let me know if you are still encountering a problem.

-Andrea

moberdries
Rank
Badge +3

Thanks for that great explanation Andrea - problem solved.

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
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.