Difference between revisions of "Bulk Editing Of Representation Maps"
Jump to navigation
Jump to search
(→Mapped Values) |
|||
| (16 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Category:How_To]] | ||
= Overview = | = Overview = | ||
It is possible to export a Representation Map into Excel format, edit it and then load it back in again. This allows for bulk addition or editing of Mappings within a single Representation Map. | It is possible to export a Representation Map into Excel format, edit it and then load it back in again. This allows for bulk addition or editing of Mappings within a single Representation Map. | ||
| Line 6: | Line 7: | ||
== Format of Excel Spreadsheet == | == Format of Excel Spreadsheet == | ||
| − | The spreadsheet is split into 3 sections | + | The spreadsheet is split into 3 sections separated by blank rows: |
| + | |||
| + | [[File:00-Example-Mapping-Excel.png||An example Mapping Spreadsheet]] | ||
| + | |||
* The "header" - the first 11 rows of the Spreadsheet describe mandatory information regarding the maintainable aspects of the Representation Map. For example, the Agency, ID and version as well as optional values such as Description and Start and End Dates. | * The "header" - the first 11 rows of the Spreadsheet describe mandatory information regarding the maintainable aspects of the Representation Map. For example, the Agency, ID and version as well as optional values such as Description and Start and End Dates. | ||
* The "source to target" statement - this next "block" Rows 13 and 14 describe the format of the Source and Target | * The "source to target" statement - this next "block" Rows 13 and 14 describe the format of the Source and Target | ||
* The mapped values is the final section. The format of this is a little complex so requires explanation. | * The mapped values is the final section. The format of this is a little complex so requires explanation. | ||
| + | |||
| + | |||
=== Mapped Values === | === Mapped Values === | ||
| − | The first columns are for the Source | + | The first columns are for the Source values. The source values are delimited by the column containing "--->". The subsequent columns are for the target as well as the "Valid From" and "Valid To" dates. |
| − | * A | + | * A '''Direct Mapping''' of one (or more) values to to another value is simply listed with the values in the appropriate column. The following illustrates how the 2 source values make a single target value. So an input of "S11" and "L" would result in the value of "EJBA" |
[[File:01-Mapping-ValueToValue.png]] | [[File:01-Mapping-ValueToValue.png]] | ||
| − | * A Regex | + | * A '''Regex Mapping''' where one or more of the source values are Regular Expressions is expressed by the use of the phrase "regex::" at the start of the cell. All values subsequently are treated as a regular expression. The following example illustrates how an input which starts with a "N" for one value and "W2" for the second value maps to "RHGA" |
| − | * A substring | + | [[File:02-Mapping-Regex.png]] |
| + | * A '''Substring Mapping''' is where a substring of the input is used to map to the output. This has the format substr( <start position>, <end position>, <value> ). The following example illustrates how the first 2 characters from "IND" map to the value "KFTB" | ||
| + | [[File:03-Mapping-Substring.png]] | ||
| + | === Regular Expression Examples === | ||
| + | The following specifies some simple examples of how to use Regular Expressions in an Excel Spreadsheet | ||
| + | * regex::W2 - will match if the input is the phrase "W2" | ||
| + | * regex::^W2.* - will match if the input starts with "W2" but can then be optionally followed by any other characters | ||
| + | * regex::^.*W2.*$ - will match if the input contains "W2" at any point | ||
| + | * regex::^[0-9]*$ - will match the input if it is numerics only | ||
| + | * regex::^.*W[0-9].* - will match the input if a capital W is followed immediately by any numeric, and this can occur anywhere in the input | ||
| + | |||
| + | == Loading Excel == | ||
| + | Once the Excel file has been succesfully edited, it can be re-submitted via the structure upload service which is found on the front page of the Registry. | ||
Latest revision as of 00:36, 12 September 2023
Contents
Overview
It is possible to export a Representation Map into Excel format, edit it and then load it back in again. This allows for bulk addition or editing of Mappings within a single Representation Map.
Exporting Excel
On the Representation Maps page, selecting a Representation Map and clicking "Export Excel" will create the Excel spreadsheet. It is recommended to edit an existing map rather than starrting from scratch as the spreadsheet must conform to a specific format.
Format of Excel Spreadsheet
The spreadsheet is split into 3 sections separated by blank rows:
- The "header" - the first 11 rows of the Spreadsheet describe mandatory information regarding the maintainable aspects of the Representation Map. For example, the Agency, ID and version as well as optional values such as Description and Start and End Dates.
- The "source to target" statement - this next "block" Rows 13 and 14 describe the format of the Source and Target
- The mapped values is the final section. The format of this is a little complex so requires explanation.
Mapped Values
The first columns are for the Source values. The source values are delimited by the column containing "--->". The subsequent columns are for the target as well as the "Valid From" and "Valid To" dates.
- A Direct Mapping of one (or more) values to to another value is simply listed with the values in the appropriate column. The following illustrates how the 2 source values make a single target value. So an input of "S11" and "L" would result in the value of "EJBA"
- A Regex Mapping where one or more of the source values are Regular Expressions is expressed by the use of the phrase "regex::" at the start of the cell. All values subsequently are treated as a regular expression. The following example illustrates how an input which starts with a "N" for one value and "W2" for the second value maps to "RHGA"
- A Substring Mapping is where a substring of the input is used to map to the output. This has the format substr( <start position>, <end position>, <value> ). The following example illustrates how the first 2 characters from "IND" map to the value "KFTB"
Regular Expression Examples
The following specifies some simple examples of how to use Regular Expressions in an Excel Spreadsheet
- regex::W2 - will match if the input is the phrase "W2"
- regex::^W2.* - will match if the input starts with "W2" but can then be optionally followed by any other characters
- regex::^.*W2.*$ - will match if the input contains "W2" at any point
- regex::^[0-9]*$ - will match the input if it is numerics only
- regex::^.*W[0-9].* - will match the input if a capital W is followed immediately by any numeric, and this can occur anywhere in the input
Loading Excel
Once the Excel file has been succesfully edited, it can be re-submitted via the structure upload service which is found on the front page of the Registry.
