Skip to main content
Solved

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


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_

 

View original
Did this help you find an answer to your question?

3 replies

ctredinnick
Supporter
Forum|alt.badge.img+18
  • Supporter
  • 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
Evangelist
  • 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.


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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings