• vadim@workflowexcellence.com
  • Utah, United States

Nintex Workflow – Parse CSV and Create a List

It seems that getting information out of a CSV file and into SharePoint is a common request.  I’m sure there a number of ways to get this done, but this post will be about how to parse CSV files using Nintex Workflow.

We first start off with a simple SharePoint list that contains a First Name, Last Name and Age.

SharePoint List

This is the list that we want to populate with data.

The data currently exists in an CSV File.  In Excel, it looks like this:

Excel CSV

You could consider putting the Excel XSLX into Excel Services and then using the Query Excel Services action in Nintex Workflow.  This is an option.  But I think it’s over kill for this type of scenario.

The simpler solution would be have a document library where you upload your CSV file.  It doesn’t have to be anything more than an out of the box SharePoint Document Library.

This is where you design the Nintex Workflow.

Solution Pseudo-Code

  1. Read the CSV into a Text variable
  2. Split the data into lines
  3. Iterate through each line
  4. Split a line into individual fields (by comma)
  5. Retrieve each field value into a corresponding variable
  6. Use a Create Item action to create the item in the destination list

Reading a File into a Text Variable

This is done with one action.  A Web Request action.  You configure it with the url of the document and that you want to do a GET.  Store the result in a Text variable.  in a Document Library, the document has a common property named “Item URL”.  We use that, because that is the document we want to read.

Web Request

Splitting the data into Lines

CSV files have rows of data, each row separated by a Carriage Return and Line Feed.

We split the data into lines, using a Regular Expression action with the following:

Expression : \n\r

Regular Expression Lines

This is followed by a For Each action that iterates through each line.  Inside the For Each, not only do we store a line into a text variable, we also store the line index in a number variable.  This is so that we can check the index and ignore the first one.  The first line in the example CSV file is just titles of the columns and we don’t want to put that into the SharePoint list.

The test for that is done with a Run If action:

Run If

Split Line into Fields

Splitting a line of a CSV file similar to the above Regular Expression, except this time the expression is just a comma:

Expression: ,

Regular Expression Fields

Individual Fields and Creating the List Item

We are nearing the end of this workflow, as now all we have to do, is retrieve the fields into their own variables and then create the List Item in the List.

Use a Run Parallel Actions action, with 3 branches, one of First Name, Last Name and Age.  Inside each branch we use a Collection Operation to extract the appropriate field value from the Collection variable.

Run Parallel Actions

You’ll notice I’m using a Convert Value action  This is because the Age field in the my list is a Number, and so I want to convert my Text variable into a Number variable.

Finally, we have all the data we need, we can now create the item in the destination list:

Create Item

Downloads

Nintex Workflow 2010 : v2.3.6.0

Download the Files

Leave a Reply

Your email address will not be published. Required fields are marked *