Vadim Tabakman

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

Posted by Vadim Tabakman Thursday, December 6, 2012 2:04:00 PM Categories: Collection Operation Collection Variable For Each Nintex Nintex Workflow Regular Expression Run Parallel Actions SharePoint Web Request
Copyright Vadim Tabakman
Rate this Content 2 Votes

Comments

Tuesday, December 11, 2012 4:32:46 AM
Bhuti Mbele

re: Nintex Workflow - Parse CSV and Create a List

This is great. I will try it.

Thank you man.

Thursday, April 11, 2013 4:06:33 PM
unknown

re: Nintex Workflow - Parse CSV and Create a List

Does the Execute SQL action only store a single column if stored in a collection?

 

Thanks you have some great information here!! I really appreciate the time you have taken to post!

Thursday, April 11, 2013 9:58:22 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi,

yes the Execute SQL action store the first column of your query into the collection variable.

But, if you have a newer build, you can specify which columsn you want and store each in their own Collection variables.  One action to rule them all!!

cheers,

Vadim

Monday, September 30, 2013 8:11:07 AM
Andi

re: Nintex Workflow - Parse CSV and Create a List

Hi, that example is great. I have a question:

How can I read a csv-file from a network drive (no http)?

For example: a file at the server \\servername\ordnername is updated every month and has to be readed by the nintex workflow.

Is there a common action, which can handle such a task?

Monday, September 30, 2013 8:13:00 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Andy,

there's no action out of the box that I'm aware of, to read from a network share. You may need to look at putting it into a library or building a custom action.

cheers,

Vadim

Tuesday, October 1, 2013 12:03:54 AM
Andy

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

Thank you for your response! Putting the file into a library is the way we work at the moment. Ok, looks like that would not change.

Great blog and great work !

Cheers,

Andi

Tuesday, October 1, 2013 4:02:02 AM
Dirk Beck

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

is there a way to import a csv using Nintex 2007?

Cheers, Dirk

Tuesday, October 1, 2013 11:27:10 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Dirk,

Not that I'm aware of.  There's a Copy to File Share action, but not a Copy from File Share.  There was some talk a while back about WebDAV, but I don't know much about that.

cheers,

Vadim

Tuesday, October 1, 2013 11:27:28 AM

re: Nintex Workflow - Parse CSV and Create a List

Andi,

thanks for the kind comments.

cheers,

Vadim

Thursday, November 21, 2013 12:04:14 PM
Sammy

re: Nintex Workflow - Parse CSV and Create a List

Vadim,

I am getting an error when I use the Web Request action to read the file.  I am getting "Error performing web request. The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel" as an error.  My SP Admin says all the certs are correct. The user is a Farm Admin.  Any suggestions?

Thursday, February 20, 2014 8:19:26 AM
Basmall

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

I downloaded your sample Workflow and the list templates in the hope of reusing it with modifications. The sample workflow stops on "Runif" Action even though the workflow status message says that it is complete. I haven't done any modifications to your sample except to the user name and password where it calls the Web Request action. I am quite new to Nintex and I am learning as I work with these samples.

Workflow history shows it is completing the steps up to "RunIf" and the rest of the steps are all grayed out.

 

Any pointers or tips would be helpful

Thank you

 

Basmall

 

 

 

Saturday, February 22, 2014 9:16:55 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Basmall,

I would recommend you use the Log in the History List action and log the values of some of the variables.  Such as the ones being used in the Run If action.

That should give you an idea of why the workflow isn't going into the Run If.

cheers,

Vadim

Tuesday, March 11, 2014 5:17:41 AM
Surya

re: Nintex Workflow - Parse CSV and Create a List

Hey Vadim, 

 

First of all thanks for the post. Saved me a lot of effort. 

 

I have one issue though. When I split the CSV using \n\r - I get all my rows in one line. Basically it doesn't split. Then I tried this instead: I cleaned the pattern textbox in the split action and just hit ENTER once in the pattern textbox i.e. to split based on ENTER. Now it splits fine but adds a blank line at the end of the collection. 

Is there something I am missing?

Thanks 
Surya

Monday, March 31, 2014 8:38:38 AM
Kunal Patel
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

Your worflow is not working for me. I have 3 columns (first & second  are an alphanumeric while 3rd one has the domain name)

1. If I keep \r then it won't run actions inside "Run-If".

2. If I remove \r then it stores the first column value in all 3 variables (under Run Parallel action)

I am not sure what I'm doing wrong !!!!

