Structure of XML and CSV Files (Related Data)

Introduction

Files can be imported into a related data set using either XML or CSV formats. This document outlines the prerequisites, structure, and best practices for successfully importing data.

Prerequisites

Before importing data, ensure that:

• For CSV files, the required columns must be created in the related data set beforehand.

• For XML files, both the structure and data can be added during the import process.

• The column names are case-sensitive and must exactly match between the import file and the Related Data table (e.g., OrderID and orderid are different).

• The import file must contain at least one data column in addition to the key column.

• The import file must not contain more columns than the Related Data table.

• The maximum length for Related Data text columns is 2,000 characters, and for searchable strings (used in segmentation), it is 200 characters.

XML File Structure

The XML file structure must align with the columns defined in the related data set.

Basic XML 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 for XML File

The <metadata> section defines the structure of the related data set, ensuring that column attributes are properly configured.

<relatedData>
    <metadata>
        <columnDefinition name="Productname" type="String" default="Shoe">
            <enum>Shoe</enum>
            <enum>Skirt</enum>
        </columnDefinition>
        <columnDefinition name="Date" type="date"/>
        <columnDefinition name="Price" type="String"/>
    </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>

Data Type Attributes in XML

• number: Accepts numeric values only.

• date: Accepts values in date formats such as:

  • DD.MM.YYYY

  • YYYY-MM-DD

  • YYYY-MM-DD HH:mm:ss

  • ISO formats: YYYY-MM-DD'T'HH:mm:ss.

• string: Stores alphanumeric strings.

• boolean: Accepts true, false, or empty values.

• enum: Defines a list of acceptable values.

CSV File Structure

CSV files must follow a specific format for successful import.

Key Considerations for CSV Files

• The first row must contain column names that match the related data set.

• The first column serves as the identifier and is not created during structure setup.

• The field delimiter (e.g., , or ;) must be properly configured.

• Field values should be enclosed in quotes (" ") to handle special characters.

• The correct character encoding must be set before import.

CSV Example

Key, Purchase-ID, Article, Price
"123456","123","Jacket","179.95"
"123457","124","Shirt","99.95"
"123458","125","Trousers","119"