This article is intended for developers. For user help on Excel import, see: Working with Excel: Export and Import.

The Excel workbook whose data is imported into the questionnaire must be prepared: Excel field names corresponding to the names of the components must be placed on the Excel cells containing the data. Such a workbook is called an input matrix or input workbook.

Input matrices can be simply generated by the XLSX Export from GTAnswer. This is the preferred solution for ease of implementation.

Advanced Excel input matrices can be designed by defining the field names corresponding to the components using formulas, which allows for example partial imports (an example of use is provided in the article Informal Collection Workflows)

The field names corresponding to the Excel field names can have two forms:
– Component_Name for old input matrices when the headers are unique for the whole qstx
– Pattern_Name? Component_Name for qstx with uniqueness per compartment branch, when the component is in a pattern. Field names do not use the Pattern_Name prefix for components outside patterns (see Header Uniqueness article)

Importing Excel into patterns

Every pattern must have a Name_of_pattern.FOOTER field in Excel, which marks the end of the pattern:
– the rows to be imported from Excel are located above this field.
– “Rows” where all component values (with corresponding Excel field names) are empty are ignored during import.

If the field Pattern_Name.FOOTER is not present, the pattern is not imported.

To avoid users having to insert rows at the end of the pattern themselves, it may be appropriate to provide a .FOOTER name field with slightly more rows than necessary in a manual design of an input matrix.
However, providing too many empty rows can be detrimental to the import.

Procedure for importing Excel into a pattern

The term “row” used in this and the following paragraphs refers to a range of cells with the same dimensions (number of rows and number of columns) as the base range of the pattern.

– GTAnswer reads the data (name fields, etc.) from Excel to count the non-empty “rows” to be included in the pattern
– “Rows” are added to or deleted from the existing pattern of the questionnaire if the number of “rows” to be imported from Excel is different from the current number of rows in the pattern
– The first “row” of data in Excel overwrites (for unlocked out-of-control components) the first “row” of data in the pattern, the second “row” in Excel overwrites the second “row” in the pattern, and so on.

Designing a pattern to allow Excel import

Design rules

The operation described in the previous paragraph means that any pattern for which Excel import is authorised must meet the following criteria:

• The pattern must necessarily include a key; otherwise, conflict management will produce inconsistent results
• If the pattern does not have a key, then all components must be modifiable or use formulas based on a sub-key specific to an item in the row: no value directly from the client database can be entered in the pattern without being modifiable.
• If the pattern contains components that cannot be modified by the correspondent (locked components), the Excel import will not modify them, and the values of these locked components must not have any meaning (access to these components must be defined by the logic of the questions and not be an access right for the correspondent as an individual)

These rules of good design apply to any pattern for which data is to be imported from Excel whether or not Excel allows rows to be added or deleted.
They are based on the following operating consequences described above:
– Components not editable by the correspondent are not modified during the Excel import.
– sorting or filtering is always allowed in Excel. The data in the first row of Excel will overwrite the data in the first row of the qst and so on.

In the case of an input pattern, since the key is editable, if the input pattern should be limited to a list of specific keys, a reference list of items to be input can be constructed in another pattern.
Constraints can then check the membership of each key of the input pattern to the reference list and, conversely, the status of each reference key to the input pattern (EQUIVs can be useful in this case)
This reference list can also be used to display “reference” data specific to each key.

Multi-tab Excel import

Principle

The multi-tab import works with fixed items. No sheet is added or deleted by the import.
In the case of a dynamic multi-tab, the sheets must be added manually by the correspondent.

The name of the multi-tab axis must be compatible with the Excel field names.

The link between the GT components and the Excel workbook is made by named fields. Outside the multi-tab, these areas are global. In the multi-tab sheets, the fields are local, including that for the pattern footers. For Answer to make the link between a multi-tab item and an Excel sheet, the sheet must contain a local field with the name NOM_DE_L_AXE.MO, whose value is the multi-tab item.

Multi-tab import compatible with Calc LibreOffice but not Calc OpenOffice

Importing data to a multi-tab group only works with Microsoft Excel (versions 2000 and later) or the Calc version included in LibreOffice.

Data from a workbook loaded into Calc OpenOffice cannot be imported into a multi-tab group (this is due to a working restriction of OpenOffice, which does not allow local field names, unlike Excel).

The Calc (OpenOffice) import to Excel can, however, be used on data outside the multi-tab of a questionnaire.