Question

Multiple workbooks into one based on filename

  • 31 August 2016
  • 2 replies
  • 4 views
W

Hi I have a question which I think is simple to answer but I'm struggling and in a rush...

I have a folder with 1000+ excel files all named the same except for a 3 digit code at the end of the filename string, before the extension. I then have two data layers of file for each code. I need to extract the two excel workbooks that have matching 3 digit codes into one workbook, with each of the two data layers going into a separate worksheet.

eg. example files

London_123_Cars_ABC

London_123_Buses_ABC

London_123_Cars_DEF

in this example I need the two files which end in ABC (3 digit code) to merge into one excel file with a sheet containing the 'Cars' data and a sheet containing the 'Buses' data.

i've tried exploders and lists but struggling so any help? the more detail the better, thanks in advance

2 replies

itay
Rank
Badge +16

Hi,

I would use the fme_basename to extract the digits and group with the aggregator, make sure you are keeping the attributes when doing that .

redgeographics
Rank
Userlevel 5
Badge +25

Here's how I solved it.

  1. Set up a reader to read all Excel files in a directory and output a single feature type. I'm assuming they all have the same attribute structure.
  2. Expose the fme_basename attribute. This holds the original filename.
  3. Assuming the filename always follows that structure I've used an AttributeSplitter to split it with the _ (underscore) as split character. This creates a list with list{2} containing "Cars" or "Buses" and list{3} the 3-digit code.
  4. Set up an AttributeManager or AttributeCreator to create new attributes "sheet" and "file" and fill them with list{2} and list{3} respectively.
  5. Set up an Excel writer and use the attribute "sheet" to fill the sheet name, then set the fanout option in the navigator and use "file" for the filename.

Hope this helps. I've attached a template with my workspace and input files.

excelcombiner.fmwt

Reply


Community Stats

28,723
Posts
109,511
Replies
36,560
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.