How To
Summary
How to use a Transformation Extender map and the Excel Adapter to output data to multiple worksheets of the same Excel spreadsheet / workbook file.
Objective
The purpose of this article is to explain how to use a Transformation Extender map and the Excel Adapter to output different data to multiple worksheets of the same Excel spreadsheet / workbook file.
Environment
Different data is to be used to create multiple worksheets in an Excel spreadsheet / workbook file.
Steps
The following steps may be used as an example of how to use a Transformation Extender map and the Excel Adapter to output different data to multiple worksheets of the same Excel spreadsheet / workbook file.
- Create a Transformation Extender map the reads and validates the different data to be written to the columns and rows of each Excel worksheet.
- Create or use an existing Excel template spreadsheet / workbook that contains required worksheets. The template should contain header rows for each worksheet, formatting and other information that is desired for the spreadsheet / workbook.
- Use the Design Studio Excel Importer to create type trees from the different worksheets in the Excel template spreadsheet to be written to. Or use the Design Server Excel connection and action to create a schema from the different worksheets in the Excel template spreadsheet / workbook to be written to.
- Create a map output card for each worksheet type tree / schema.
- Complete all mapping functional maps and map rules in each map output card to build the required data for each worksheet.
- Define the Excel Adapter and the following Excel adapter commands in each map output card.
-F workbook.xls[x] -FILE workbook.xls[x]
-W sheetname -WRKSHEET sheetname
-P template.xls[x] or -TEMPLATE template.xls[x]
(optional) -T trace or TV trace verbose with + for append to enable the Excel Adapter trace for troubleshooting - The first map output using the Excel Adapter will use -P template.xls[x] or -TEMPLATE template.xls[x] command to reference the Excel template spreadsheet / workbook created in above step 2.
- The second and later map output using the Excel Adapter will use -P template.xls[x] or -TEMPLATE template.xls[x] command to reference the same value as the -F workbook.xls[x] -FILE workbook.xls[x] command of the first output card or previous output as the Excel template spreadsheet / workbook. This is the most the important concept which allows the data to be added to multiple worksheets by using the previously created Excel spreadsheet / workbook file to be the template for the next worksheet to be written to.
Note: The same value can be used for both the -F workbook and -P template commands.
The following is an example of the Excel commands for each output card for each worksheet. Note that only the first output uses template.xlsx as the template and the later output cards use the workbook from the previous output card as the template.
Output card 1:
-F output.xlsx -W "Worksheet 1" -P template.xlsx -TV
Output card 2:
-F output.xlsx -W "Worksheet 2" -P output.xlsx -TV+
Output card 3:
-F output.xlsx -W "Worksheet 3" -P output.xlsx -TV+
Output card 4:
-F output.xlsx -W "Worksheet 4" -P output.xlsx -TV+
Each output card could also use a different workbook name as long as the previous output workbook name is used as the template as in the following example.
Output card 1:
-F output1.xlsx -W "Worksheet 1" -P template.xlsx -TV
Output card 2:
-F output2.xlsx -W "Worksheet 2" -P output1.xlsx -TV+
Output card 3:
-F output3.xlsx -W "Worksheet 3" -P output2.xlsx -TV+
Output card 4:
-F output4.xlsx -W "Worksheet 4" -P output3.xlsx -TV+
This second method would be useful if needing to check results from each output card since using the first method overwrites the previous output file and only produces that final file from the last output card.
Related Information
Document Location
Worldwide
[{"Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSD8","label":"IBM Transformation Extender"},"ARM Category":[{"code":"a8m0z000000bmWYAAY","label":"Adapters->Excel"}],"ARM Case Number":"TS004639492","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"},{"code":"PF055","label":"zLinux"}],"Version":"10.0.0;10.0.3;10.1.0;9.0.0"}]
Was this topic helpful?
Document Information
More support for:
IBM Transformation Extender
Component:
Adapters->Excel
Software version:
10.0.0, 10.0.3, 10.1.0, 9.0.0
Operating system(s):
AIX, Linux, Windows, z/OS, zLinux
Document number:
6389358
Modified date:
18 December 2020
UID
ibm16389358