Please advise.

Thanks,

KP

Monday, March 31, 2014 9:43:16 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Kunal,

can you email me an example of your CSV and I'll see why the workflow isn't working?

cheers,

Vadim

Monday, March 31, 2014 2:40:31 PM
Bryan Malone
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Really nice article, unfortunately we are still on 2007, so I can't import your .nwf file. I am trying to duplicate, but not clear on the for each iterate through each line syntax (and whether it's reading/writing collection or text). LIkely the run if is different in 2007 as well. Any insights would be helpful.

Thanks,
Bryan

Friday, April 4, 2014 10:47:56 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Bryan,

the For Each is configured to go through one of the collections and store the value in the appropriate variable and also the index into a number variable.

IT's then using the Collection Operation action to do a "Get" from another collection and using the index we have.  So we are getting the related value from each collection.

Tae a look at this : http://www.vadimtabakman.com/nintex-workflow-iterate-through-related-collections.aspx

cheers,

Vadim

Wednesday, April 9, 2014 6:36:54 AM
Bcobra
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

For those with issues with line breaks and the reg expression \n\r stopping the Run-If statement:

Swap the order to \r\n and it will work.

Thursday, April 10, 2014 5:44:36 AM
bcobra
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

What regular expression would you use to remove commas in quoted strings within the CSV before splitting on the commas?

EX:

09210IT004,12/11/2009,OBLG,,"IT 004-BENSON,F-ATLANTA-1/3-9",584,7509,2013,75193710310001,,4939000D584,,,2143,584CDC01,,,42.00,0.000,0.00,0.00

thanks

Thursday, April 10, 2014 7:56:33 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi bcobra,

I'm not sure what Regex would do that.  You might need to come up with something a little more elaborate.

cheers,

Vadim

Friday, April 11, 2014 2:50:35 AM
bcobra
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

I came up with  a solution but it is very slow. At the "Run if" Activity I first use a regular expression to find quoted text and extract it. Then I run a "For Each" to iterate through the found quoted text and run another regular expression to replace the comma with a " " I then run another regular expression to replace the quoted string with the comma with the fixed quoted string without the comma. Then the workflow goes back to the original layout you designed. Any thoughts on this process.

Friday, April 11, 2014 11:11:18 PM

re: Nintex Workflow - Parse CSV and Create a List

HI bcobra,

I'm sorry, I wouldn't know of another way to do that.  But then, I'm not a regex expert.  I'm not sure what that would be slow though.

cheers,

Vadim

Wednesday, April 23, 2014 7:44:50 AM
BBasmall
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

I am able to run your workflow without issues once i did the switch from \n\r to r\n as suggested in bcobra's post. The workflow runs and populates the list as expected and then it errors out at the end. I have an idea why it is doing it, but have no idea how to fix the issue.

What I believe the issue is, that the workflow runs and after adding all the items from the string; errors out as it cannot find any more items to process at  "For Each" loop. Is there a way to stop the loop after all the items are processed?

I have created an other work flow based on your example and it exhibits the same behavior after cycling through the loop it errors out at "For Each" loop once it encounters a blank value at the end of the string

 

I have pasted the error message at the bottom

Event Message Outcome
 Workflow Comment: Error operating on collection variable. Index property is greater than the number of items in the collection.

 

 

Thank you

 

Basmall

Wednesday, April 23, 2014 8:21:36 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Basmall,

this is happening because your collection variables are not all the same size.

It looks like your first collection may have 'n' number of items in it, and one of your other collections has less than that, and that is what is causing the Collection Operation (GET) to fail with this error.

This can happen if you have some number of rows, but one of the rows has less columns in it.

What I would recommend, is before the For Each, you run a Collection Operation action on each collection and configure it to do a "Count" and log that to the workflow history,  You'll find that the one or more of your collections are different sizes.

cheers,

Vadim

Thursday, April 24, 2014 1:58:14 PM
Basmall
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

Thank you very much for the clarification.You are bang on. I checked the collections and out of the 4  collections I have 3 of them are giving me a count of 4  and the remaining one shows a count of 1. Could this be an issue with the CSV file? or the way the rows get split?Though I am getting this error the workflow is copying the data that I need. If it is an issue with the data file is their a a way in Nintex to stop the loop when it encounters the error and bypass the workflow to completion. We may get source files that could some times be corrupted.

Thank you

Basmall

 

 

 

 

