Skip to main content
Solved

How do I specify an Excel filename in the writer based on the input filename?

  • December 15, 2023
  • 9 replies
  • 230 views
amandah
Contributor
Forum|alt.badge.img+2

I am creating a flow that runs a number of tests on an Excel spreadsheet and spits out the results into another Excel spreadsheet. The incoming spreadsheets will almost always be in different folders (same parent directory, but each project has its own folder within that), and I would like the outgoing spreadsheets to go to the same folder as input, and have the same filename but with the suffix of 'FME Test Results'. I am currently managing this manually, and wondered if there is a way to do it using FME.

I have found several answers that seem to get partway to my goal, but don't seem to fully get there.

Thanks,

Best answer by redgeographics

In the Navigator you'll have a "fanout dataset" option on the writer. That allows you to construct the filename based on attribute values. The (internal) attribute fme_dataset contains the original filename so something like 

@Value(fme_basename) FME test results.xlsx

should do the trick.

View original
Did this help you find an answer to your question?

9 replies

redgeographics
Celebrity
Forum|alt.badge.img+49
  • redgeographicsCelebrity
  • Celebrity
  • Best Answer
  • December 15, 2023

In the Navigator you'll have a "fanout dataset" option on the writer. That allows you to construct the filename based on attribute values. The (internal) attribute fme_dataset contains the original filename so something like 

@Value(fme_basename) FME test results.xlsx

should do the trick.

amandah
Contributor
Forum|alt.badge.img+2
  • amandahContributor
  • Author
  • Contributor
  • December 17, 2023
redgeographics wrote:

In the Navigator you'll have a "fanout dataset" option on the writer. That allows you to construct the filename based on attribute values. The (internal) attribute fme_dataset contains the original filename so something like 

@Value(fme_basename) FME test results.xlsx

should do the trick.

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\a\b\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\a\b\c.xlsx\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\a\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

redgeographics
Celebrity
Forum|alt.badge.img+49
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

fme_basename is an attribute that is automatically created, but it may be hidden. You can find it on the Format Attributes tab of a reader feature type:

imageTicking the box to expose it should do the trick, you can then use it in other FME transformers or to build that fanout path.

amandah
Contributor
Forum|alt.badge.img+2
  • amandahContributor
  • Author
  • Contributor
  • December 18, 2023
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

Thank you - that has solved the filename issue in the fanout.

I'm still having the directory path issues because the reader and writer directory paths are linked - it is either trying to save the new file inside the old file, or trying to open the directory as the file. Is there a way to disconnect the reader and writer paths from without turning off the fanout? Because I do want the directory to be the same, is it possible to get the reader to have a directory path, and then a separate field for the file (like the writer has when you do the fanout), so that the directory path part of the reader is still linked to the directory path part of the writer, but the file doesn't form part of that path in the writer? Please see the below image which hopefully explains it better than I have.

image

redgeographics
Celebrity
Forum|alt.badge.img+49
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

You can change that destination directory to whatever you like. Keep in mind that when you're dealing with Excel that parameter controls the directory and then the Fanout Expression creates the filename.

amandah
Contributor
Forum|alt.badge.img+2
  • amandahContributor
  • Author
  • Contributor
  • December 19, 2023
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

When I change the destination directory it also changes the input file name - meaning I then have a broken input. Is there a way to stop that occurring?

redgeographics
Celebrity
Forum|alt.badge.img+49
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

If you want your Excel files to appear in the folder C:\\a\\b\\ you should put C:\\a\\b\\ as the destination fanout directory.

 

Then if you want the Excel files to be named [input name] - Test results.xlsx you should put @Value(fme_basename) - Test results.xlsx as the fanout expression.

 

That destination fanout directory is not reliant whatsoever on where your input files are, you can put any path you like in there.

amandah
Contributor
Forum|alt.badge.img+2
  • amandahContributor
  • Author
  • Contributor
  • December 20, 2023
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

OK - so if mine is changing the input when I change the output that means I must have a setting wrong somewhere. Do you know what setting that would be? Sorry for all the questions, but this is driving me crazy - I feel like there is something very simple somewhere that I must be missing.

amandah
Contributor
Forum|alt.badge.img+2
  • amandahContributor
  • Author
  • Contributor
  • January 8, 2024
amandah wrote:

Hi,

 

I have tried that, but I run into problems because that makes the input and output sources linked. While I do want them linked, this isn't linking them in the way I need.

 

If I set the input file to C:\\a\\b\\c.xlsx the output file becomes the same, and then adds the fanout after that so I get C:\\a\\b\\c.xlsx\\c - FME Test Results.xlsx, which obviously throws an error. (I used FilenamePartExtractor to get the _rootname - I was unable to find an attribute called fme_basename)

 

Then I try and change the output directory to just C:\\a\\b and the input file changes to that too, and then throws an error because that's not an excel file.

 

I'm not sure if I'm doing something wrong, missing a step maybe?

 

Thanks

New year, new try :)

Still not sure where I've gone wrong with my original writer to make it connected to the reader, so I decided to try again with a new writer, and it worked as you described this time. I think I will spend some time comparing all the settings to see what is different between the two writers, as I do want to understand what I've done, however, in the meantime I will be able to do what I need to do.

Thank you for your help.

Reply


Helpful Members This Week

Community Stats

31,139
Posts
118,062
Replies
38,758
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.