Question

How to consolidate Excel worksheets by columns ?

  • 8 August 2017
  • 3 replies
  • 0 views
C

Hi all, I am new to FME.

Which transformers should I use to solve the following problem ?

[1] The input Excel file has 3 worksheets, each with 4 columns (1 primary, 3 secondary).

[2] The output Excel file should have 1 worksheet for each secondary column, and the data should be labelled by input worksheet name.

[3] What if there are a few hundred secondary columns in each worksheet in the input Excel file ?

In other words, "transform feature types to attributes".

I was trying to use AttributeManager but did not know how to get hold of the name of the feature type.

Thank you.

* Input Excel file :

* Expected Excel file :

3 replies

T
Rank
Badge +6

Hello @chkkbim

 

 

I think this article may help get you started with your workflow:

https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

fmelizard
Rank
Userlevel 4
Badge +13

Hi @chkkbim, I've attached a workspace I've created in 2017.0. It could use some fine-tuning (for example, I didn't sort/order by Hour on write and there are duplicate transformers being used) but you can use FME to perform the task you're trying to accomplish.

consolidate-worksheets-by-columns.fmwt

takashi
Rank
Userlevel 2
Badge +17

Hi @chkkbim, I've attached a workspace I've created in 2017.0. It could use some fine-tuning (for example, I didn't sort/order by Hour on write and there are duplicate transformers being used) but you can use FME to perform the task you're trying to accomplish.

consolidate-worksheets-by-columns.fmwt

I think @TiaAtSafe's example contains basic techniques to solve the problem, but I'm afraid that the workflow could not solve the third requirement.

 

 

[3] What if there are a few hundred secondary columns in each worksheet in the input Excel file?

 

 

This is an example that demonstrates a way to solve it:

 

consolidate-excel-worksheets-example.fmwt (FME 2017.0.1.1)

 

 

Note: It assumes that the source Excel book always only has three worksheets and also the sheet names are known. If the number of source sheets was unfixed or their names were unknown, the Dynamic Schema approach should be combined.

 

Reply


Community Stats

28,729
Posts
109,527
Replies
36,565
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.