Skip to main content
Solved

Download data from list of tables

J

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.

Best answer by erik_jan

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.

View original
Did this help you find an answer to your question?

4 replies

erik_jan
Contributor
Forum|alt.badge.img+18
  • erik_janContributor
  • Contributor
  • Best Answer
  • May 2, 2019

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.

redgeographics
Celebrity
Forum|alt.badge.img+49

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.

erik_jan
Contributor
Forum|alt.badge.img+18
  • erik_janContributor
  • Contributor
  • May 2, 2019
redgeographics wrote:

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.

J

Thanks @erik_jan that gave me just what I needed.

Reply


Helpful Members This Week

Community Stats

31,313
Posts
118,738
Replies
38,899
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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