In Pigment, importing data from a CSV may be necessary when building or updating a list. This article describes how to populate List data using a CSV file. For more information on importing data using integrations, see Active Pigment Connectors. 

Populate a List with CSV file data

You can do one of the following:

• If your List already exists, you can import all the List Items and Property data. Open your Block and click Import data.
• Create a new List and its data starting from an import. After you click New Transaction List or New Dimension List, click Start from an import. 

Load source data

First, you need to load your data into Pigment. There are two ways to do this:

• Upload a local CSV file.
• Load data using an existing Integration.

Once you’ve loaded your data, click Set up import.

Step 1: Define the file structure

This step describes how to configure the source file structure to ensure that it’s correctly read by Pigment.

File settings

• Encoding. If some special characters are not interpreted correctly in the preview, it is likely related to the encoding setting. Check which encoding is being used by your source file.

• Column separator. Select the separator between each column: semicolon (;) or comma (, )

• Text delimiter. Select the text delimiter double quote (”)or single quote (’)

File data

Pigment supports two different types of data layout: flat or pivoted.

1. Flat data layout

Flat data layout is designed to support import files that contain only column headers. Each line represents a different List item. Flat files are more suited to import to a List.

When the Flat data layout is used, the following options are available:

• Headers
  • Include a header. Toggle On if the file has column headers. This enables the Row number field.
  • Row number. Enter the row that contains the headers.

• Data
  • First row. Specify the starting line of the file that contains the data you want to import into your List Items. This is useful if you want to skip any initial lines in your file.

The following table is an example of a flat file.

2. Pivoted data layout

Pivoted data layout is designed to support import files that contain headers in both rows and columns. The Pivoted mode is more suited to import data into a Metric.

When the Pivoted data layout is used, the following options are available:

• Headers 
  - Column headers. Enter the number of column header.
  - Starting at row. Enter the starting row of column header.
  - Row headers. Enter the number of row header.
  - Starting at column. Enter the starting column of row header

• Data
  - First cell. Enter the row and column number of the first cell of data (top left)
  - Last cell. Enter the row and column number of the last cell of data (bottom right)
  - Exclude empty values. Toggle on this option to ignore empty data cells, and to prevent them being imported into the Block.
  - Exclude specific value(s). Click + Add value to specify which values should be excluded during the import.
  These values are case sensitive, and any numbers with decimals must match exactly.

The following table is an example of a pivoted file.

Enrich Data

• Add load date

This option creates a new column in your import with the time and date of your data load. When you toggle on Add load date, the Select a time zone menu appears. By default, this is set to your local time zone, however, you can adjust it to whichever time zone you need.

The date can be imported into text or date-formatted mappings, and is in the format: yyyy-mm-dd hh:mm‏‏‎ ‎:ss

• Additional constant value 

Additional constant values allow you to add mappable data to your source file. The data can only contain one value for the entire import.

1. Click + Add.
2. Enter a name in the Source name field. This is used to define the column that will be added to your file. This is the field that you will map in Step 2.
3. Enter a name in the Value field. This is the value that will be imported.

For example, let’s say you have a file for a particular product, Product A. However, the data was not in the file. You can create an Additional constant value that would have the Source name of Product and the Value field set to Product A.  This data would appear on the file in Step 2, and you could then map it to the correct source.

When your configuration is complete, click Next step: Map the data.

Step 2: Map the Data

Now that you’ve defined the file structure, you need to configure where the data should go and how to read the data within the file.

Values format

You can use Values Format to determine how Pigment should interpret certain data types, such as Date and Number formats. This setting applies by default to all mapped data.

Number Format

• 1,123.45: Select this option for numbers with a period (.) as the decimal separator and a comma (,) as the thousands separator
• 1 123,45: Select this option for numbers with a comma (,) as the decimal separator and a blank space as the thousands separator

Date Format

Most standard date formats are correctly interpreted by any of these options. However, for ambiguous date formats, localizing the date format can help ensure accuracy:

• US. Follows American Date format standard. For example, “01-02-2022” is Jan 2nd, 2022.
• GB. Follows European Date format standard. For example, “01-02-2022” is Feb 1st, 2022.
• FR. Follows European Date format standard. For example, “01-02-2022” is Feb 1st, 2022 and understands Date written in French words. For example, “Février 22” is Feb 1st, 2022.

Data validation

This section lets you specify the import behavior when data cannot be imported due to errors or missing items.