Thursday, May 1, 2014 6:21:28 AM
bcobra
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Basmall,

  I ran into the same issue. If you do a collection count on the number of lines created you will get a count of one extra line. This extra line has a count of one column.  To fix this open the csv in notepad, go to the end of the file and make sure the cursor ends at the last entry i.e. no extra carriage return. When using excel for CSV I find it adds and extra carriage return.  

Thursday, May 8, 2014 10:08:33 AM
SL
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi,

Thank you for the great article!  I have imported the workflow and when I run it, it is failing with the error "Coercion Failed: Unable to transform the input lookup data into the requested type."

Looking at the history log, the "for each" is running for the first line, because the index is 0 the "run if" doesn't run (as expected).  The for each then processes the second line in the CSV file but this then fails at the "Run If" step with the above error message.  I am not sure what is causing this, all of the row columns have data and the index is outlined as 1 so it should run.

Any help would be greatly appreciated.

Thanks in advance

Thursday, May 8, 2014 11:04:11 AM

re: Nintex Workflow - Parse CSV and Create a List

Hey SL, can you send me your CSV file? cheers, Vadim
Tuesday, May 13, 2014 4:51:08 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim, love the post and is making a lot of sense for those of us not having the full portal functionality.

I am having the same issue as posted under SL (last post)

Can you tell me how to deal with that ?

When I save the excel file as .csv and re-open in Notepad it looks like this (so a carriage return and a linefeed is present indeed):

First Name;Last Name;Age
Homer;Simpson;33
Marge;Simpson;31
Bart;Simpson;10
Lisa;Simpson;9
Maggie;Simpson;1

Your help is very much appreciated .

 

Tuesday, May 13, 2014 5:59:06 AM

re: Nintex Workflow - Parse CSV and Create a List

Issue solved with saving the Excel file as proper csv textfile as written by bcobra before, thanks.

Tuesday, May 20, 2014 9:24:38 AM
Basmall
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Same here worked liked a charm

Thank you bcobra

Basmall

Thursday, June 5, 2014 12:18:57 AM
Kiran Ghatge
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hello,

i am bit new in share point & i m working on reporting system project. my plan is use my BAAN daily report to update in my existing  sharepoint list. the BAAN  report could be in .CSV\.XLS\.TXT . Can any once can help me how to sync data on daily basis?

Friday, June 13, 2014 1:02:44 PM
Matthew Skinner
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Brilliant Solution! I do have a question regarding a work-around if you CSV that contain currency numbers that use a commas? Any thoughts on another way to parse the file that has additional commas or to skip over the comma of the currency? Example: John, Doe, $42,000, NY, 31

Thanks again!

Friday, June 13, 2014 1:36:39 PM
Matthew Skinner
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Regarding my previous question - it appears that any currency with commas also have quotes. So I am working on building a Regular Expression to ignore commas inside of quotes Example "$12,000" - Any thoughts on this approach?

Friday, June 13, 2014 2:24:15 PM
Matthew Skinner
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Sorry for the multiple posts - but I found the solution to rows that contain commas used for currency. Example Bob, Jones, "$42,000", 22 - this messes up the Split Fields Pattern by comma.

