Skip to main content
Question

Write many excel files using a counter value in the file name without including the counter as an attribute in the output

F
Forum|alt.badge.img

Hello!

 

I have a workbench in FME Desktop 2021.2 where I need to write out several XLSX files from the same dataset, with exactly 1500 rows in each, and each one named FileName_1.xlsx, FileName_2.xlsx, FileName_3.xlsx, etc.

 

I have managed to do this using a Counter, and ExpressionEvaluator. The ExpressionEvaluator takes the attribute from Counter (_count) and creates a new attribute called "_result" using this expression:

 

@floor(@Value(_count)/1500) + 1

 

So that gives me the sequential file numbers that I want to append to the file names based on 1500 rows in each one.

 

I can do this if I include the "result" attribute in the Excel writer, and use a FanOut Expression like this:

 

FileName_@Value(result).xlsx

 

This produces the Excel files with the correct file names, with 1500 rows per file.

 

However, my question is, how can I exclude this "result" attribute in the Excel files? I don't want that attribute being written out. If I remove it, there is then no attribute for the Fanout expression to operate on.

 

I thought about putting this "result" value in a variable, but I cannot seem to access this variable in the FanOut Expression. Any help would be appreciated.

 

Thanks

8 replies

M
Forum|alt.badge.img+2
  • markatsafe
  • February 2, 2022

@firsttube​ I think you must have Attribute Definition: Automatic set on the Excel Writer Feature Type User Attributes TAB. Switch to Manual and you'll have more control over the attributes you're writing out.

dialog

ebygomm
Influencer
Forum|alt.badge.img+38
  • ebygommInfluencer
  • Influencer
  • February 2, 2022

You don't need to remove the attribute, just make sure it is not defined as an attribute in the writer

 

image

F
Forum|alt.badge.img
  • firsttube
  • Author
  • February 2, 2022
markatsafe wrote:

@firsttube​ I think you must have Attribute Definition: Automatic set on the Excel Writer Feature Type User Attributes TAB. Switch to Manual and you'll have more control over the attributes you're writing out.

dialog

Thanks @Mark Stoakes​  I do have the Attribute Definition set to Manual.

F
Forum|alt.badge.img
  • firsttube
  • Author
  • February 2, 2022
ebygomm wrote:

You don't need to remove the attribute, just make sure it is not defined as an attribute in the writer

 

image

Hi @ebygomm​ , please clarify how to "make sure it is not defined as an attribute in the writer"

 

If I disconnect the "result" attribute, the files don't fanout.

 

thanks!

mark2atsafe
Safer
Forum|alt.badge.img+44
  • mark2atsafeSafer
  • Safer
  • February 4, 2022

Huh! I'd be quite shocked if it failed to write any data if the attribute were missing.

I did a very quick video demo here: https://www.screencast.com/t/CmGgjiZS

I hope this helps.

M
Forum|alt.badge.img+2
  • markatsafe
  • February 4, 2022

@firsttube​ This should be working for you. I've attached a small example. I'm using _creation_instance to fan out each file, but _creation_instance isn't written to the spreadsheet (as per @ebygomm​ 's image). Workspace attached (FME 2021.2)

F
Forum|alt.badge.img
  • firsttube
  • Author
  • February 4, 2022
mark2atsafe wrote:

Huh! I'd be quite shocked if it failed to write any data if the attribute were missing.

I did a very quick video demo here: https://www.screencast.com/t/CmGgjiZS

I hope this helps.

Thank you @mark2atsafe​ !! Your video made me realize that I was using the "result" attribute in the writer for the fanout parameter, but I should have been using the "_result" attribute created in the ExpressionEvaluator.

imageimageOnce I changed the Fanout expression to @Value(_result).xlsx and removed the "result" attribute from the Excel writer, it worked!

mark2atsafe
Safer
Forum|alt.badge.img+44
  • mark2atsafeSafer
  • Safer
  • February 7, 2022
firsttube wrote:

Thank you @mark2atsafe​ !! Your video made me realize that I was using the "result" attribute in the writer for the fanout parameter, but I should have been using the "_result" attribute created in the ExpressionEvaluator.

imageimageOnce I changed the Fanout expression to @Value(_result).xlsx and removed the "result" attribute from the Excel writer, it worked!

Excellent. Glad it's working for you now.

Reply


Helpful Members This Week

Community Stats

31,323
Posts
118,784
Replies
38,912
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.