Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Authoring
  • Using attribute from excel reader to write new sheets that are fanned out based on column contents - creating new file with all of the sheets, and editing existing file with a blank sheet?

So i have a sheet that contains a bunch of unsorted rows, and i want to read the most recent file, create separate tabs within the existing file for each unique attribute value and then copy all of the rows with that value into the associated tab.

 

So i've got an excel reader to read the directory, then i'm sorting the files to get the latest one, sampling just the newest one and using featurereader to feed into the writer.

 

i've attached a copy of my workspace for reference, as well as an input file.

 

i've been trying to use this article as a guide:

https://community.safe.com/s/article/fanout-1

 

but what is happening right now is the writer is creating a new file, with NULL as the filename with the new tabs, and, then editing the original file with an empty tab named "fme_blank_sheet" instead of writing them all back to the original file.

 

here is a snippet of the original tab's data:

 

imageso i'd like all of the unique specialchar values to be copied to their own tabs:

like this:

imagei've done some googling on using the path_filename attribute as the file name, but mostly i've run across people with issues writing new files, which is the opposite of what i'm trying to do.

 

I'm not sure what i'm missing, please advise.

 

One way to solve this is to use the fme_dataset attribute. You need to expose this deep in the (feature)Reader, but then you can use it for the writer. Sample workspace attached.

 

As an alternative you can use a FeatureReader (Directory and File Pathnames) to scan a directory for files and use this as input for the FeatureReader. But then you also have to set "Merge attributes" in the FeatureReader (Excel) to merge the attribute path_windows to the records. You can then use that attribute to write again to the original file.

One way to solve this is to use the fme_dataset attribute. You need to expose this deep in the (feature)Reader, but then you can use it for the writer. Sample workspace attached.

 

As an alternative you can use a FeatureReader (Directory and File Pathnames) to scan a directory for files and use this as input for the FeatureReader. But then you also have to set "Merge attributes" in the FeatureReader (Excel) to merge the attribute path_windows to the records. You can then use that attribute to write again to the original file.

oh, i didn't even realize that you could write to an excel file without the excel writer 😅

 

I'll give this a go, thanks!

That, my friend worked exactly how i wanted, thank you very much!

 

That, my friend worked exactly how i wanted, thank you very much!

 

Cheers :)

Reply


Community Stats

30,108
Posts
114,295
Replies
37,745
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.