So the solution is to change the Split line info Fields by Comma Pattern from ,  to  ,(?=(?:[^"]*"[^"]*")*[^"]*$)

You can delete my last two posts if you like.

Cheers!

Sunday, June 15, 2014 8:33:25 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Matthew,

That is an awesome solution.  Thanks for sharing that. 

Cheers,

Vadim

Tuesday, June 17, 2014 9:16:33 AM
Kris H
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Vadim,

Great post. My CSV file is formatted differently. Could I possibly send you the CSV file I am trying to parse?

Tuesday, August 19, 2014 2:45:34 AM

Doesn't go to next row anymore

Hi Vadim,

I used your examples before and it worked great. But now I am pulling my hair off ! I was trying on 2013 and it stopped at Row1, 

then I decide to use EXACTLY your files on a 2010 environment to make sure and ... it has the same behaviour, just reads the first row and stops.

Did the Regex action in Nintex latest updates got changed recently somehow ? 

thanks.

Tuesday, August 19, 2014 3:41:19 AM

/n/r vs. /r/n

Interesting : I got it working using Expression : \r\n  but when using /n/r  the Collection var (0) takes the whole text into 1 row only. weird !

as described in here http://stackoverflow.com/questions/20056306/match-linebreaks-n-or-r-n 

 

 

 

Tuesday, August 19, 2014 6:43:27 AM

re: Nintex Workflow - Parse CSV and Create a List

Glad you got it working Francois. I'm not sure what has changed to result in this behavior.

cheers,

Vadim

Tuesday, September 2, 2014 6:00:16 AM
Moses Ayo-John
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

I am a new user of a Nintex workflow ,I want to use Nintex workflow to implement this task:
The list of approvers are stored in a CSV File, when the request gets to any approver ,the approver can accept ,reject or request for additional information. The request for approval shall be dynamically generated serially using Nintex workflow. These are the requirements:
When any approver approves the request it goes to the next approver on the list.
When any approver requires additional information the workflow shall notify the initiator of the request for additional information. The requester will update the request with required information and send it back to requester that requested for the additional information without starting from the beginning.
Whenever any approver rejects the process goes to end state.
Please can anybody assist in these requirements?

Tuesday, September 2, 2014 7:49:35 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Moses,

I would recommend you break this up into components and build out each one to see how it works.

1. Reading the CSV file - this should be easy enough if you follow this post.

2. Iterate through each approver, use a For Each action.

3. When other information is needed - when assigning a task use the Flexi Task action and add another outcome like "More Data".

4. If an approver selects "More Data", assign a task to the initiator for more data"

When I build workflows, I try to build the individual pieces first and get them working and once I have that working, I can start putting them together.

If you are looking for help with building the workflow, you may consider going to http://community.nintex.com .  That is the Nintex Community site and a lot of people are there maybe you could get some collaboration going there.

Cheers,

Vadim

Friday, October 10, 2014 1:03:50 PM
Cody
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

This may be a dumb question.... I have a .csv file that has a dollar amount with a comma (ex. "$1,500.00") and the workflow ends up separating the dollar amount into two columns. To complicate this, it only has the quotations around the dollar amounts that are over $1000,  and the rest of the columns do not have quotations. Do you have any suggestions on how I can get around this issue?

Thursday, May 28, 2015 4:05:15 PM
Toni
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi - this workflow looks awesome! 

But when I run it I get a 'unauthorized' error on the web request. What account should I be using?

Monday, August 31, 2015 3:58:31 AM
Happy
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

 

I tried the same process provided by you. The only issue that i am facing is when item is created in a list the whole data is shown in a single row. But in CSV file we have 10 rows. my requirement is when item is created in a list data needs to be stored in 10 rows instead of single row. 

 

Please suggest

Monday, September 14, 2015 11:53:10 AM
Corey
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Vadim, great solution. However it is erroring out where there are blank cells. Is there a way around that?

Monday, September 14, 2015 1:09:52 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Corey,

so simple way, since what we're doing is get the data for each column and we assume there will be the same amount of data in each resulting collection variable.

If there isn't, that's where we have an issue and there's no nice way of handling that.

cheers,

Vadim

Tuesday, September 15, 2015 11:17:33 AM
Corey
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Ah, well that is ok. Since it only occurs on the last cell (handles blanks in the previous lines cells OK), ill use VB to scrub the raw file and add a value, then delete that value with another workflow.

 

Also, the recommendation to remove last carriage return and reverse the \n\r worked a treat for me!


I definitely appreciate the assistance.

Thursday, September 24, 2015 6:05:10 AM
Srik
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

 

I would like to store people and group field value, while storing this value some time I need to store empty value. But not able to store the empty value.

Please help to store empty value to people and group field.

Thursday, September 24, 2015 12:21:34 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Srik,

I've never had to update a people field to make it empty.

If the Set Field Value or Update Item don't work, you could look at using the Call Web Service action to call Lists.asmx and specifically, the UpdateListItems web method.

cheers,

vadim

Wednesday, September 30, 2015 10:59:32 AM
Corey
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Vadim


When I add a new column, it is replicating the first name and shifting the one of the other columns. Any suggestions? Am I missing something when I add a new column?


Thanks!

Wednesday, September 30, 2015 11:17:07 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Corey,

I'm afraid I don't know what you mean.

Vadim

Wednesday, September 30, 2015 11:21:48 AM
Core
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

I guess my disconnect is how do the columns know what lines up with first name, last name, etc?

Wednesday, September 30, 2015 11:46:45 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Corey,

it's up to you to know the structure of your CSV file and what field should go into what column.

CSV files are structured, so each row will have the same number of values, separated by commas.  Your workflow needs to be built to understand which value corresponds to which column in your list.

There's no way to make the workflow smart enough to just figure it out if you add a new column or other data into your CSV.

Vadim

Wednesday, September 30, 2015 11:50:23 AM
Corey
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Here is an example of how it is failing :) I added column "Awesome" but it is pulling the first name :). Everything seems ok up to the collection operation:

 

9/30/2015 1:46 PM
Comment
 
textfilecontents: First Name,Last Name,Age,Awesome
Homer,Simpson,33,This is cool
Marge,Simpson,31,This is cool
Bart,Simpson,1111,This is cool
Lisa,Simpson,9,This is cool
Maggie,Simpson,1,This is cool
 

9/30/2015 1:46 PM
Comment
 
collcsvlines: First Name,Last Name,Age,Awesome;Homer,Simpson,33,This is cool;Marge,Simpson,31,This is cool;Bart,Simpson,1111,This is cool;Lisa,Simpson,9,This is cool;Maggie,Simpson,1,This is cool;
 

9/30/2015 1:46 PM
Comment
 
text first name: Homer
 

9/30/2015 1:46 PM
Comment
 
text last name: Simpson
 

9/30/2015 1:46 PM
Comment
 
text age: 33
 

9/30/2015 1:46 PM
Comment
 
text awesome: Homer
 

9/30/2015 1:46 PM
Comment
 
num age: 33
 

9/30/2015 1:46 PM
Comment
 
collLineValues - Homer;Simpson;33;This is cool;

Separate Collection Variables - Homer, Simpson, 33,  Homer

Wednesday, September 30, 2015 12:25:18 PM

re: Nintex Workflow - Parse CSV and Create a List

In the Run Parallel Actions, make sure you have a 4th branch with a Collection Operation action to do a Get.

Make sure it's getting an item from the collections in the 3rd index (that is the 4 item).

The Collection Operation action is probably not configured correctly.

cheers,

Vadim

Friday, October 2, 2015 11:57:41 AM
Corey
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Gah, you were right. The default value on the index was not correct. I left it on 0 instead of 4 (4th column)....brilliant workflow and thank you so much for responding.

Friday, October 2, 2015 1:19:52 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Corey,

awesome!! Glad it was an easy fix.

cheers,

Vadim

Tuesday, October 20, 2015 8:07:41 AM
Patrik
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Dear Vadim, thank you very much. It helps me very lot! :) Also thank to "bcobra". I had the problem with last row in .CSV 

Tuesday, October 20, 2015 8:58:12 AM

re: Nintex Workflow - Parse CSV and Create a List

awesome Patrik.

glad this helped.

Thursday, October 29, 2015 3:44:38 AM
Kunal Gupta
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

I have a similar Nintex workflow to read from excel sheet and save data in a list.

The workflow works fine in INT and QA environment, but on production it is giving following error -

"Error querying Excel Services. You do not have permissions to open this file."

the file opens fine in the browser as well as client excel application.

Appreciate your help on this... :)

