Importing with CSV files in ReflexBlue

For import definitions that allow you to import data from CSV files into ReflexBlue, there are configuration settings available that let you determine which data you import and how it is linked and processed.

This guide explains how to configure CSV import definitions in the ReflexBlue Desktop Client.

Configuring a CSV import definition

To configure a CSV import definition, first open an import definition from the Import/Export Definitions overview screen. In the General tab of the detail screen, there are sub-tabs available with configuration settings.

Receive CSV tab

The rows in a CSV file represent entities in ReflexBlue. In this tab, you specify what type of entity each row represents.

• Select New to add an entity.
• Select Delete to remove an entity.

Follow these steps for the configuration:

1. Configure one or more entities based on the structure of the CSV file.
2. Does the first row of the CSV file contain a header? Then enable Has header.
3. Is the data separated by a comma (,) or semicolon (;)? Then select Auto at Delimiter. Otherwise, use the correct character.
4. Set the desired Quotation mark if values in the file are enclosed by, for example, " or '.
5. Are all values enclosed by quotation marks? Then select All at Quotation mark usage. Only some values? Choose Where necessary. Never? Select None.
6. Select the correct file encoding. The encoding determines how characters are stored and read. Without the correct encoding, strange characters may appear after importing. In Western Europe, Windows-1252 encoding is often used for CSV files.

Link Lines tab

The option Check for duplicate lines is enabled by default when one or more relations are set. You can only disable this option if no relations are configured.

Multiple entities in one file

Are there multiple entities in one file? Then configure the relations between these entities. This way, the system understands how they relate to each other.

The example below shows a CSV file with three entities: order, order line, and order line registration.

"O1", "L1", "R1"
"O1", "L2", "R2"
"O1", "L1", "R3"
"O1", "L2", "R4"
"O2", "L1", "R5"
"O2", "L2", "R6"
"O2", "L1", "R7"
"O2", "L2", "R8"

This file contains two orders, each with two order lines (four in total) and two registrations per order line (eight in total).

A correct configuration for this structure is as follows:

• Parent type: entity://orders.order
  Parent position: A
  Child type: entity://orders.orderline
  Child position: A;B

• Parent type: entity://orders.orderline
  Parent position: A;B
  Child type: entity://orders.orderlineregistration
  Child position: A;B

Link Fields tab

For each field in the CSV file, you link the field to an entity field in the system. You choose from a fixed set of entity fields. For a new definition, default fields are already added.

Follow these steps:

1. Adjust the positions based on the structure of the CSV row. To do this, edit the cell under Position.
2. Is a value required in ReflexBlue, but possibly empty in the CSV? Then specify a Default Value.
3. Deselect Overwrite if the value should not be overwritten in ReflexBlue.
4. Do you want to sort the keys by position? Then select Sort by position.

Script tab

Use a script to modify data. For example, if the format is incorrect or if a value needs to be converted during import.

The example below converts a date from day-month-year to year-month-day:

var deliveryDate = GetColumnValue("ORDER_DELIVERYDATE");
if (!string.IsNullOrEmpty(deliveryDate))
{
    var parsedDate = DateTime.ParseExact(deliveryDate, "dd-MM-yyyy", CultureInfo.InvariantCulture);
    parsedDate = parsedDate.AddDays(0);
    var universalDateFormat = parsedDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
    SetColumnValue("ORDER_DELIVERYDATE", universalDateFormat);
}