How to append data to a table using the Import Tool

EMu 3.2.01 added the ability to import records specified in either XML (eXtensible Markup Language) or CSV (Comma Separated Values). The new Import Facility enabled records to be created, including the creation of any records referenced. Hence it is possible to import a new Catalog record and have a Party record created for the Creator field if the Party record does not already exist. The Import Facility also provided a mechanism for updating records. Using the update feature it is possible to replace the contents of a field with new data. Unfortunately the complete contents of the field are replaced with the data imported.

In some instances it may be useful to update a list of values 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.

EMu 4.0.02 extends the Import Facility, allowing tables and nested tables of values to be updated rather than replaced. It is now possible to append, prepend and replace values at given positions with imported data.

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 Catalog 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 the following section we will look at the extensions added to the Import Facility to support the appending of data for 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 behavior 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.