Thursday, October 29, 2015 5:47:54 AM

re: Nintex Workflow - Parse CSV and Create a List

I think you need to check out the permissions on the library where the file is, and if's been a while, but I think there's some permissions in configuring Excel Services.

Thursday, October 29, 2015 6:34:08 AM
Kunal Gupta
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim Tabakman....I was cracking my head on the wall waiting for your reply and as for this issue it turns out I was missing such a trivial thing. The account I used in excel services didnt have permissions on the library.

For a fact, this is the most foolish/novice I have behaved in quite a long time. how could i miss such a basic thing.

Thanks Vadim Tabakman :) have a great day man!! cheers!

Thursday, October 29, 2015 7:34:31 AM

re: Nintex Workflow - Parse CSV and Create a List

haha don't you hate that? I'm glad it was something easy and quick.

vadim

Sunday, November 1, 2015 4:55:30 AM
Yoram Katz
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

This is a great guiding article and served me very well.

But... when I try to extract a CSV text column in a foreign language (in my case Hebrew) - I get gibberish.

Do you happen to know how to work around this? Is there a special setting required, in Nintex or in Excel?

 

Regards,

Yoram

 

Sunday, November 1, 2015 10:44:15 PM
Yoram Katz
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi,

It is an EXCEL issue, not Nintex.

I got around this by saving the EXCEL file to a tab separated .txt file in UTF8.

This had another significant benefit . Tab separation has a significant advantage over CSV - you do not need to bother with special handling of inherent commas inside text or numbers columns, which is an issue with a CSV file.

Tegards,

Yoram

Sunday, November 1, 2015 10:44:23 PM
Yoram Katz
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi,

