Example: Mapping Excel 2007+ to XML

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Microsoft OOXML Excel 2007+ >

Example: Mapping Excel 2007+ to XML

The mapping file used in the following example is available at the following path: <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\Excel-mapping.mfd. When you open the sample file, you will notice that it contains three distinct mapping transformations. The top two transformations are discussed in this section.

 

The aim of the first mapping is to do the following:

 

Select from the source Excel workbook only people whose phone extension (column C of the workbook) starts with a "1".
Add the prefix "10" to the original number, and write it to a target XML file, along with the First and Last names of the respective persons.

Excel-mapping-1-ENT

Excel-mapping.mfd (sample 1)

The mapping is configured as follows:

 

Altova.xlsx is the source Excel 2007+ workbook. Columns A and B supply the First and Last names respectively. Column C supplies the phone extension number.
Both worksheets of the workbook are shown as one node in the component (in other words, the Show worksheets by name option is disabled). This is indicated by the "Worksheets" node under the Workbook item.
The starts-with function checks if the phone extension (col. C) starts with a "1", and if the result is true then those records are forwarded by the filter component.
The concat filter adds the prefix "10" to each of the telephone extensions and writes it to the PhoneExt item.
MFCompany.xsd is the target component and contains the filtered person details when data is output.

 

The result of the mapping is that four persons have been mapped to the XML file with their details.

ex2xml01a

The second mapping is identical with the first one, except that worksheets have been individually enabled using the Show worksheets by name option. This mapping is configured as follows:

 

The Admin and Development worksheets are both visible under the Workbook item.
Connectors have only been defined from the Admin worksheet to the target component.

Excel-mapping-2-ENT

Excel-mapping.mfd (Sample 2)

The result of the mapping is that only two persons have been mapped to the XML file with their details.

ex2xml01b


© 2019 Altova GmbH