Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • General
  • Write many excel files using a counter value in the file name without including the counter as an attribute in the output

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

@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

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

 

image

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

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!

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.

@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)

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!

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


Community Stats

30,171
Posts
114,537
Replies
37,835
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.