Multiple XML files from Excel rows

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

Home >  Designing Mappings > Processing Multiple Input or Output Files Dynamically >

Multiple XML files from Excel rows

The content of the altova.xlsx spreadsheet file, available in the ...\MapForceExamples\Tutorial folder, is shown below. It consists of two worksheets: Admin with 10, and Development with 11, rows of data. This example is available as Excel-Mapping-dyn.mfd in the ...\Tutorial folder.

 

Admin worksheet

Development worksheet

dyn-excel2xml3a

dyn-excel2xml3b

 

MapForce is able to display and map Excel components in two different ways, depending on the component options. The default settings are shown in the dialog box below. The "Show Worksheets by name" check box is selected when you first insert the Excel component.

excel2

 

To access a Workbook as if it were a single Worksheet:

1.Click the excel1-compicon icon next to Admin in the Excel component.

dyn-excel2xml4

2.Click the "Show Worksheets by name" check box to deselect it. The named Worksheets are not visible anymore, as shown in the screenshot below, but a Worksheet Name item is now available.

dyn-excel2xml04

Aim 1: To generate separate files for each Worksheet containing the person records of each.

 

The "Worksheet Name" item determines the specific worksheets in the workbook, so this is the item we will use to split up the source workbook into separate files.

 

1.Drag the concat function from the Libraries window into the mapping, and insert a constant.

 

dyn-excel2xml1

 

2.Create the connections as shown above: Worksheet Name to value1 and the constant to value2.
3.Connect the result parameter of the concat function to the File: item of the target component. Note that File: <dynamic> is now displayed.
4.Define the remaining connections as needed.
5.Click the Output tab to see the result of the mapping.

dyn-excel2xml5

Each record is now visible in its own Preview tab, the first one is shown above.

Clicking the Next/Previous ic-fwdback arrows allows you to see each of the files in the Output tab.

 

Notes:

 

The WorkSheet name field supplies the first part of the file name e.g. Admin.
The constant component supplies the file extension i.e. .xml, thus Admin.xml is the file name of the first file. Admin.xml contains all the rows of that Excel tab. Development.xml contains the other rows.
Clicking the Save All icon ic-save-all-out allows you to save the individual files directly from the Output tab, without having to generate code.

 

 

Aim 2: To generate separate files for each Person in each Worksheet

 

1.Click the excel1-compicon icon next to Worksheets if you followed the section above, and activate the "Show Worksheets by name" check box.

Each of the separate Worksheets are now visible in the component: Admin and Development.

dyn-excel2xml2

2.Insert the concat and constant components as shown.
3.Insert a second XML Schema file of the same name and create the connections as shown.

 

As the "Row number" element determines the specific person rows in the worksheet, this is the item we will use to split up each worksheet into separate files.

 

For the Admin worksheet item in the Altova XLSX component:

 

1.Create the connections as shown above: Admin constant to value1, Row Number to value2, and .xml constant to value3.
2.Connect the result parameter of the concat function to the File: item of the target component. Note that File: <dynamic> is now displayed.
3.Define the remaining connections as needed.
4.Click the Output tab to see the result of the mapping.

dyn-excel2xml6

Each row is now visible in its own Preview tab, the first one is shown above. All 10 records of the Admin worksheet have been split into separate files.

Clicking the Next/Previous ic-fwdback arrows allows you to see each of the files in the Output tab.

 

Notes:

 

The Admin constant supplies the first part of the file name e.g. Admin.
The Row Number item supplies the row number from the Excel worksheet e.g. 1.
The .xml constant supplies the file name extension used when saving the file which is Admin1.xml, as shown above.
Clicking the Save All icon ic-save-all-out allows you to save the individual files directly from the Output tab, without having to generate code.

 

To see the output of the Development worksheet, click the Preview button of that component, then click the Output tab to see the result of the mapping. 11 records have been split into separate files.


© 2019 Altova GmbH