Skip to main content
  • EN

Looking for a way to provide a list of tables (in Excel), create a SQL statement and read all data into and Excel file. The challenge: each source table can have different fields.

Example: two tables:

Table1-Fields: ID, Text1, Number1

Table2-Fields: ID, 88Text, ANumberField

Excel file would have a 'TableToRead' column containing table names. In this example Table1 and Table2. (My actual excel file has about 40 tables)

Use attributemanager to build the select statement based on 'TableToRead'- result 'SQLSelectStatment'

(for instance SELECT * FROM Table1 )

SQLExecutor then reads from 'SQLSelectStatement'-problem is the 'Attributes to Expose' will change with each table.

I though I could use another SQLExecutor, do a sql DESCRIBE on the table, get the field list and pass to the SQLExecutor that does the select-but it looks like you can't pass 'Attributes to Expose' into A SQLExecutor.

This can be done, and the easiest way is to use this workflow:

Create a dynamic workspace reading 1 table from the database and write using dynamic mode to Excel (make sure the spreadsheet is not overwritten each time: I would use the table name as file name).

Using the "Feature types to read" parameter you can specify what table to read.

Publish the "Feature types to read" parameter.

Point the dynamic writer to the same source.

 

Then create a second workspace (the runner).

Read the Excel spreadsheet with tablenames, using an Excel reader.

Then use the WorkspaceRunner transformer to call the first workspace per table and pass the table name from the spreadsheet to the "Feature types to read" parameter.

 

Hope this helps.

If you try the FeatureReader instead of the SQLExecutor it'll also output a schema feature which you can use to dynamically set the Excel output schema.

Check out the Advanced Reading & Writing part of the FME Desktop Advanced training.

If you try the FeatureReader instead of the SQLExecutor it'll also output a schema feature which you can use to dynamically set the Excel output schema.

Check out the Advanced Reading & Writing part of the FME Desktop Advanced training.

Why didn't I think of that. Way easier than my solution!

Use the Excel reader from the table list as input for the featureReader.

Write both Schema and Generic outputs to the Excel Writer.

Thanks @erik_jan that gave me just what I needed.

Community Stats

31,951
Posts
121,337
Replies
39,562
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.