Import Data Into Related Data Set
Goal
To import data into a Related Data Set.
Prerequisites
The data to be imported is saved in a CSV or XML file. If the data is saved in a CSV file, the Related Data Set and its structure are in the system.
When a data set is imported from a CSV file, the columns (structure) must be in the Related Data Set. With an XML file, both the structure (the table's columns) and the data can be added during import. For more information, see Structure of XML and CSV Files (Related Data).
Recommendation
It is recommended that key values in the imported files are entered in lowercase. Using uppercase will not stop the import but will affect filtering because all uppercase key values will be changed to lowercase upon import.
Background Information
There are several different ways to upload data into a Related Data Set. These instructions describe how to import data into a related data set manually.
If source data is updated regularly, you can create a Time-based Automation that automatically imports the data according to a schedule. For more information, see Related Data: Import Data.
The Mapp Engage API can send instructions to create and update related data sets via SOAP or REST. For more information, see developers.mapp.com.
File Structure
Files can be imported into a related data set in either XML or CSV format.
To import a CSV file, first create columns in the related data set out of the structure that is used in the data (see Import Data: Create Columns of Related Data Sets During Import). These columns must be created before the data is imported. With an XML file, both the structure (such as the columns of the table) and the data can be added during import.
This structure can be created in the user interface of Mapp Engage and during data import.
XML File
This example must be adapted to the structure created in the related data set.
Example:
<relatedData>
<row key="123456">
<column name="Productname">shoe</column>
<column name="Date">11.01.2011</column>
<column name="Price">10</column>
</row>
<row key="123457">
<column name="Productname">skirt</column>
<column name="Date">15.01.2011</column>
<column name="Price">20</column>
</row>
</relatedData>| Metadata / tags | Description |
|---|---|
| row key | This element represents a data record. All values included in the element are saved in a common row. The following tags describe the columns, which contain the individual data that belongs to this data record. The row key does not need to be set up as a column in the related data set. The row key can be linked to an attribute to create a connection between an attribute and a related data set. To check a data record, you can search for the entry in the row key. Depending on the settings when the data record was created, the entry is either row key unique or non-unique (for examples, see ). |
| column name ="<Name>" | This tag marks a single piece of information that is stored in a data record. All tags must be created as a column in the data record. The column name must be identical to the created column name. The name can only contain letters, numbers, and hyphens. Spaces or special characters generate an error. Depending on the character encoding of the data, different character encodings can be used in the tag. This encoding must also be indicated when importing the file into the related data set. For more information, see Related Data: Import Data. |
XML files for data import and building the structure of the container
This example includes the <metadata> element which creates the structure of the related data set. The structure and data can be imported in a single XML file.
<relatedData>
<metadata>
<columnDefinition name="Productname" type="String" default="shoe">
<enum>shoe</enum>
<enum>skirt</enum>
</columnDefinition>
<columnDefinition name="Date" type="date"></columnDefinition>
<columnDefinition name="Price" type="String"></columnDefinition>
</metadata>
<row key="123456">
<column name="Productname">shoe</column>
<column name="Date">11.01.2011</column>
<column name="Price">10</column>
</row>
<row key="123457">
<column name="Productname">skirt</column>
<column name="Date">15.01.2011</column>
<column name="Price">20</column>
</row>
</relatedData>| Metadata / tags | Description |
|---|---|
| <metadata> | Shows the area for defining the file structure of the data record. |
| <columnDefinition> | This tag marks the area for defining the data column. |
| <column name> | This tag determines the name of the column. The name must correspond to the entry used for the file transfer in the attribute key. The name of the data column can only contain letters, numbers, and hyphens. Spaces or special characters generate an error. |
| type | This attribute determines the type of data:
|
| default | A value that is always entered when no other value is entered in the column. You do not have to fill in this input. |
| <enum> | This tag creates an enumeration (that is, all possible values are defined). All possible values of the enumeration are listed in the tag. |
After the metadata are defined, the data set to be imported can be specified (see Structure of XML and CSV Files (Related Data)).
CSV files
The CSV file must contain the column names that were created in the related data set in the first row. The first column is always the identifier for the data record. This identifier is not created in the related data set when the structure is being built.
For more information, see Related Data: Import Data.
Example
"Key","Purchase-ID","Article","Price"
"123456","123","jacket","179,95"
"123457","124","shirt","99,95"
"123458","125","trousers","119" The names that are displayed in the first row correspond to the columns created in the related data set. You can select any character to separate the fields or columns, but this character must be configured appropriately when the file is imported.
Use the field delimiter " to enable the use of special characters in the data records. In addition, the correct character encoding must be selected when saving the file so that the imported file can be displayed correctly. This character encoding must be indicated when importing the file into the related data set. For more information, see Related Data: Import Data.
Procedure
- In the top navigation, click Administration > Attributes > Related Data.
- In the Actions column, click
Add New Data. The Add New Data window opens. - Click the Choose File button to locate and select the file that you want to import from your local directory.
- Click the File Type option that corresponds to the source file format.
- If the source file format is CSV, enter the separator character in the Separator field.
From the drop-down list Import Mode, select how the records are imported from the source file. The following options are available:
Import mode Description Add This option saves new records from the import file to the data set without changing any existing records. Replace This option overwrites all existing records in the data set with the records from the import file, including the columns. You can use this import mode to add new data without using an empty related data table. Update This option replaces existing records in the data set with the corresponding record from the import file. Only existing records that have a key that matches a record in the import file are replaced. Records in the data set that do not correspond to any records in the import file remain unchanged. This option does not add any new records to the data set. Update and Add This option replaces existing records in the data set with the corresponding record from the import file and adds new records to the data set. Records that were already saved in the data set and lack a corresponding record in the import file remain unchanged. Records with new keys are added to the data set. If either update options are selected, the additional option Synchronization Mode is displayed.
The update modes Update and Update and Add replace all existing records in the related data set with records in the import file that contain the matching key column values. In non-unique data sets, this means that the data set can actually contain fewer records for a specific key value after import. You can have up to 800 similar keys.
For example, a data set called Purchase has five purchase records for a key-value Customer ID. The import file only contains three records for the same key value. The update modes Update and Update and Add delete the original five records and leave only the three new records from the import file in the data set.
If the Import Mode is set to an update option, click the Synchronization Mode option to refine how the update mode functions. The following options are available:
Synchronization Mode Description Overwrite existing rows completely This option replaces all values (columns) in the data set (row) with the corresponding new record from the import file. As a result, column values in the row that are not part of the import file are deleted. Overwrite only columns from file This option is only available for unique data sets. Only the value (column) in the data set (row) is replaced with the new record from the import file, other values in the row remain unchanged. From the drop-down list Error Mode, select how errors are handled during data import:
Error mode Description Skip Value If an error is encountered in the data set, only the value which contains the error (such as the column) is not imported. If there are other values (columns) in the data set that can be imported, they are imported. Skip Row If an error is encountered in the data set, the entire row which contains the error (such as one data set) is not imported. - From the drop-down list Encoding, select how the source data is encoded. This selection ensures that characters in the imported data sets are displayed correctly.
- Click the Import button. The data import begins and the window Related Data Overview (window) opens. A status message displays information about the import. When the import is finished, a confirmation email is sent.