Importing Data Using Templates

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

Home >  Importing Data >

Importing Data Using Templates

Templates represent reusable data mappings between fields in the WIP report and cell ranges in a custom sheet. The source cell ranges may be horizontal (for example, A1:Z1) or vertical (for example, A1:A30). Templates are very similar to the data mappings described in Importing Data Using Data Mappings. However, unlike data mappings, templates are reusable across multiple workbooks, because they are saved as independent files (not inside the workbook, like data mappings). With templates, it is therefore possible to import data from virtually any source workbook that contains the cell ranges predefined by the template.

 

A significant difference between data mappings and templates is that the cell ranges referenced by a data mapping are fixed (predetermined) while cell ranges referenced by templates can grow if more rows or columns of data are available in the source (likewise, they can shrink if less rows or columns are available in the source). Also, unlike data mappings, all mapped cell ranges in a template must originate from the same workbook, and from the same worksheet, as further explained below.

 

Creating a template

You can create a new template as follows:

 

1.Open any existing Excel workbook which contains WIP data in custom (not XBRL-bound) format.
2.On the WIP tab, click Insert, and add a blank WIP report into the workbook (see also Creating a new WIP Report).

wip_select_template

3.In the WIP Report Pane, click the Data Mapping tab. Notice this tab has three other tabs: In Process, Completed, and DEI (Document Entity Information). The first two tabs let you map fields belonging to the "In Process" and "Completed" sheets, respectively. The DEI tab lets you map data for those fields which appear in the Document properties (such as the start and end date of the reporting period, Registrant Name, and others).

wip_mapping_tabs

4.Enter a cell range next to each field in the WIP report that you want to be part of the template. You can either type the cell range, or click the Ellipsis wip_ic_select_range button and then select the range from the source sheet (in the latter case, the range will be populated automatically). For example, the image below illustrates the field "Contract Identifier" mapped to the cell range B3:B7 of Sheet1. The cell range selection mechanism is the same that you use for Excel formulas, see https://support.office.com/en-US/article/Create-or-change-a-cell-reference-C7B8B95D-C594-4488-947E-C835903CEBAA.

wip_mapped_cell_range

 

Rules for referencing cell ranges

 

All cell ranges must consist of either single rows (for example A1:K1) or single columns (for example, A1:A20). In other words, all of them must be either vertical or horizontal.
All cell ranges must originate from the same worksheet of the same workbook.
All cell ranges must begin and end on the same row (or column in case of vertical ranges). For example, an error would be raised if the field "Contract Identifier" used the range B1:B10 and the field Contract Name used the range C2:C9. However, no error would be raised if these fields used the range B1:B20 and C1:C20, respectively.

 

When you apply the template to a workbook, it may be the case that more or less rows or columns are available in the source workbook than specified by the template cell range—in this case, the cell range will grow or shrink to match the exact number of rows or columns.

 

5.Once you've mapped all required fields to cell ranges, click Save Template. At this stage, some validation takes place, and error messages may appear with indicative text (to fix the errors, see the rules above). Otherwise, the dialog box below is displayed.

wip_save_template

6.Optionally, select a destination template folder. By default, the template folder is C:\Users\...\Documents\Altova\WIPAddInForExcel. You can optionally change this folder to another one, by clicking the Ellipsis wip_ic_ellipsis button.
7.Enter a file name in the File name box. The template will be saved to the selected template folder as a file with .wdmt (WIP Data Mapping Template) extension.
8.Optionally, enter in the Template name box some descriptive text to help you identify this template later (for example, "Template 1").
9.Click Save.

 

If your WIP worksheet already contains previously defined data mappings (as explained in Importing Data Using Data Mappings), it is possible to save them as a template (meaning that you can skip directly to step 5 above). Note the following:

 

All data mappings must adhere to the "Rules for referencing cell ranges" listed above; otherwise, relevant errors will be shown, and the template will not be saved.
Templates are meant to be reusable and not bound to specific workbooks, like data mappings. Therefore, when you save data mappings as a template, any references to specific workbooks (if such exist) are removed. Only references to worksheets and cell ranges will be preserved by the template. For example, a cell range such as [CustomBook.xlsx]CustomSheet!$C$4:$C$8 would be saved only as CustomSheet!$C$4:$C$8.

 

Editing existing templates

Templates saved previously can be modified if necessary, as follows:

 

1.Open any workbook that contains an XBRL-bound WIP Report sheet.
2.Click the Data Mapping tab in the WIP Report pane.
3.Click Load Template.

wip_template_load

4.Select the required template from the list of available templates, and then click Load. If the templates were saved to a custom template folder, click the Ellipsis wip_ic_ellipsis button to switch the template folder.
5.Edit the cell ranges In the Data Mapping tab as necessary. The same rules apply as when adding new templates, see Rules for referencing cell ranges.
6.Click Save template.

 

How to apply a template to a custom Work-In-Process workbook

Once you have created a template, it can be applied to virtually any source workbook that contains the cell ranges defined by the template.

 

1.Open any custom Excel workbook that contains the cell ranges defined by the template.
2.In the WIP tab of the Excel ribbon, click Insert. Notice that the right-hand area of the dialog box below displays any open Excel workbooks and their worksheets (in this example, the workbook CustomBook.xlsx and the sheet "CustomSheet").

wip_apply_template1

3.Under Custom, select the template name (in this example, "Template1"). Any template files available in the Template Folder appear on this dialog box automatically. If no template files appear on the dialog box, make sure that the template folder contains .wdmt template files that you've created previously (see "How to create a template" above). To switch the template folder, click the Ellipsis wip_ic_ellipsis button.
4.Click either the In Process or Completed tab, depending on the kind of WIP report you wish to import data into.
5.Click Open Workbook and browse for the Excel book from where you would like to import data (you can ignore this step if the source Excel workbook is already open). In this example, the source workbook was already opened in step 1 so this step can be ignored.
6.Select the worksheet from which you would like to import data (in this example, data is being imported from worksheet "CustomSheet" of workbook CustomBook.xlsx).

wip_apply_template2

7.Under Mapping Method, select either Copy or Reference (see explanation below), and click OK.

 

There are two ways to import values from a source sheet: copying or referencing them. Copying means that the value is literally taken from the source range and placed into the target; also, if the source value changes, then it is not automatically updated in the target WIP sheets (you can however update the WIP report from the source at any time by clicking a Copy Values button). Referencing means that, instead of storing an actual value, the WIP sheet will contain a formula which refers to the source cell range; updating the value in the source will also automatically update it in the target sheet next time when you open the target sheet.


© 2019 Altova GmbH