Question

merge three excel worksheet tabs into one based on header fields

  • 5 August 2015
  • 8 replies
  • 15 views

Hi, FME beginner here. 

 

 

I have an excel sheet with 3 tabs containing data, each tab's first row is the field name header. I'm looking to merge all the attributes in these three tabs into one tab where the field names are the same. I've tried using featuremerger but somehow it still only writes into separate tabs.

 

 

Any help? Thank you

8 replies

Userlevel 2
Badge +17
Hi,

 

 

The situation is not clear. Firstly, connect an Inspector to the Merged port of the FeatureMerger and run the workspace, to check whether the merging will be performed as expected.

 

 

Takashi
Nothing comes through the merged port, hence the problem. What would be the general aproach to idealy combine three separate excel files into one file using FME?
Userlevel 2
Badge +17
I think the FeatureMerger is most widely used to merge multiple tables, if the tables have common key attribute.

 

But I'm still not sure your actual situation. Could you please explain your requirement concretely?

 

It would be best if you could paste screenshots of the headers of the source tables and the desired table.

 

Badge +2
1. If you want to make 3 sheets to 1 sheet then just conenct to excel writer with input of 3 sheets. Make sure the headers are same in al the input and output. Data will be added one after another in the excel sheet.

 

 

2. If you want to merge when you have same values in first column then you have to use feature merger

 

 

Hope this helps
TABLES 1,2 AND 3: all have the same fields name headings in row 1 i.e.

 

 

TABLE 1

 

FIELD 1      FIELD 2...

 

A                  1...

 

B                  2...

 

C                  3...

 

 

TABLE 2

 

FIELD 1      FIELD 2...

 

D                  11...

 

E                  21...

 

F                  3.1..

 

 

TABLE 3

 

FIELD 1      FIELD 2...

 

X                  10...

 

Y                  20...

 

Z                  30...

 

 

the desired table would look like:

 

FIELD 1      FIELD 2...

 

A                  1...

 

B                  2...

 

C                  3...

 

D                  11...

 

E                  21...

 

F                  3.1..

 

X                  10...

 

Y                  20...

 

Z                  30...

 

 

FeatureMerge does not seem to be merging any of the table. 
Badge +3
Use the featuremerger unconditionally? (requestor = supplier = 1)

 

 

Aggregator can be used as wel. etc.

 

 

Not based on the table fields that is.
Userlevel 2
Badge +17
It's the first case in Pratap's post. You can send every feature into an Excel writer feature type without merging.

 

If the result would still be 3 sheets, you might be using Dynamic writer with default properties setting. If so, try modifying the "Sheet Name" (destination feature type name) and the "Schema Definition Name" in the writer properties, so that the detination feature type name (i.e. sheet name) for every feature would be identical.

 

e.g. (FME 2015.1)

 

Badge +3

I think some clarification is needed to confirm the type of merge you are looking for. If you want the data from 3 tabs to be merged sequentially, so the records from tab1 followed by tab2 then that would be an append operation. Then you dont want to use FeatureMerger, but just have multiple feature types with the same output feature type - arrows come from 3 source data flows and combine into one output data flow. You may need to explicitly expose, remove and redefine fme_feature_type if you are using dynamic schema.

If you want to fields from tab1 with tab2 on the same record then that is a join. That will work with FeatureMerger assuming you have a common key. If there is no common key then one could be created with row number assuming row correspondence. If the rows are not in the same order and there is no common key then a join is not possible.

Reply