Skip to main content
  • EN

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,

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.

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

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.

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

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.

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?

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

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.

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


Community Stats

30,106
Posts
114,275
Replies
37,740
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.