FME Workbench Reader select tables to import using wildcards

Question

In FME Workbench for various database format readers one can coose which tables to import:

Because all in all I have thousands of tables, I would like to select tables that begin with certain characters and in addition contain certain characters, for the example shown in the screenshot, I would like to select all tables beginning with 'strasseninfrastruktur' and additionally containing '_ct'.

Is there a way to do this?

Mark Ireland · Accepted Answer

Yeah, that's tough because that dialog only filters on actual string matches, and doesn't support a regex type query.

Niel's idea is very inventive, but I think it will only work on file-based databases, because for a regular database you can't specify all tables (unless you select them all, but then it leads to an output port for them all, whether they're used or not).

So for a relational database I'd suggest using a SQLExecutor to get a list of tables, using a query like this:

select name from sys.tables where name like 'strasseninfrastruktur%' and name like '%_ct%'

i.e. get a list of matching tables by querying the database's own sys tables. Then you can feed those names into the FeatureReader as Niels suggests.

The other advantage is that this is a dynamic solution - as more tables are added or existing tables removed (schema drift) - then you are automatically covered.

I see you are using Smallworld, which I don't have access to and haven't used in a long time. I think this method should still be OK, but I can't recall what sort of system tables Smallworld might have available to query.

nielsgerrits · Answer

I would use FeatureReaders instead of Readers. Then you can chain them together.

Start with a FeatureReader and read only schema features to obtain all tablenames. Then filter and use as input to read features.

nr_aus · Answer

I would personally solve this problem on the Smallworld side of the fence.
A solution would be to do this programmatically on the Smallworld side in magik.
You can programmatically setup the Smallworld instance to populate the 'FME Export' input stream to only be populated with tables that meet your criteria.
Particularly with the Smallworld FME Reader, the less collections you populated into the input stream, the less collections FME needs to know about, which will greatly improve the performance of your FME workbench, both from a speed persective, but also memory usage.

FME Workbench Reader select tables to import using wildcards

3 Answers

Add your own answers!

Ask a Question