Excel Mapping

Mapping data to and from Excel files in Altova MapForce

MapForce Excel Features:

As Microsoft customers continue to upgrade to the newest version of the Office suite, support for Excel is an indispensable feature for sophisticated data integration applications. Mapping support includes:

  • XML data to/from Excel
  • Database to/from Excel
  • Flat files to/from Excel
  • EDI to/from Excel
  • XBRL to/from Excel
  • Web services to/from Excel

Download Trial

Developing Excel Mappings

Altova MapForce® 2017 includes support for mapping data based on the spreadsheet format for Microsoft® Excel 2007 and later versions, under the Office Open XML file format specification. MapForce supports Excel spreadsheets as mapping sources or targets, enabling you to take advantage of its powerful graphical interface for either data mapping to or from Excel files.

When you begin a new mapping based on an Excel (.xlsx) file, MapForce will prompt you to assign a sample file or global resource to preview your transformation. In the case of using an Excel spreadsheet as a target component, MapForce will load an empty template without a sample file.

To develop an Excel mapping based on an existing spreadsheet or workbook, simply open your source file in MapForce using the File menu command, or drag and drop your document into the design pane. MapForce will display a graphical representation of the file structure, depicting rows, columns, and cells, as well as references to numbers and names. This component includes clickable icons which allow you to define and specify mappable data.

Additionally, MapForce supports an optional file format introduced in Microsoft Office 2013, the Strict Open XML Spreadsheet format (ISO/IEC 29500 Strict) as a data mapping input component. Users do not need to specify whether an existing file is a Strict Open XML Spreadsheet or a standard Open XML document since both use the same .xlsx extension.

MapForce® 2017 allows you to select and map each unique data table in the spreadsheet, avoiding manual extraction, export, or other pre-processing of complex Excel worksheets outside MapForce before they are inserted into your mapping design.

When an Excel document is the output component, the ability to define multiple ranges lets you combine related data into a single spreadsheet.

Mapping data to and from Excel files in Altova MapForce

Filtering and Processing Data

MapForce also provides flexible support for filtering and processing data via advanced processing functions based on Boolean conditions or manipulating data as it’s converted from source to target format. You can create and combine custom functions, or build advanced functions using MapForce’s extensive function library.

Excel Data Conversion

Once your mapping is defined, the built-in MapForce Engine allows you to view and save the results with one click. Mappings to Excel produce Office Open XML (OOXML) markup.

MapForce can autogenerate the program code in Java, C#, or C++ to migrate data from the source to the target content structure for use in your customized data integration applications. This allows you to reuse your mapping structure for future iterations. MapForce also supports automating data conversions via data integration API, ActiveX control, or the command line.

"MapForce has proven to be an easy-to-use, effective tool for making the data integration and mapping process much easier and faster to implement.”

Dan Ochs
Principal Consultant, Wrycan