It is an EXCEL issue, not Nintex.

I got around this by saving the EXCEL file to a tab separated .txt file in UTF8.

This had another significant benefit . Tab separation has a significant advantage over CSV - you do not need to bother with special handling of inherent commas inside text or numbers columns, which is an issue with a CSV file.

Tegards,

Yoram

Monday, November 30, 2015 9:38:22 AM
Eric Santovenia
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hello Vadim,

I have been working on a workflow for a couple days now and have had zero luck. My problem seems to be with the regular expression. Regardless of which expression I use ( \r\n or \n\r), it will not split into seperate lines. I am using Nintex 2013 and I am using the file you provided for a CSV. Do you have any ideas of what could be happening here?

Thanks,

Eric Santovenia

Monday, November 30, 2015 9:47:13 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Eric,

When you are using the Regular Expression, the results should go into a collection.

Have you tried using a Collection operation to do a Count on the collection and see what the number is?

Is it zero or more?

Vadim

Monday, November 30, 2015 11:11:46 AM
Eric Santovenia
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hey Vadim,

I added the Collection Count as suggested, I also added Verbose logging and I was able to pin point the issue.

Thank you for your help!

Monday, November 30, 2015 11:17:32 AM

re: Nintex Workflow - Parse CSV and Create a List

Awesome.  Glad you got it working.

Vadim

Thursday, December 3, 2015 2:43:25 PM
Eric Santovenia
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hey Vadim,

I was able to get the example workflow running perfectly, Now I went ahead and took that same concept and applied it to a much larger CSV and I continue to get an error on the "For Each".

I have enabled the verbose logging and I am able to see how many iterations of the "For Each" have been performed. The main problem is the randomness of the issue. Some times the "For Each" will run 70 times, sometimes it will run 80+ times. I am kind of stuck here and I can only really think of two things, Safe Looping and Exceeding the memory. The file is a CSV, so it does have a lot of variables, but is relatively small in size (>800 kb).

Thanks,

Friday, December 4, 2015 9:12:10 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Eric,

the only thing I can think of that would cause this issue, is that it's queried the CSV and you have multiple collection variables.

If that is the case, use the Collection Operation action to do a "count" on each and log the count value.  Do this before the For Each.

It's possible that you have multiple collections, but the are of different sizes.  This would result in a For Each going through Collection1 and then a collection operation action failing to get a corresponding value from another collection, because you've gone over the limit of the other collection.

vadim 

Wednesday, February 24, 2016 8:32:04 AM
Vignesh Natarajan
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

 

I have a csv of which can have maximum 10000 rows of data. I want to split it into 500, 500, 500 and so on rows and store them in  a collection variable to send emails to 500 users at a time. Since we are not able to send mail to 10000 users at a time, we need to split them into 500. Can we do that? If yes, which action item will help us? Also can a collection variable hold 500 data?

 

Thanks,

Vignesh Natarajan

Wednesday, February 24, 2016 8:32:13 AM
Vignesh Natarajan
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

 

I have a csv of which can have maximum 10000 rows of data. I want to split it into 500, 500, 500 and so on rows and store them in  a collection variable to send emails to 500 users at a time. Since we are not able to send mail to 10000 users at a time, we need to split them into 500. Can we do that? If yes, which action item will help us? Also can a collection variable hold 500 data?

 

Thanks,

Vignesh Natarajan

Thursday, February 25, 2016 11:44:22 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vignesh,

yeah you can have 500 items in a collection.  In fact, you can have as many items as you want.

I would potentially read all the items into a single collection, then use a For Each to go through it.  Also, have a temp collection varaible that you populate with 500 items. Then inside, use a Math Operation action and use the MOD operator.  MOD 500.  Then, have a Run If, to see if the result is zero.  If it is, send the email, then clear the temp collection.

Vadim

Sunday, February 28, 2016 1:57:25 AM
Guillaume DIMECH
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hello all,

First, thanks a lot for this post. But I am not a specialiste user of nintex Workflow.

I have some difficulties to put in place this in my platform.

Source data: I have 3 CSV file where I would like to import in my platform. Files are every week (or every day if requested) updated. those 3 file when I combined them gave me my complete worklfow. This is to manage my daily job.

My conserns are simply. I can not achieved the first step as writen in this post. Do you have an update or on better detail to help me? Like for a "newbie".

Many thanks in advance

 

Sunday, February 28, 2016 11:43:30 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Guallame,

