Merging or Joining Spreadsheet or Database Data

Introduction

Merging or joining data together in FME is accomplished by using a transformer. These transformers allow the user to define a relationship for the basis of a join. This article covers which transformers work best for merging data that comes from a spreadsheet or database. If you are interested in merging spatial data, see the Merging or Joining Spatial Data article.

Merging or joining data together is different from connecting data streams in FME. When the data is merged the attributes from each dataset are combined together based on a common identifier and then output as a single fused dataset. When the data streams are just connected by drawing two connections to an input port of a transformer that isn’t a merging transformer, the data will be in a single stream, but the attributes will still be separate. For more information on combining data streams see the Combining Multiple Streams of Data (Append vs. Merge or Join) article.

Transformers

There are two main categories for merging or joining transformers, they are: SQL and no-SQL. The transformers that are in the SQL category require knowledge of SQL; these are the InlineQuerier, SQLCreator, and the SQLExecutor. The no-SQL transformers have the same functionality of the SQL ones but can be set up easily without any database knowledge; these are the FeatureJoiner, FeatureMerger, FeatureReader, and the DatabaseJoiner. This isn’t an exhaustive list, there are other transformers that allow data to be joined but are used for a specific task. For more information on these other transformers, see the documentation.

Now when trying to decide which transformer is the correct one to use, follow the flowchart below to help. When trying to decide, inspect your data and ask the following questions:

1) Is all of my data already inside the workspace?

• If yes: Follow the INTERNAL path

• If no: Follow the EXTERNAL path

2) Does the FeatureJoiner for internal or DatabaseJoiner for external work for my data?

• If yes: Great! End here

• If no: Continue to question 3

3) Do I know or want to use SQL?

• If yes: See the transformers in the green SQL box

• If no: See the transformers in the blue No SQL box

4) Did any of those transformers work?

• If yes: Great!

• If no: Read the documentation to ensure if it was set up correctly and suites your needs

• If unsure: Re-read the documentation, if still unsure ask a question in our community.

Tutorials and Documentation

Transformers listed in alphabetical order:

DatabaseJoiner

The DatabaseJoiner, previously known as the Joiner transformer provides the ability to form a join against a database or other external dataset with an existing dataset in the workspace.

The DatabaseJoiner Transformer tutorial

Join CSV File to a Database Table with the DatabaseJoiner

DatabaseJoiner Documentation

FeatureJoiner

The FeatureJoiner transformer is a simpler version of the FeatureMerger and uses joins based on SQL, with greater performance. This transformer can be used with ease without prior SQL knowledge. *Note this transformer only works for FME 2018+

The FeatureJoiner Transformer tutorial

FeatureJoiner Documentation

FeatureMerger

The FeatueMerger performs a simple, in memory, join. The join is carried out from two or more input streams of data that have already been read using a reader or a transformer like the FeatureReader.

The FeatureMerger Transformer tutorial

Combining Multiple Streams of Data (Append vs. Merge or Join)

Polling Places for Local Government

FeatureMerger Documentation

FeatureReader

The FeatureReader reads features from any FME-supported format. This can be done midstream which aids in adding and joining data quickly.

The FeatureReader Transformer tutorial

Performing spatial queries on database tables using the FeatureReader

FeatureReader Documentation

InlineQuerier

The InlineQuerier utilizes the power of SQL. It can be used to build data linkages more directly by making use of a temporary database consisting of tables created from incoming features and returning the results (generated from SQL statements) as new features.

The InlineQuerier Transformer tutorial

Using the InlineQuerier as a replacement for multiple FeatureMergers

InlineQuerier Documentation

SQLExecutor and SQLCreator

The SQLExecutor and SQLCreator transformers are very similar. They are both used to execute SQL against a database. The only difference is that the SQLExecutor requires an incoming feature to trigger the SQL statement, and the SQLCreator does not.

The SQLExecutor and SQLCreator Transformers tutorial

Using the SQLExecutor to do a SQL Join

Performing native spatial queries on database tables using the SQLExecutor

SQLExecutor Documentation

SQLCreator Documentation

Looking for spatial joining transformers? See the Merging or Joining Spatial Data article.