OPC Router ETL (Extract, Transform, Load) Data Transformation Tools 

Multiple Tools for Maximum Access

OPC Router's built in ETL transform objects help you extract data that may be encoded in some formatting and make the data accessible and shareable to other applications.

  • XPath to extract data from XML files / data
  • JPath to extract data from JSON data packets 
  • CSV for formatting data that comes in this ubiquitous format
  • .NET C# Script plug in for handling about any format with your own scripts

XPath for XML Formatted Data

The Extensible Markup Language (XML) is an important standard for communicating structured data across the internet. For this reason, XML is also used both for value or parameter entry and output in IT-connected and internet-ready production systems around the world. The XML Path Language – or XPATH – allows easy XML data access for the OPC Router. The XPath object allows easy extraction of desired information, which is then passed on to connected systems using OPC Router's various plug-ins. 

In the example shown here, currency exchange data obtained from a RESTful web service is extracted using XPATH and stored into a database with OPC Router.  Click here for a detailed XML/XPath  example.

JPath for JSON Data Package Queries

JSON is a JavaScript-based standard used for transmission of structured data via the internet. JSON is often used as value or parameter input or output. JSON is a valuable exchange format between web client and backend services. JPath is designed for responses from sources containing JSON formatted data packages.  JPath works on the same principle as the XPath function to empower access to data contained in JSON data packages, thus enabling the OPC Router to extract and provide the desired information whenever and whereever needed.

In the example shown here, data from a REST endpoint is extracted and formatted then stored in a database.

CSV - The "Universal" Format

As long as there has been data interchange between computing systems, Comma Separated Variable (CSV) files have been used. Although simplistic they are a practical way to format data in a text form that is compatible with about any system. CSV import/export is almost an expected option in all software. Many legacy systems only offer CSV as a means of integration. 

The OPC Router CSV transfer object can process and create different CSV formats. Using the OPC Router plug-ins, data can be read from and written to different types of systems, from local files and with the network file access plug-in installed, remote file shares. 

In the example shown here, data is read from a CSV file and transferred to a SQL database. We also have a video showing how to transfer CSV to SQL. We also have a video for reading OPC data and transforming to SQL

.NET C# Scripting 

When you have a format that doesn't follow a standard like XML, JSON, or CSV, you can use the .NET C# scripting capabilities to perform ETL operations on datasets. 

Define your own triggers, use OPC Router functionality from within scripts, and even reference external .NET assemblies/libraries if required. 

The example shown here is the framework for a basic call in the OPC Router editor. 

Get Started Now

The demo is the full product. When you put an OPC Router project into runtime, it will stop operating after 2 hours and have to be restarted. Purchase of a license will remove the restriction.

Connect with Us

1-888-665-3678 (US + Canada toll free)
+1-704-849-2773 (Global)
support.softwaretoolbox.com