what part of the workflow are you having an issue with, and maybe I can help you.

vadim

Monday, February 29, 2016 12:53:02 AM
Guillaume
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim, 

Ok to make simply:

I have 3 phase in my Process (3 CSV file): First Impact analyse, Incorporation of the Impact Analisys and the last Quality and payment for this incorporation of triggers in our Technical documentation.

This is 3 differente CSV file. When you add this 3 file in one sahrecopter list, you have the complete process "or workflow".

I want to built a common list in sharepoint with this 3 CSV files because I need to know where this trigger or this one is.

How many are done, payed etc...

I just need to bring this and for me I hope this is the good post.

Guillaume

 

Monday, March 7, 2016 9:27:39 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Guillaume,

Since it's 3 separate CSV files and they are going into one document library, you'll have to try to figure out how to match all 3.  Do they have a similar name?

One you have them, you should be able to use the Web Request action to read each CSV into it's own Text variable.

Now, you can parse the data from each, as this post describes.

Vadim

Wednesday, March 23, 2016 7:15:32 PM
Lisa
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Guillaume,

I downloaded your workflow and am testing it, I've logged some debug variables in the history, I'm also using your sample CSV. I believe the problem is that each line is not being split out correctly - see below in history the results. The comment I am logging is:

LINE INDEX
{WorkflowVariable:numLineIndex}

LINE DATA
{WorkflowVariable:textCSVLine}

 

But the results (below) suggest that all 3 lines are returned in line 0. This makes sense because line 0 is skipped and the workflow does not error but no items are added to the sharepoint list.

 

The regular expression pattern is \n\r and the operation is split. I am using Nintex 2013.

What am I doing wrong?

Thanks,

Lisa

 

 

24/03/2016 12:13 PM Comment  
LINE INDEX
0

LINE DATA
First Name,Last Name,Age
Homer,Simpson,33
Marge,Simpson ,31
Bart,Simpson,10
Lisa,Simpson,9
Maggie,Simpson,1
Wednesday, March 23, 2016 7:46:51 PM
Lisa
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

correction on my question above:

all 3 lines are returned in line 0. should say

all 6 lines (inc header and 5 names) are returned in line 0.

Wednesday, March 23, 2016 7:48:31 PM
Lisa
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

I downloaded your workflow and am testing it, I've logged some debug variables in the history, I'm also using your sample CSV. I believe the problem is that each line is not being split out correctly - see below in history the results. The comment I am logging is:

LINE INDEX
{WorkflowVariable:numLineIndex}

LINE DATA
{WorkflowVariable:textCSVLine}

 

But the results (below) suggest that all 6 lines (inc header and 5 names) are returned in line 0. This makes sense because line 0 is skipped and the workflow does not error but no items are added to the sharepoint list.

 

The regular expression pattern is \n\r and the operation is split. I am using Nintex 2013.

What am I doing wrong?

Thanks,

Lisa

 

 

24/03/2016 12:13 PM Comment  
LINE INDEX
0

LINE DATA
First Name,Last Name,Age
Homer,Simpson,33
Marge,Simpson ,31
Bart,Simpson,10
Lisa,Simpson,9
Maggie,Simpson,1
Thursday, May 26, 2016 2:58:44 PM
Charlie Price
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

Hi Vlad,

With Nintex Workflow 2013 is it possible to read the contents of an Excel file and copy to a list?

Monday, July 11, 2016 6:24:33 AM
Pankaj Garg
Gravatar

re: Nintex Workflow - Parse CSV and Create a List

I was able to parse the csv file. Thank you! We have a recurring csv (nightly upload to Sharepoint Document Library) file that bring data from another application into Sharepoint. I run workflow to create any new entries and update existing ones.

 

However, I am not able to delete any entry from Sharepoint list if it doesn't exist in CSV file. Is there way to delete the entry?

Monday, July 11, 2016 6:56:41 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Pankaj,

It would require a bunch of logic in your workflow.

You'll need to keep a collection of data from your CSV, and after you've worked on that, you'll need to go through each it in the list and use the Collection Operation action to do an "exists" operation.  Then you can use a Run if action, and if it doesn't exists, delete it from the list.

 

Vadim

Monday, January 23, 2017 11:09:48 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vladim,

