Apply a Metadata Input Sheet#

To apply metadata from a spreadsheet to variables in a dataset, use the Apply Metadata Input Sheet command.

Navigate to a dataset, variable set, or variable group.
Click the Apply Metadata Input Sheet button on the ribbon.
Select the Excel or CSV file that contains your metadata. See below for information on the required format.
A summary of changes to be made will be displayed. The Row column identifies the row of the spreadsheet that each message refers to, and the Error column indicates whether a message needs your attention.
To apply the changes, click Save. To discard the changes, click Cancel.

Metadata Input Sheet Columns#

All columns other than Name are optional. When a cell is left blank, the corresponding property is left unchanged.

Name or Name:ignoreCase

Required. Matches the name of a variable in the data file. If Name:ignoreCase is used as the column header, the match is made ignoring capitalization.

Label

The variable label to be applied in the metadata.

Description

The variable description to be applied in the metadata.

Type

The type of the variable. Allowed values are Text, Numeric, Code, and DateTime.

Changing the type of a variable replaces its existing representation, including any codes, missing values, or numeric ranges it contained.

MeasurementUnit

Describes the units of data in the variable (for example, meters, inches, minutes).

Additivity

Describes how values in the variable can be aggregated. Allowed values are Unspecified, Stock, Flow, and NonAdditive.

FrequenciesForValid

Whether summary statistics should include frequencies for valid values. Allowed values are True and False.

FrequenciesForInvalid

Whether summary statistics should include frequencies for invalid values. Allowed values are True and False.

Width

The width of the variable, as an integer.

NumericType

For numeric variables, the type of numeric representation. Allowed values include Integer, Decimal, Float, Double, etc.

DecimalPositions

For numeric variables, the number of decimal positions, as an integer.

Minimum

For numeric variables, the minimum allowed value.

Maximum

For numeric variables, the maximum allowed value.

StartPosition

The start position of the variable in a fixed-width data file, as an integer.

EndPosition

The end position of the variable in a fixed-width data file, as an integer.

ArrayPosition

The array position of the variable, as an integer.

IsWeight

Whether the variable is a weight variable. Allowed values are True and False.

IsGeographic

Whether the variable contains geographic information. Allowed values are True and False.

BlankIsMissingValue

Whether a blank value should be treated as a missing value. Allowed values are True and False.

Role

The role of the variable (for example, identity, weight, geographic).

CodeListId

References an existing code list by ID, and links it to the variable’s code representation. The variable must already be a code variable; use the Type column to make it one. See Referencing Existing Items by ID for the accepted ID formats.

If both CodeListId and Codes are present for a variable, CodeListId takes precedence. If the code list cannot be found, the Codes column is applied instead, and the summary reports the code list that could not be located.

MissingValuesId

References an existing managed missing values representation by ID, and links it to the variable. The variable must have a representation type set, because Colectica cannot store missing values for a variable that has no representation. See Referencing Existing Items by ID for the accepted ID formats.

If both MissingValuesId and MissingValueCodes are present for a variable, MissingValuesId takes precedence. If the missing values representation cannot be found, the MissingValueCodes column is applied instead, and the summary reports the item that could not be located.

QuestionText

If present, creates a question item with the specified question text, and references it as the variable’s source question.

UniverseLabel

If present, creates a universe with the specified label, and references it as the variable’s universe. If the variable already has a universe, that universe’s label is updated. Variables that share a universe label within one sheet share a single new universe.

UniverseId

References an existing universe by ID, and assigns it to the variable, replacing any universe the variable already has. See Referencing Existing Items by ID for the accepted ID formats.

If both UniverseId and UniverseLabel are present for a variable, UniverseId takes precedence, and the UniverseLabel column is ignored.

UnitTypeLabel

If present, creates a unit type with the specified label, and references it as the variable’s unit type. If the variable already has a unit type, that unit type’s label is updated. Variables that share a unit type label within one sheet share a single new unit type.

UnitTypeId

References an existing unit type by ID, and assigns it to the variable, replacing any unit type the variable already has. See Referencing Existing Items by ID for the accepted ID formats.

If both UnitTypeId and UnitTypeLabel are present for a variable, UnitTypeId takes precedence, and the UnitTypeLabel column is ignored.

Codes

