You can manually Import data into a list from a CSV. This article describes how to populate List data using a CSV file.
⚠️ Important
To import data into Pigment, you must have the Import Data permission. Ensure that your Role has the necessary permissions to add or remove Items in the List where you are importing data.
For more information on importing data using integrations, see Active Pigment Connectors.
You can also create a List manually. For more information, see Set Up Properties in Lists.
Populate a List with CSV file data
You can use CSV data to populate existing lists or create a new list:
Existing List. Import all the List Items and Property data. Open your Block and select Import data.
New List. Create a new List and load data starting from an import. After you select New Transaction List or New Dimension List, select Start from an import.
Load source data
ℹ️ Note
You can also populate a List by importing data from a Snapshot. This can be useful for recovering List data from a recent point in time. For more information, see Set up List-to-List import.
You can load data into Pigment using the following methods:
Upload file. Load data by uploading a local CSV file.
Integration. Load data using an existing Integration.
Once you’ve loaded your data, select Set up import.
Step 1: Define the file structure
When your data is uploaded, you can configure the source file structure to ensure that it’s correctly read in 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.
See settings available for header formatting in flat data layout in the table below:
Header setting | Description |
|---|---|
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. |
See settings available for data formatting in flat data layout in the table below:
Data setting | Description |
|---|---|
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.
ID | Name | Country | Gender |
|---|---|---|---|
1 | Bob | FR | M |
2 | Mary | DE | F |
3 | Lisa | UK | F |
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.
See settings available for header formatting in pivoted data layout in the table below:
Header setting | Description |
|---|---|
Headers | Toggle On if the file has column headers. This enables the Row number field. |
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. |
See settings available for data formatting in pivoted data layout in the table below:
Data setting | Description |
|---|---|
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) | Select + 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. |
See settings available for header formatting in pivoted data layout in the table below:
Country | Jan 21 | Feb 21 | Mar 21 | Apr 21 | Jun 21 | Jul 21 | Aug 21 |
FR | 100 | 200 | 150 | 45 | 100 | 200 | 80 |
DE | 200 | 100 | 200 | 150 | 45 | ||
UK | 160 | 100 | 200 | 150 | 45 | 0.00 | 0.00 |
Enrich Data
See settings and details available for Enrich data in the table below:
Setting | Description |
|---|---|
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: |
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. Select + Add. 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. 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, select 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.
ℹ️ Note
If you have multiple date formats, custom-formatted dates, or numbers with prefixes and suffixes, you can define settings for each column individually. Select the Options icon next to each Property you want to configure. For more information, see How to set Column Specific Data Definitions in Imports.
See number formats available in the table below:
Format | Description |
|---|---|
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. |
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.
See date formats available in the table below:
Format | Description |
|---|---|
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 define how the import should behave when data cannot be imported because of format errors or missing items.
On values format error
Use On values format error to specify how the import should behave when an invalid Date or Number format is encountered, see details in table below:
Setting | Description |
|---|---|
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. |
Automatically add new items to dependent Dimensions
Use the Automatically add new items to dependent Dimensions drop-down menu 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.
See available settings in the table below:
Setting | Description |
|---|---|
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. |
ℹ️ Note
To configure settings source by source, select the Options icon next to each Property you wish to configure.
Properties mapping
In the Properties mapping section, select the corresponding datasource column for each List Property. If you haven’t created Properties already, select + 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.
ℹ️ Note
A Member can map a source field to both a Property and a Dimension, provided that the Property and the corresponding Property of the Dimension share the same data type. This mapping is not restricted to text fields and applies to various data types.
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:
Enter the new Dimension name in the Search bar.
Select Create.
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 selecting 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.
Select 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.
Additional options
For details, see the following full articles on additional import options:
When you’re finished, select Import.
Import summary and saving your configurations
When 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.
ℹ️ Note
You can view a summary of completed imports in the Update History section of the Block. Import summaries are retained for 90 days.
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).