Skip to main content
Solved

How to read attribute names from multiple Excel workbooks/sheets to new workbook?

  • June 6, 2022
  • 3 replies
  • 200 views
T

Hello! FME newbie here 👋

I have 100+ Excel workbooks, each of which has 2-4 worksheets in them. To prep to do some schema mapping, I'd like to read out all of the attribute names to a new spreadsheet (Excel or CSV, doesn't matter). Could anyone give me some guidance on how to do this? I've played around with the following transformers, but haven't had much luck figuring out how to use them to do what I'm hoping to do:

  • Sampler (with no header on Excel reader)
  • AttributeTransposer
  • FeatureReader
  • AttributeExposer

Any help, guidance, or links that might point me in the right direction would be much appreciated!

Best answer by ctredinnick

The transformer you're looking for is the SchemaScanner. It will return a list of all the attributes present on the input. ListExploder on that list, and there you have all the attributes.

 

If the process will be reading all the spreadsheets at the same time and you want to know which attribute came from which spreadsheet, you'll need to set it to Group By on fme_feature_type (excel sheet name) and fme_basename (excel filename) (this format attribute will need to be exposed manually on the reader, as it's not normally an attribute which is read in).

You'll also need to filter out the FME format attributes from the set of attributes, which will be anything beginning with fme_ multi_ or xlsx_

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

3 replies

ctredinnick
Supporter
Forum|alt.badge.img+19
  • ctredinnickSupporter
  • Supporter
  • 225 replies
  • Best Answer
  • June 7, 2022

The transformer you're looking for is the SchemaScanner. It will return a list of all the attributes present on the input. ListExploder on that list, and there you have all the attributes.

 

If the process will be reading all the spreadsheets at the same time and you want to know which attribute came from which spreadsheet, you'll need to set it to Group By on fme_feature_type (excel sheet name) and fme_basename (excel filename) (this format attribute will need to be exposed manually on the reader, as it's not normally an attribute which is read in).

You'll also need to filter out the FME format attributes from the set of attributes, which will be anything beginning with fme_ multi_ or xlsx_

 

david_r
Celebrity
  • david_rCelebrity
  • 8391 replies
  • June 7, 2022

And if you're not yet using FME 2021+ and haven't got access to the SchemaScanner, you can use the FeatureReader transformer and look at the "Schema" port output contents.

T
  • traitortots
  • Author
  • 2 replies
  • June 7, 2022

Thank you both so much! This helped tremendously. Still having some trouble with the overall process, but I think I'll make a separate post for that.

Community Stats

32,065
Posts
121,737
Replies
39,692
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.