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.
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:
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
- Read the CSV into a Text variable
- Split the data into lines
- Iterate through each line
- Split a line into individual fields (by comma)
- Retrieve each field value into a corresponding variable
- 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.
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
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:
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: ,
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.
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:
Downloads