Question

Read Excel File with sheet names, row and column numbers?

  • 7 December 2017
  • 1 reply
  • 5 views

I have a large number (~1000) Excel files that I need to import into a database table. They are many different formats, so using a reader with a defined schema will not work. I would like to have the output contain the following attributes:

  • Filename
  • Sheetname
  • RowNumber
  • ColumnNumber (preferably as a number but could be letters as used in excel)
  • value (as text)

I've tried numerous things and searched through many posts. It seems like this should be a simple task, but the Excel Reader attempts to detect a "schema" for a sheet, and it uses values it finds wherever in the excel as attribute names. Any suggestions would be greatly appreciated.


1 reply

Badge +22

What about using the Schema Reader to read in the excel files, that will give you attribute{}.name, with the index being the Column Number, the Filename would be fme_dataset (or fme_basename if you don't want the full path), the sheet name fme_feature_type_name.

Merge that with your regular excel reader data, which should have the xlsx_row_id exposed.

Then use an AttributeExploder to get one feature per Attribute per row.

Reply