For Code variables, specifies the codes and categories (sometimes called value labels). The format of each cell mapped to a code list should be: 1, Choice One | 2, Choice Two | 3, Choice Three | 4, Choice Four.

MissingValueCodes

Specifies labeled missing values. The format is the same as the Codes column.

Topic

Specifies a topic for the variable. A topic hierarchy can be specified by separating levels with colons. To include an actual colon in the topic, use two colons.

group:Group Label

Specifies the group to which the variable belongs, within a top level group Group Label. Replace Group Label with the label of the group. For example, a column can be named group:Waves. A top level variable group named Waves will be created, and variables will be placed within a sub-group that takes the name specified in the corresponding cell. The group hierarchy can be specified by separating levels with colons. To include an actual colon in the group, use two colons.

Warning

The UniverseLabel and UnitTypeLabel columns update the label of the universe or unit type that a variable already references. If that item is shared with other variables or datasets, the new label applies everywhere it is used. To point a variable at a different existing item instead of renaming its current one, use the UniverseId or UnitTypeId column.

Note

The columns OutputVariableName, HeaderQuestion, HeaderQuestion2, and Logic are also recognized, and are stored as custom fields. They are retained for compatibility with existing metadata input sheets; for new sheets, use the custom: columns described below.

Referencing Existing Items by ID#

The CodeListId, MissingValuesId, UniverseId, and UnitTypeId columns link a variable to an item that already exists, rather than creating a new one. Each of these columns accepts any of the following ID formats.

Format	Example
UUID	`a4c34b0f-c6bc-4a1c-8b30-6d1d5f6b1f2a`
`agencyId:uuid:version`	`int.example:a4c34b0f-c6bc-4a1c-8b30-6d1d5f6b1f2a:1`
`urn:ddi:agencyId:uuid:version`	`urn:ddi:int.example:a4c34b0f-c6bc-4a1c-8b30-6d1d5f6b1f2a:1`

When only a UUID is supplied, Colectica uses the latest version of the item under your default agency ID.

Colectica looks for the item first in the repository you are working in, and then in each of your other connected repositories. If the item is found in a remote repository while you are working locally, Colectica automatically adds it to your local workspace so that the reference resolves. This means you can reference items that exist only in a repository, without checking them out beforehand.

If the item cannot be found in any connected repository, the summary reports the variable and the ID that could not be located, and the variable is left unchanged.

Multiple Languages#

The following columns support multiple languages.

Label
Description
QuestionText
UniverseLabel
UnitTypeLabel
Codes
MissingValueCodes
custom: and custom:question: columns

To specify a language for the content, append [{lang}] to the column name. For example, to specify question text in French, the column name should be QuestionText[fr]. When no language is specified, Colectica will set the content for the active metadata language.

Custom Fields in Metadata Input Sheets#

Custom fields can also be applied to variables using a Metadata Input Sheet. To apply information in custom fields on a variable, add extra columns that begin with custom:. For example, to add a field named Curator, add a column named custom:Curator.

To apply information in custom fields on a question, add extra columns that begin with custom:question:. For example, to add a field named Curator, add a column named custom:question:Author.

An example metadata input sheet with custom fields may look like this:

Name	Label	Description	Type	Codes	Topic	custom:Curator
name	The name of the respondent		Text		Admin	Abigail
marstat	The marital status of the respondent	A longer description can go here.	Code	1, Single \| 2, Married	Family	Bob
age	The age of the respondent		Numeric		Demographics	Cecilia

Custom Field Types#

When the column name matches a custom field that has been defined for variables, Colectica interprets the cell contents according to the type of that field. The following custom field types can be set from a metadata input sheet.

String: The cell contents are stored as written.
MultilingualString: Add a language suffix to the column name, such as custom:Notes[fr]. See Multiple Languages above.
Boolean: Use True or False.
Number: Use a plain number, such as 12.5. Use a period as the decimal separator. If the cell does not contain a valid number, the summary reports the problem and the field is left unchanged.
ControlledVocabulary: Use the code value of the term, rather than its label.

If no custom field with a matching name has been defined, the cell contents are stored as text.

Note

Custom fields on questions are always stored as text. The types listed above apply to custom fields on variables.

Other custom field types, such as dates and relationships, cannot be set from a metadata input sheet.