Data Integration Archives

Excel Data Mapping to Update Existing Documents

February 2, 2022/in Data Integration, Tools /by David McGahey

Excel began life as a simple spreadsheet tool. Over time, support for rich text styling options, built-in charts, and copy and paste formatting features has led many enterprises to create reports in Excel documents. This can cause difficulty when data changes and existing documents need to be manually updated for distribution to a wide audience in the familiar report style.

Altova MapForce, the award-winning, graphical data mapping tool for any-to-any conversion and integration, supports Excel data mapping to convert data to existing Excel documents while preserving styles and formulas in the original.

This feature lets you write directly to nicely formatted Excel files to update data at runtime: any designated worksheets, rows, and cells from the specified file will be replaced with data from the mapping and all formatting in the existing file will be preserved as-is. To protect functionality in the existing spreadsheet, cells with formulas are not overwritten.

Let’s look at an example of how to map Excel data.

API Data Mapping

April 30, 2021/in Data Integration, Tools /by David McGahey

Web service data integration with MapForce is a popular and proven strategy to capture timely information for analysis or generation of user-friendly reports. In an earlier post we demonstrated API data mapping in 5-day weather forecasts for busy cargo shipping ports by reading Web service data in JSON format and mapping to richly formatted Excel spreadsheets. The weather API we used is hosted by OpenWeather, a provider of historical, current, and weather forecast data.

But integrating data from any API is not a set-it-and-forget-it task. When you build a solution based on external data, you have to react quickly when the data structure changes. Since our original integration project OpenWeather revised the data delivered by their API. The API now includes wind gust predictions in a JSON property separate from wind speed. Since wind gusts are suspected as a cause of the recent Suez Canal blockage the new data is very relevant to our application! Fortunately, both the MapForce data mapping and the Excel spreadsheet are easily revised to add new data.

How to View Workflow Automation Reports on FlowForce Server

April 28, 2021/in Data Integration, Servers /by Erin Cavanaugh

Altova FlowForce Server is a high-performance workflow engine for automating enterprise-level data processing, data integration, and ETL jobs.

FlowForce Server includes a helpful web interface for managing and monitoring all aspects of data processing jobs, including in-depth logging functionality and a complete visual dashboard. Let’s take a look at how you can take advantage of FlowForce charts and statistics to monitor the progress of FlowForce Server jobs, as well as performance of the server itself, in great detail.

Data Mapping with XSLT3 Math Functions

April 14, 2021/in Data Integration, XPath, XQuery /by David McGahey

XSLT3 adds trigonometry and other advanced math functions, new formatting functions, functions to collect environment variables, and more, extending XSLT and XSLT2 XML transformation standards. Data analysts and other data professionals can apply XSLT3 functions to solve XML data mapping and integration challenges that require complex mathematical computations. Let’s look at some MapForce examples of data mapping with XSLT3 math functions using trigonometry and other complex math expressions.

Data Mapping Binary Objects – Part 2

October 30, 2020/in Data Integration, Database mapping, SQL, Tools /by David McGahey

Binary objects – BLOBs — can be cumbersome to manage in databases. In an earlier post we described a MapForce data mapping to insert binary objects into a database with generated metadata to identify the BLOBs later. The companion challenge in data mapping binary objects is to extract binary data and save it in a comprehensible form faithful to the original.

Let’s look at how that’s done.

Data Mapping Binary Objects

October 21, 2020/1 Comment/in Data Integration, Database mapping, SQL, Tools /by David McGahey

Binary objects are difficult to manage in databases. They are large, their content is not human readable, and they can contain bytes of data easily misinterpreted as control characters. Even the data type name for binary large objects – BLOB – reflects most database managers’ dislike of them. Before relational databases, the definition of a blob was “something undefined or amorphous.”

Altova MapForce, the award-winning, graphical data mapping tool for any-to-any conversion and integration, includes features for effortlessly data mapping binary objects to or from all popular relational databases. Data such as images, PDF files, video files, or any other binary data can be mapped. Let’s look at an example.

Tags: binary objects, data mapping

Web Service Data Integration

May 11, 2020/in Data Integration, JSON, Tools /by David McGahey

In a previous post we wrote that every data integration and reporting task needs to start with a clear understanding of the source data. Using grid view in XMLSpy, the industry-leading XML and JSON editor, we analyzed JSON data for 5-day weather forecasts retrieved from a Web service.

Continuing with our earlier scenario, we’ll use MapForce, the award-winning, graphical data mapping tool for any-to-any conversion and integration, to map the forecasts for a series of major cargo shipping ports into nicely formatted Excel documents. We’ll want to highlight any predicted high winds or heavy rainfall that could cause delays by interfering with cranes loading and unloading containers, or slowing ships entering and exiting the harbors.