On values format error

Specify the import behavior when a Date or Number format is invalid.

• Fail import: If a value format error is encountered, the import will not complete.
• Replace with blank: If a value format error is encountered, the value is ignored and replaced with a Blank Value.

In case of missing Items

Use the In case of missing Items dropdown to specify the import behavior when the data source contains items that are not found in dependent Dimensions.

For Example: If you configure an import in an Employee List containing a Team column, which will be mapped to a property of type Dimension (Team). This setting determines how the import process handles cases where the data source contains a team that doesn’t exist in the Team Dimension.

• Replace with blank: Missing Items are replaced with a blank. The import will complete but data will be partially imported.
• Fail import: If any missing Item is encountered, the import will not complete.
• Auto-create Item: If a missing Item is encountered, it’s automatically created in dependent Dimensions and the data is correctly imported.
• Reject row: Rows with missing items are skipped but all other rows are correctly imported.

Properties mapping

In the Properties mapping section, select the corresponding datasource column for each List Property. If you haven’t created Properties already, click + New Property.

After you align the Property with the Source column, you need to select the data type. For more information, see Data Types in Pigment.

Dimension data type

If you set your Property data type to Dimension, a new Dimension menu appears. Enter the Dimension name in the Search bar, or select it from the dropdown.

If you haven’t created the Dimension yet, you can create it during this import process:

1. Enter the new Dimension name in the Search bar.
2. Select Create.
3. Select Automatically add new items to dependent Dimensions. This creates new Items in that List.
   • Automatically add new items to dependent Dimensions. When you use this functionality, new Items are added to all dependent Dimensions by default. You can toggle this setting on and off as needed.
   • Dimension unique property. When you map Property with a Dimension data type, a cog wheel appears next to the selected Dimension. Here you define how Pigment identifies the Items in the Dimension.  The Dimension’s default Property is selected by default, but you can select any other unique Property of the Dimension by clicking the cogs beside the Property.  Let's say you have a Country list. In this list, there's a unique property called Country Code. If your file contains country codes instead of country names, you can use the Country Code to identify each country.

Customize column data definitions 

If you have multiple data formats such as custom date formats, prefixed or suffixed data, and other specific cases, you can set column-specific data definitions during an import. This allows you to adjust the data format for each Property.

Click the Option icon beside the required Property to define how Pigment should interpret that data. For more information, see How to set column specific data definitions in Imports. 

Automatically generate List Properties from unmapped columns

To use the Autofill source fields setting, you must create the new List from Import. Autofill source fields automatically creates a List Property for any unmapped columns in your List import. Pigment names each Property based on the column header. However, you'll need to manually adjust the data type, as all Properties are initially set to Text.

Manage Item deletion before importing transactions

In Transactions Lists, use the Clear Items setting to clear Items prior to import.  This allows you to clear Items present in your Transaction List and replaces them with Items from the imported data.

When this setting is enabled, you can choose one of two ways to specify which Items are deleted from the List:

• Delete the entire List.
• Define a specific set of Items to be deleted.

For more information, see Clear Items prior to import in Transactions Lists.

Check column mappings for errors

Each CSV column that is successfully mapped is colored in blue. However, when you import data into a number or date-typed Property, values that cannot be parsed correctly are highlighted in red. If there are errors, you cannot complete the import until the issues are resolved. However, you can toggle on the Ignore values with data format errors setting. When this setting is enabled, blank values will be imported instead.

When you’re finished, click Import.

Import summary & saving your configurations

Once the import is finished, a report will show how many items have been created and updated in the List.

If the "Automatically add new Items to dependent Dimensions" option has been enabled, you will see the number of new items that were created during the import under Dependent Dimensions. By hovering over the (i) you will have a sample (up to 10 values) of created items. 

If you plan to run this import multiple times, you can save the import configuration (Step 1 and Step 2) by selecting the "Save as new.." option. It can be triggered either before launching your import (step 2) or after the import (in the import summary).  You can also replace an existing configuration for imports with the same data source. 

Save your import configuration

If you plan to run this import multiple times, you can save the import configuration (Step 1 and Step 2) by selecting the "Save as new.." option. It can be triggered either before launching your import (step 2) or after the import (in the import summary).

To reload this saved configuration, use the "Load existing configuration" button (Step 1).

This will load the configurations for both step 1 (define the file structure) and step 2 (data mapping).