Data Exchange File Formats: Exploring ETL Tools with OPC Router

4 min read

Dec 21, 2023 2:00:00 PM

To achieve operational and business goals, many industrial automation system architectures are designed to push and pull data from a multitude of sources and destinations that come with a variety of formats. Bridging the gap between systems that use distinct standards and protocols can be challenging if the right tools aren't properly selected, and too often users resort to custom software. This is where users can leverage OPC Router’s ETL (Extract, Transform, Load) Tools—a valuable solution for seamlessly repackaging data across processes that use distinct standards or protocols.

In this article, we will briefly introduce the power of OPC Router’s ETL Tools, exploring a few use cases that highlight their role in structuring data into popular file formats. If you are not already familiar with what OPC Router is and what it can do for your particular process, you can visit the OPC Router website or the OPC Router Technical Guides with a Quick Start for New Users.

Extract, Transform, Load

ETL describes the process of consuming data from multiple sources (Extract), deriving the information needed from it (Transform), and outputting it to the relevant systems (Load). OPC Router’s modular configuration makes it effective middleware for assuming this role; and information can be restructured to accommodate each destination.

OPC Router ETL diagram

For consolidating data into text file formats, OPC Router offers the following transfer objects:

Each transfer object represents a module within OPC Router where some processing is performed. To show some of  these in action, let’s go over some simple workflows that leverage these tools to push tags from an OPC server to different data destinations.

Example: JSON Payload to an MQTT Broker

OPC Router JSON Example

The JSON Write object can be used to define a payload that can be published to a broker. The properties of the JSON Write allow you to input an example payload, from which item keys can be extracted.

OPC Router JSON write

Example: CSV Files to an FTP Drive

OPC Router CSV Example

OPC Router has the option to write files to a local directory or to a network drive. This can be combined with ETL objects to generate logs in a specific file format— in the above case, a CSV file is updated with the latest values for the monitored items.

The CSV object can also generate fixed-width files, and alternative delimiters can be specified.

Example: XML Data to a REST API

OPC Router XML Example

Like the JSON Write, the XML/XPath object contains a text field to input an example format. XPath expressions are used to extract the items that values will be passed to.

OPC Router XML properties

In addition to the tools highlighted above, other transfer objects are provided for ETL operations relating to modifying data through arithmetic operations, filtering, ordering, scripting, and more. For more information about each Transfer Object, please take a look at the product’s help manual online, a link to which is provided when you install with OPC Router.

To start evaluating how OPC Router can securely improve efficiency and interoperability in your environment, you can download the free trial. Please do make sure to visit the resources section on OPC Router’s product page for information on how to use the product with other highly utilized standards. 

As always, please contact us with any questions and be sure to subscribe to our blog for more tips on navigating features in OPC Router and our other software.

New call-to-action


Nicole Knox
Written by Nicole Knox

Software Toolbox Technical Blog

We're engineers like you, so this blog focuses on "How to" appnotes, videos, tech team tips, product update announcements, user case studies, and other technical updates.  Subscribe to updates below. Your feedback and questions on posts are always welcomed - just use the area at the bottom of any post.

Subscribe to our Blog

Recent Posts

Posts by Topic

See all