I've used your workflow to parse a tab-separated TXT file successfully, and it works great! However, I have another tab-separated TXT that is much larger (74 KB; about 1400 lines). With this larger file, I encounter strange behavior in the workflow. When I kick off the parsing WF, it starts to create list items, then stops between 800 to 1200 entries, at a different point each time. I'm emailed the error "Workflow failed to start," but it has obviously started, since many items were created based on the TXT file. The entries that are created are correct. The two workflows are identical except for the file size that they operate on. Is this some kind of timeout? When I run the WF, it takes a few minutes to run fully, then it errors.

Edit: I switched the regular expression to \r\n since \n\r did not work for me in either case.

SharePoint 2013 on-prem Standard; Nintex 2013 Standard

Thursday, February 23, 2017 12:01:44 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

When I run your example it works fine.

I've then adapted it to suit my .csv so essentially adding more index variables, variables and collection actions.

When I run it against mine it puts the first piece of data into alll the columns of the row.

It seems that the indexes do not increase thus moving it a long.

Do you have any ideas what I'm doing wrong?

I've tried everything and would say I'm pulling my hair out but it's long gone.

Thank you

Regards

Julian

 

Thursday, February 23, 2017 12:13:59 PM

re: Nintex Workflow - Parse CSV and Create a List

Are you potentially using a different delimiter/separator?

Can you shoot the CSV file to me?  I can take a look when i have a minute.

Vadim

Thursday, February 23, 2017 12:48:44 PM

re: Nintex Workflow - Parse CSV and Create a List

Nope, same delimiter as you the comma e.g. ,

How do I attach the csv or should I email it to youi?

Thursday, February 23, 2017 1:24:38 PM

re: Nintex Workflow - Parse CSV and Create a List

Email it me. :)  I just shot you an email.

Vadim

Tuesday, February 28, 2017 5:06:57 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

I'm still suffering form using this method.

Are you or have you any plans to develop:

 

putting the Excel XSLX into Excel Services and then using the Query Excel Services action in Nintex Workflow

 

Regards

Julian

 

Tuesday, February 28, 2017 5:07:01 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

I'm still suffering form using this method.

Are you or have you any plans to develop:

 

putting the Excel XSLX into Excel Services and then using the Query Excel Services action in Nintex Workflow

 

Regards

Julian

 

Tuesday, February 28, 2017 10:16:35 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Julian,

Still having issues parsing the CSV? I'm not sure why it's not working for you.  I tried your CSV and with a tweak to the regex, from \n\r to just \n, I was getting the CSV to split into lines.  Then the For Each that went through each line, was able to do another regex to split by comma.  I didn't make any other changed to the workflow.

I don't know of any plans to add this functionality directly into Nintex Workflow.

I'd suggest you put it on http://nintex.uservoice.com

Vadim

Tuesday, February 28, 2017 10:17:13 PM

re: Nintex Workflow - Parse CSV and Create a List

There is a Query Excel Services action in NW on-prem.  But I don't know about adding the Excel/CSV to where Excel Services are looking.

Vadim

Thursday, March 2, 2017 1:34:53 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim, 

 

      I followed your steps. But I'm having issues connecting with the CSV file I uploaded in the document library. 

  •     I set the URL field in the web request configuration to item URL. Yet when I test the connection I get:

         " Invalid URL format"

  •     I left the user name and password fields blank. 

Am I missing something in the steps?

 

Cheers

Frank

Thursday, March 2, 2017 3:48:45 PM

re: Nintex Workflow - Parse CSV and Create a List

All sorted.

I must have been having a Senior moment as I hadn't setup the index variables defaults to the individual columns.

e.g. sapnumindex = 0, sapnamindex = 1, projectnumindex = 2 etc.etc.

instead they were defaulting to 0...... Doh!! laugh from provoke

Anyway, I'm so very, happy that it's solved and so simple.

Many thanks to Chris Ben from Provoke who instantly saw the fault.

Thursday, March 2, 2017 9:01:37 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Frank,

 

what does the URL look like?

Vadim

Tuesday, June 6, 2017 3:02:52 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,

I would like to test this out on SharePoint online; however, I can't get your example to load in my version of nintex online. Can you give me screenshots of the collection operation settings? I am going out of my element and having a hard time following the directions. Any additional help would be appreciated.

Thanks

Amanda

Wednesday, August 22, 2018 11:49:44 AM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,Will the web request work in site workflow?

Wednesday, August 22, 2018 1:58:13 PM

re: Nintex Workflow - Parse CSV and Create a List

Hi Vadim,Can we map the columns in CSV file to the columns in destination sharepoint list?Please suggest.Thanks

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register

Statistics

  • Entries (279)
  • Comments (1769)

Categories