Topics

CSV files format

Overview

You can include information about interactions, client metadata, or both in the same CSV file. Sitefinity Insight uses the name of the headers to understand what kind of information is contained in a CSV file. If you include information about both interactions and client metadata, Sitefinity Insight will import both in one operation.

If you create a CSV import file mixing different kind of records, it may contain records only for interactions, only for client metadata, and records combining the two data sets. In the first two cases, the rules of the CSV files require that you create empty fields for the information which is not part of the record. To learn more about the terms used in this article, see Developer terminology.

EXAMPLE: In the following sample file, you can see an example of an import CSV file containing combined records (lines 2-4), a record for interactions data only (line 5), and a record for client metadata (line 6). You may use it as a template for constructing your import CSV files. The meaning of the different columns is explained below.

Interactions columns

If you want to record interactions in the CSV file that you want to upload, it must have the following columns with the following headers:

  • UniqueId
    Required for both Interactions and Client metadata records
  • Predicate
    Required for Interactions records
  • Timestamp
    Required for both Interactions and Client metadata records
  • Object
    Optional for Interactions records

If you are uploading only interactions, then your file must contain data in these columns only. If you want to upload client metadata in addition or instead of an interaction, then after these columns, you can create additional columns (headers).

Client metadata columns

When creating a custom column containing client metadata, there are the following requirements for the column header name:

  • The header name cannot be empty or consisting only of a whitespace.
  • Header names are not case-sensitive.
  • Sitefinity Insight ignores any leading and ending whitespace characters.
  • The names of the custom headers must be maximum 32 characters long.
  • Headers can contain custom names that you create. For the name, you can use any string except UniqueId, Predicate, Object, and Timestamp.

    EXAMPLE: Email, First Name, Last name, Address.

If your CSV file contains both interactions and client metadata, but you want this record to contain  only client metadata, you must leave the Predicate and Object columns empty, and fill out only the custom columns that you have created.

NOTE: If you fill out both the columns for interactions and the columns for client metadata, you can upload both interactions and client metadata with the same CSV file. Regardless of whether you are uploading an interaction or a metadata, your records must have the UniqueId column filled out.

Create record values

When creating the record values, keep in mind the following requirements:

Header Requirements
UniqueId
  • You must fill out this field for every record.
  • Cannot be empty or whitespace characters only.
  • The value must have less than 256 characters long.
Predicate
  • You must fill out this field if you are defining an interaction.
    You can leave it empty for client metadata.
  • The value must be less than 64 characters long.
Object
  • Can be empty or whitespace even for lines defining an interaction. Empty or whitespace values will generate a Warning to prompt you to check if this omission was intentional.
  • Value must have less than 2048 characters long.
Timestamp
  • You must fill out this field for every record.
  • Value must be a valid ISO Date Time string. For more information, see ISO 8601.
    Supported formats:
    • UTC time zone: 2021-02-25T15:36:45.250Z
    • Offset time zone: 2021-02-25T18:36:45.250+3:00
    • Variable milliseconds digits – from 0 to 7 digits after the decimal point.
    • No seconds and milliseconds digits: 2021-02-25T15:36Z
    • No time component: 2021-02-25
    • Earliest timestamp value: 2015-01-01T00:00:00.000Z
    • Maximum timestamp value: Today + 2 years and one day
  • If no time zone information is available, the timestamp value is assumed to be in UTC.
Client metadata values - Text
  • Can be empty or whitespace only.
  • Value must have less than 256 UNICODE characters long.
Client metadata values - Number
  • When importing the data, Sitefinity Insight uses the property name to discover the value’s type as it was defined in the UI.
  • If the property is of type Whole Number, the accepted range is from 2,147,483,648 to 2,147,483,647.
  • If the property is of type Decimal Number the accepted range is – 999,999,999,999.9999999 to 999,999,999,999.9999999 with up to 7 digits of precision after the decimal point.
  • Sitefinity Insight accepts only a comma (,) as the thousands separator and a period (.) as the decimal separator.
  • If Sitefinity Insight cannot parse the value from the CSV file for reasons such as the values are outside of the specified ranges, use symbols for digit separators other than the specified, or for any other reason, the behavior depends on how the CSV file is imported:
    • If you import a CSV file using the Web interface on insight.sitefinity.com, you receive an error and the import stops.
    • If you use one of the automated ways to import CSV files, such as using Workato or Azure Data Factory, the value is skipped during the import process. Therefore, the contact will not have changed the existing value, or it will be empty only for this particular property, the import process continues, accepting other valid properties for this contact or other contacts, if any.
Client metadata values - Date
  • Sitefinity Insight accepts Date values only in the format of yyyy-MM-dd. You may also use as separators short dash (-), forward slash (/), and space.
  • If you use ISO 8601 compatible date and time representation, such as yyyy-MM-ddTHH:mm:ss(.ffffff)Z, yyyy-MM-ddTHH:mm:ss(.ffffff)+03:00, or yyyy-MM-ddTHH:mm:ss(.ffffff), Sitefinity Insight silently ignores the time and the timezone part of the record and accepts the date portion as provided without considering the specified time zone.
Client metadata values – Yes/No
  • Sitefinity Insight accepts the texts True, 1, and Yes as equivalent to Yes
  • Sitefinity Insight accepts the texts False, 0, and No as equivalent to No
  • Parsing is case-insensitive – for example True, true, or tRUE are all valid values and are accepted as the value Yes.

NOTE: Sitefinity Insight ignores any leading and ending whitespace characters in filed values. To represent a comma in a value, enclose the value in double quotes.
Leading and ending whitespace characters outside the double quotes will cause the value to become unreadable. Remove any such characters before importing the file.

Sitefinity Insight considers a record to be valid if there is either value for the UniqueId attribute and either a Predicate attribute or at least one non-empty Client Metadata value.

Examples

To review examples of valid CSV import files, see the following samples:

  • Client metadata:
  • Interactions:
  • Combined data – both client metadata and interactions:

Was this article helpful?