Skip to main content
Question

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

  • December 7, 2017
  • 1 reply
  • 111 views
M

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

jdh
Contributor
Forum|alt.badge.img+28
  • jdhContributor
  • Contributor
  • December 7, 2017

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


Helpful Members This Week

Community Stats

30,974
Posts
117,398
Replies
38,758
Members

Latest FME

Terms & ConditionsCookie settings

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
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.