Question

Re-order Excel results based on Template

  • 30 October 2018
  • 2 replies
  • 5 views

Badge

I have a task that need extract several excel workbook , then rename and combined based on a key; The result also write to an Excel workbook which I have a template; My question is how to order the results based on Template columns Names. I can manually re-order them, but I am handling excel sheet with 100 and 300 columns source tables, if I manually re-order, that needs every time to re-order again; See attached image: Template has " ILI Reference Girth Weld"; FME has "NDE Report Issue#" after "Dig Phase #"; I also can read the Template and get the order, but I don't know how to merge to the workflow, also feel redundancy since Writer already reference the Template. Any solution to this issue? Thank you.


2 replies

Userlevel 4
Badge +30

H @allensong

Could you share us the Workspace template to investigate better?

Thanks,

Danilo

Badge

Hi @allensong

If I understand your problem correctly, you have an Excel workbook which contains the schema you require for your writer, but need to know how to set the writer to use this schema - is that correct?

If so, there's a section of the FME Advanced training course which covers dynamic schema handling which may fit the bill - see here. The video here works through a couple of scenarios - around 5hrs 30 mins in.

The procedure you need to follow is to first define your Excel template as a 'workspace resource' - you can find this on the Navigator pane - right click and select 'add reader as resource.' Select the workbook and worksheet which contain the schema you want your writer to take.

Next, add your writer. Where the dialog prompts to 'add feature type -> sheet definition' select 'dynamic' and add the writer. Then when you open the writer parameters, you should see a section named 'dynamic schema definition' with the checkbox ticked.

One of the dialogs to complete below this is named 'schema sources - click the elipses here and you should see an option to select the schema that you read in above as a workspace resource.

Running this way, you can make any required changes to the template file and the writer will automatically pick those up when defining the schema at runtime.

Reply