Solved

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

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!

icon

Best answer by ctredinnick 7 June 2022, 06:45

View original

3 replies

ctredinnick
Rank
Userlevel 3
Badge +16

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
Rank
Userlevel 4

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

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


Community Stats

28,707
Posts
109,477
Replies
36,551
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.