Question

how could i combine two excel files in one excel file as seprated sheets

  • 9 March 2020
  • 6 replies
  • 26 views
gogopotter90
Rank
Badge +13

i have two excel writer but i want to have one excel output has both of them as seperated sheet so i used fme_feature type

but it still give me output as two excel file , i do not know why

i used attribute creator then attribute filter

to get two seperated sheet but it does not work with me

 

i used sheet name @Value(fme_feature_type)

and also edit parameter fan out and overwriting yes

 

First excel writer :

 

because i have many seperated tables so i used

output field name NO

USE ATTRIBUTES NAMES AS COLUMN POSITION YES

 

i have A B C D AND SO ON NAME OF COLUMNS so my question should i used attribute creator for fme_feature_type before connects to excel writer direct and so excel writer will recognize fme_feature_type or should i enter it in each table that i divided

 

i tried both of this metthod but i did not get my expected output

 

second excel file

utput field names yes and use attribute names as column position no

whatever i did not have many tables in this excel sheet so i do not want to use the column posiiton

but my question now how could i make both of them in one excel file as two seperated sheets and with the name of fme feature type .

 

could someone help me to put two excel files in one excel file but separated sheet

FME VERSION 2018

Thanks alot

 

 

6 replies

D
Badge

Instead of using the old writers, try using a FeatureWriter. You can then pull your connections to the "Connect Input" input of the FeatureWriter and each connected input will become a separate sheet inside the FeatureWriter.

Try it for yourself, it's quite self explanatory and I'm sure you'll be satisfied. :-)

gogopotter90
Rank
Badge +13

Instead of using the old writers, try using a FeatureWriter. You can then pull your connections to the "Connect Input" input of the FeatureWriter and each connected input will become a separate sheet inside the FeatureWriter.

Try it for yourself, it's quite self explanatory and I'm sure you'll be satisfied. :-)

thanks for ur suggestion it is working perfect but i tried to use attribute to make the output excel file name as attribute it does give me any output.

could u tell me how could i rename the excel output file depends on attribute and also i want to add date beside the name .i tried using fme_feature_type as attribute name but it gives me nothing

D
Badge

thanks for ur suggestion it is working perfect but i tried to use attribute to make the output excel file name as attribute it does give me any output.

could u tell me how could i rename the excel output file depends on attribute and also i want to add date beside the name .i tried using fme_feature_type as attribute name but it gives me nothing

In the dialog for the FeatureWriter, click the down-arrow to the right of the Dataset field and select Open Text Editor.

In the text editor you can find all your attributes in the list on the left, under FME Feature Attributes. If you have defined your filename as an attribute, this is where you can pick it by double-clicking it.

And then you can scroll down to Date/Time Functions (still in the text editor) and double-click DateTimeNow and add ".xlsx" at the end to get a filename that looks like this:

@Value(<your_filename>)@DateTimeNow().xlsx

That's just a simple way to do it. By using transformers, e.g. the AttributeCreator, you can shape your filename exactly as you want it as an attribute, by using string functions and date formatters, and then use that as filename in the FeatureWriter.

gogopotter90
Rank
Badge +13

In the dialog for the FeatureWriter, click the down-arrow to the right of the Dataset field and select Open Text Editor.

In the text editor you can find all your attributes in the list on the left, under FME Feature Attributes. If you have defined your filename as an attribute, this is where you can pick it by double-clicking it.

And then you can scroll down to Date/Time Functions (still in the text editor) and double-click DateTimeNow and add ".xlsx" at the end to get a filename that looks like this:

@Value(<your_filename>)@DateTimeNow().xlsx

That's just a simple way to do it. By using transformers, e.g. the AttributeCreator, you can shape your filename exactly as you want it as an attribute, by using string functions and date formatters, and then use that as filename in the FeatureWriter.

could u provide small workspace include two sheet one with seperated tables has empty rows and one without ,and the important for me how to create name and date of excel file .bcs i spent many hours on that but no success.Thansk alot

D
Badge

could u provide small workspace include two sheet one with seperated tables has empty rows and one without ,and the important for me how to create name and date of excel file .bcs i spent many hours on that but no success.Thansk alot

When you use a filename consisting of an attribute value and/or for example a timestamp, it is important that the resulting file name is the same for both sheets. So in the attached example I have formatted the timestamp to year/month/date, otherwise, since the two inputs have timestamps that differ by some seconds, they would generate two files instead of two sheets.

 

Hope this helps!

spreading_sheets.fmw

gogopotter90
Rank
Badge +13

could u provide small workspace include two sheet one with seperated tables has empty rows and one without ,and the important for me how to create name and date of excel file .bcs i spent many hours on that but no success.Thansk alot

Thanks alot it is working perfect nw

Reply


Community Stats

28,743
Posts
109,582
Replies
36,583
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.