Overview
As well as supporting the creation of new records the Import tool provides a mechanism for updating records; this includes the ability to update tables and nested tables of values with imported data by:
- appending values after a given position or after the last value.
- prepend values before a given position or before the first value.
- replace values at a given position.
The Group mechanism allows separate columns to be tied together when appending data, ensuring all values are placed on the same row in each column. For example, when updating a Catalogue record you may want the Creator and Date Created values to appear on the same row, as each piece of information is related to the other.
In this section we look at the Import tool's support for appending data in both XML and CSV based data sources.
The EMu Import Facility allows XML or CSV to be used for defining records for importing. The XML format used is the same as that produced by the EMu Reporting Facility, while CSV is a de facto standard for data interchange. Consider the XML below:
<table name="ecollectionevents">
<tuple>
<atom name="irn">5000432</atom>
<table name="LatSource_tab">
<tuple>
<atom>Map Number 2314</atom>
</tuple>
</table>
</tuple>
</table>
or the equivalent CSV:
| irn | LatSource_tab(1) |
|---|---|
5000432
|
Map Number 2314 |
When imported, all values in the LatSource_tab column will be replaced with the value Map Number 2314. To provide support for updating rows in a table a new row attribute has been added to the <tuple> tag for XML, allowing a row position to be specified. The row attribute provides a mechanism for indicating what type of update should be applied and which row is affected. The format of the attribute is:
<tuple row="value">
For a CSV data source the row number appears between brackets. CSV allows two formats to be used to specify the row attribute:
(row='value')
or the shorter form:
(value)
Note: If not using the shorter form, use single quotes rather than double quotes to enclose the row value in CSV files. When saving CSV files in Excel, incorrect output will be produced if double quotes are used when specifying the row value.
The value of the row attribute may be:
| nnn | where nnn is a row number. The number indicates the row position in the list of values to be modified. The first row is numbered 1. In essence this setting replaces the current value at that position. |
| + | indicates the row position is after the last value in the table. Any data will be appended to the list of values. |
| - | indicates the row position is before the first value in the table. Any data added will be put at the start of the list with existing values moved down. |
| = | indicates the row position is row one. Any data added will replace the existing value at this position. |
| nnn+ | appends any data after row number nnn. |
| nnn- | prepends any data before row number nnn. |
| nnn= | replaces any data at row number nnn. |
In all cases, if the new row number does not exist, it is created. For example, if the row setting is:
<tuple row="12+">
or for CSV:
(row='12+') or (12+)
and there are not twelve values in the table, the table would be padded out to twelve values (with empty rows) and the new value appended, creating a thirteenth row. If a row attribute is not defined, the default behaviour is to append to the end of the table.
In order to provide backwards compatibility with the existing Import Facility and to allow all values in a table to be replaced, the first <tuple> in a table for XML, or the first row specifier for CSV, define whether the values in the table are being updated or whether all values are being replaced. If the row attribute is not specified in XML, or just a row number is specified, then the contents of the column will be cleared and the imported values added. If a row attribute is provided and it contains an update operator (that is +, - or = with or without a leading row number), the contents of the table are updated, with the existing values retained.
The following section contains examples on how to replace or update the contents of a table using the new row attribute. The examples provide both XML and CSV solutions.
