Solved

Dynamic excel sheet reading, without having to reload the reader

  • 26 September 2017
  • 10 replies
  • 58 views
J
Badge

I have set up a workspace which reads in an Excel Spreadsheet whose attributes may change everytime that the workspace is run. As this workspace is part of a larger chain of workspaces I would like to be able to run it without having to reload the reader to ensure that the latest attributes and features are being brought through each time.

I have tried setting the 'Scan Schema in Dynamic Workflow' parameter, but with no success.

Is there something I am missing?

icon

Best answer by fmelizard 26 September 2017, 20:13

View original

10 replies

fmelizard
Rank
Userlevel 4
Badge +13

Hi @johnwk,

Thanks for reporting this. This is a known issue which we are working to fix, it is particularly noticeable when using the FeatureReader.

 

 

The first hing to check, however, is that the the Additional File Handling is set to 'Apply Default Settings to Additional Files' (see below).

 

If this still persists a suggested work around is to use the Schema reader (any format) in combination with the excel reader to build the schema manually. In my screen shot below, notice that I have the output schema source set to "Schema From Schema Feature". The data from the excel file should still be being read, however, it is the schema that may not be being generated properly.

 

 

 

Let us know how this goes

 

 

jneujens
Badge

I asked a similar question not too long ago.

 

My problem was solved by using a 'generic' reader instead of an excel reader.

https://knowledge.safe.com/questions/52231/updating-reader-as-resource-for-dynamic-writing.html

J
Badge

Hi @johnwk,

Thanks for reporting this. This is a known issue which we are working to fix, it is particularly noticeable when using the FeatureReader.

 

 

The first hing to check, however, is that the the Additional File Handling is set to 'Apply Default Settings to Additional Files' (see below).

 

If this still persists a suggested work around is to use the Schema reader (any format) in combination with the excel reader to build the schema manually. In my screen shot below, notice that I have the output schema source set to "Schema From Schema Feature". The data from the excel file should still be being read, however, it is the schema that may not be being generated properly.

 

 

 

Let us know how this goes

 

 

Hi @MattAtSafe, thankyou very much for your detailed reply.

 

On your two options to try:

 

  • I checked and the Additional File Handling was already set to 'Apply Default Settings to Additional Files'.
  • I then tried using the Schema Reader as you suggest but unfortunately this didn't work for me either. In this particular workspace I am already reading in additional Excel documents which I use as the schema in the dynamic writers.
@jneujens answer below worked for me, replacing the Excel readers with Generic Readers and setting the format to Excel within that reader.

 

 

Thanks for your help, glad I wasn't being stupid and that there is actually a known issue with the Excel readers.

 

 

 

J
Badge

I asked a similar question not too long ago.

 

My problem was solved by using a 'generic' reader instead of an excel reader.

https://knowledge.safe.com/questions/52231/updating-reader-as-resource-for-dynamic-writing.html

Hi @jneujens, thanks for your reply to this. I'm happy to say that your solution worked for me, using the Generic Reader instead of the Excel reader. I did look at your question that you have linked above, before I posted this one, but it didn't work for me the first time. I think it's because there's no mention of Generic Readers in it.

 

 

Glad it's sorted though now and there is a successful work around whilst Safe fix the Excel issue.

 

D
Badge +6

I'm using 2018.1 and this still seems to be an issue. It gets me every time! For example someone modifies the headers of a spreadsheet (ex: corrects a spelling mistake) and then the workspace doesn't read the data for that column. I was under the impression that this setting (Scan Schema in Dynamic Workflow) is supposed to rebuild the Attribute list. Is that not the case? I'm finding that even if I right-click and select "Update Reader" and select "Update Reader and Feature Types", the attributes don't update. I have to also go to "Parameters" in order to see the update. There are even times where I also have to toggle the "File Names Row" and "Cell Range" back and forth for the attributes to properly update.

xiaomengatsafe
Rank
Badge +2

I'm using 2018.1 and this still seems to be an issue. It gets me every time! For example someone modifies the headers of a spreadsheet (ex: corrects a spelling mistake) and then the workspace doesn't read the data for that column. I was under the impression that this setting (Scan Schema in Dynamic Workflow) is supposed to rebuild the Attribute list. Is that not the case? I'm finding that even if I right-click and select "Update Reader" and select "Update Reader and Feature Types", the attributes don't update. I have to also go to "Parameters" in order to see the update. There are even times where I also have to toggle the "File Names Row" and "Cell Range" back and forth for the attributes to properly update.

Hi @dbaldacchino Thanks for leaving your observation here. We have a couple of ideas that' could potentially make this experience better. Unfortunately, we don't have a timeline for it yet. But we will be sure to update here, when the solution is implemented.

 

Thank you for your patience.
aaron
Rank
Badge +8

Hi @dbaldacchino Thanks for leaving your observation here. We have a couple of ideas that' could potentially make this experience better. Unfortunately, we don't have a timeline for it yet. But we will be sure to update here, when the solution is implemented.

 

Thank you for your patience.

Has this issue been addressed? I am using FME 2020.1.2.1 (build 20624) and checking Scan Schema in Dynamic Workflow does not update the attributes when they change in my Excel file.

L
Badge +1

Hi @dbaldacchino Thanks for leaving your observation here. We have a couple of ideas that' could potentially make this experience better. Unfortunately, we don't have a timeline for it yet. But we will be sure to update here, when the solution is implemented.

 

Thank you for your patience.

Hello @xiaomengatsafe​ , please, do You have any updates on this bug?

thanks :-)

daraghatsafe
Badge

Hi @aaron​ and @lazarlubomir​ thank you for your question. The Scan Schema for Dynamic Workflow parameter is used with the Single Merged Feature Type in FME Desktop, are you planning on using the excel reader dynamically?

If you would like the Attributes to reflect the changes that are made in the Excel file when reading the file in (in a non-dynamic workflow) you will need to right-click on the Excel reader in the Navigator Panel and select Update Reader, when the Update Reader dialog appears, click the Parameters button (this is required for FME to pick up the change in schema), close the Excel Parameters dialog, then select Update Reader and Feature Types.

Hope this info helps. Please let me know if you have any further questions.

aaron
Rank
Badge +8
I am looking to read Excel attributes dynamically. (I know how to update the fields manually with Update Reader.) In this case, I was reading COVID data from an online source and the fields would change occasionally. The workspace runs daily from FME Server and reading the attributes statically lead to failures when the fields changed. With a dynamic reader, it would be possible to build in some checks to allow the workspace to continue or fail gracefully. Aaron

Reply


Community Stats

28,724
Posts
109,518
Replies
36,563
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.