Parse CSV action in NITRO Workflows and Custom Actions

Description

A CSV (Comma-separated values) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more columns, separated by commas.

Excel data is different from CSV. To convert the excel file to a CSV file, open the excel data file -> File tab -> Click Save As -> Select save as CSV file.

The parse CSV action is used to create items in a SharePoint list using the records from CSV data. It can also be used to update existing items in the list using a matching criterion.

CSV data can come from a SharePoint list item attachment or an item column that has the data in CSV format.

Run for:

This specifies the source for CSV data.

Current Item: Read the CSV data from current item.

Query List: Read the CSV data from one or more items fetched as part of a query list action.

Variable: Read the CSV data from a workflow variable.

Get CSV data from:

Select one of the options to read CSV data.

Attachments:

Select this option if CSV data is in one or more item attachments. We can filter the files by specifying the type of the file. For example, we can enter file extension as .csv.

Note that all matching attachment files will be processed if there are multiple attachments in the item.

Column:

Select this option if CSV data is in a column in the list item. Multiple lines of text type columns are listed for this purpose.

Target List Settings

Select target site and list to create or update the items.

Column Mappings

This section is used to map data from columns in CSV data to SharePoint list item columns. CSV column names are specified in square brackets. A fixed value is specified directly. A mapping can consist of both fixed value and one or more CSV columns. Please refer to below examples.

Column value: Specify the column name from CSV data/Fixed value.

Value type	Example
CSV column	[Invoice Number]
Custom Value	Ordered
Concatenation of one or more CSV columns/custom values	[Invoice Number] _[ID]

Advanced Settings

Update item if it already exists:

Check this option to update existing items. This can be used if the SharePoint list already has the items and the requirement is to add or update column data for these existing items. In this case, the first step is to find the matching item in SharePoint. If the item is found then it is updated, else a new item is created. If this option is not checked then a new list item is always created for every record in CSV data.

Item fetch query:

To identify the specific SharePoint list item to update for each CSV record, a CAML query needs to be specified. Include the required columns in query to fetch the item to update. CSV column data is specified in square brackets as shown in the example below.

<View><Query><Where><Eq><FieldRef Name='AssetID' /><Value Type='Text'>[Asset ID]</Value></Eq></Where></Query></View>

In this case, each CSV record has the Id of the Asset and this Id is also there in ‘AssetID’ column of the SharePoint list. For each CSV record, it will try to fetch the item with the matching Asset Id in the SharePoint list and update the item if it is found. If the list item with Asset Id is not found, a new item will be created in SharePoint list. Note that the query can have more than one condition and we can include more than one column from the CSV in the query.

Advanced Settings:

DateValType: By default, date time values in CSV are assumed to be in UTC. If these are in some other time zone, then specify the time zone name, for example – ‘Eastern Standard Time’. For a list of the time zone names, please refer to this article.

MaxCSVRowstoProcess: Specify the max number of rows to process from the CSV data. Default maximum rows to be processed for event-based (item added/updated) and async workflows is 200, and for timer based and scheduled workflows it is 500.

Batch Settings:

These settings are considered if ‘Update item if it already exists’ is checked. For every row in CSV data, a call is made to SharePoint to find the list item that needs to be updated. If there are several rows in CSV data then it involves numerous calls to SharePoint. It can also result in calls being rejected by SharePoint (HTTP 429 error). Batch settings can be used to reduce calls to SharePoint by querying multiple items in one call.

EnableBatch: Default value for this property is false. Set this to true to enable querying items in batches.
BatchSize: Specify the number of items to fetch in one call (default value is 50). Normally, there is no need to change this value.
BatchQuery: Specify the batch query. Please refer to the example below for these settings.
ValueType: Specify the data type of the SharePoint column that is used in the batch query. Normally this will have value ‘Text’.
UniqueColumnPlaceholder: Specify the column name from CSV data that contains the unique Id to find the items in the SharePoint list.
SPUniqueColumnPlaceholder: Specify the column internal name in the SharePoint list that has the matching unique Id value.

Batch Settings Sample:

CSV data format:

Title	AssetID	AssetType	Created Date
Dell Vostro	K342356	Computer	10/26/2021
HP Laser Jet Printer	H456456	Printer	10/26/2021

SharePoint list columns

Id – Auto-generated Id of the item. Not used in mapping from CSV as it is handled by SharePoint.
Title – This will contain the name of the Asset. Column type is a single line of text.
AssetID – Text type column that has the unique Id of asset.
CreationDate – Date on which the Asset was acquired. Column type is date and time.
AssetType – Type of the asset. Column type is choice.

{
	"DateValType": "Eastern Standard Time",
	"MaxCSVRowstoProcess": 500,
	"BatchSettings": {
		"EnableBatch": true,
		"BatchSize": 50,
		"BatchQuery": "<View><Query><Where><In><FieldRef Name='AssetID' /><Values>{values}</Values></In></Where></Query></View>",
		"ValueType": "Text",
		"UniqueColumnPlaceholder": "[AssetID]",
		"SPUniqueColumnPlaceholder": "[AssetID]"
	}
}

Use Case

Create invoice records in a SharePoint list

Description:

On Asset creation, workflow will read CSV file from the item attachments and create records in “Invoice” list.

Workflow:

Create a workflow on the Assets list item added event.

Action 1:

Configure parse CSV action to create records in “Invoice” list.