Vadim Tabakman

Nintex Workflow - List to CSV UDA

This weeks challenge I set myself, was to create a reusable way of converting a SharePoint list of items into a CSV format.  I want to make it reusable, because this is something I've talked about with a bunch of people and everyone will have different list structure.  Redesigning a workflow for each list makes no sense.

This post explains how I went about building a User Defined Action that I can use to convert any list in my farm to CSV format and then store that in a document library, email it, store it in a database or even sent it to a web service.

Video

User Defined Action Design

UDA Design

First off, you can see that the general design of this User Defined Action (UDA) was done with a State Machine.  The reason for this, is that it make it easier to come back later and make change to the workflow design, because the state have proper names that explain what the workflow logic in that branch does.

Also, notice that the logic in each branch has been wrapped into an Action Set.  Again, this is purely for asthetic value.  Each branch can now be minimized to make for easier modification of the other branches/states of logic.

User Defined Action Parameters

UDA Parameters

The UDA takes a List Name, Content Type name, Field Names (this is a comma separated list of field names) and a variable to store the output CSV format of that list.

Since the logic behind a UDA is hidden from the person using it in their workflow, the list of field names is the Display Names of the field (not internal names).

What Does Each State Do?

Get Content Type

Since this is based on getting all the items of a specific content type, in order to find out the internal names of the provided Fields, we'll need to get information on this chosen content type.

This state will query the list and find all the valid content types for this list.  It will then pull out the content type ID of the content type provided to the UDA from the Workflow that is calling it.

Get Internal Field Names

In this state, we ask SharePoint to get us all the information on the content type we're going to be working with. Then, all the internal fields names can be pulled out for the fields that are required for the CSV.

The internal field names are stored in a Collection variable to be used in the next state.

Build XSL Chunk

In this state, we build up some dynamic XSL that will be used later to transform some XML to CSV format. We iterate through the internal field name Collection and build up the XSL needed.  This is not the entire XSL.  Just the chunk that is specific to the fields.

Convert List Items to CSV

This is the final state of the state machine and it performs a query on the list, using a Call Web Service action.  The result is XML for all the items in the list that match the selected Content Type.  Finally, the Query XML action is used to transform that XML into CSV, using the XSL that we have built up.

Workflow

Testing out this UDA is quite simple.  Here's the workflow that has the UDA in it and it is followed by a Web Request action to PUT the CSV into a new file in a document library on the current site.

Workflow Design

Web Request

Conclusion

There are three main things to consider when using this UDA. 

  1. There are three Call Web Service actions where I'm using a credential constant that is valid for my environment.  In order for this to work in yours, you'll need to either have a constant with the name name, or update those actions to use a constant that is valid in your SharePoint environment.
  2. I have not included any error handling in the Call Web Service actions, so if you foresee there being a chance of this UDA failing, then you could look at enhancing this UDA to cater for that.
  3. If your fields contain commas, this will create invalid CSV data.  I didn't spend the time to figure out a workaround for this 

Here is my test list and notice there are 3 fields and I wanted all 3 fields in my CSV.  Also notice that there are 2 content types on that list, Item and Holiday.  In my test, I only wanted CSV format of the items of type "Item".

List Items

After running the workflow, the workflow history or the output CSV file will contain the following:

CSV Result

Downloads

Nintex Workflow 2013

Download the UDA (.nwf) - Download and import it into the UDA Designer Page

Download the UDA (.uda) - Download and import it into the UDA Designer Page

Download the Test Workflow - Download and import into the Workflow Designer Page

Gravatar
Copyright Vadim Tabakman
Rate this Content 0 Votes

Comments

Thursday, October 9, 2014 12:00:39 PM
Henry
Gravatar

re: Nintex Workflow - List to CSV UDA

Vadim,

I had a similar requirement recently that I was able to accomplish with Nintex.  We recently created around ten new  content types and needed to get the internal and display names for future needs.  I ended up bulding a list and added all the content types to the list; which created all the columns on the list.  I then used the Lists web service to get the list, Query for Internal name and Display name and created a SharePoint list with the output.  Ended up with list with of about 150 items (total for the columns).  Doing that manually would have been very annoying.

Sunday, October 12, 2014 11:01:20 PM
Arshad
Gravatar

re: Nintex Workflow - List to CSV UDA

Hi Vadim,

  Is this specific to Nintex 2013 or can we do it with 2010 too?

 

 

Thursday, November 6, 2014 6:24:24 AM

re: Nintex Workflow - List to CSV UDA

Hi Vadim,

I'm currently on a project and need to export list items to Excel, but I'm  on Nintex 2010.  Is this possible in 2010?  I've imported both the UDA and the workflow and updated the credentials in the Call Web Service actions and still getting a 401.Not Authorized error.

By the way..I enjoyed your Nintex Forms presentation in Dallas on October 20th, in Dallas!

Thursday, November 6, 2014 8:25:36 AM

re: Nintex Workflow - List to CSV UDA

Hi Rene,

The concept should definitely work in 2010, but since the UDa was built in 2013, I don't know if it'll import into 2010.

For the 401 error, I'd say it's most likely credential related and you should try using credentials of an account with higher privileges.

Cheers,

Vadim

Monday, November 10, 2014 1:28:56 AM

Error: Nintex Workflow - List to CSV UDA

Hi Vadim,

I want to use your Workflow to create a *.csv for our guests wlan registration.

There were only columns added to the list.

This is the error I get:

Detaillierter Workflowverlauf
Aktionsverlauf
Aktion
Start
Ende
Dauer
List to CSV UDA
10.11.2014 09:19
unvollständig
 
Aufgabenverlauf

Workflow-Nachrichten
Zeit
Ereignis
Nachricht
Ergebnis
10.11.2014 09:19
Workflowkommentar
Fehler bei Web Service aufrufen. Vom Server gelieferte Fehlermeldung: 401 UNAUTHORIZED
 
10.11.2014 09:19
Workflowkommentar
Fehler beim Ausführen der User Defined Action. Der Remoteserver hat einen Fehler zurückgegeben: (401) Nicht autorisiert.
Fehler


10.11.2014 09:19
Fehler
Fehler in List to CSV UDA Workflow.

I hope you can help me!

Thank`s Michael

Monday, November 10, 2014 2:12:54 AM

re: Nintex Workflow - List to CSV UDA

Hello,

I want to use your Workflow.

My Company use Sharepoint 2013 Enterprise & Nintes 2013.

I import your UDA and nwf File. 
I get the following error:

Detaillierter Workflowverlauf
Aktionsverlauf
Aktion
Start
Ende
Dauer
List to CSV UDA
10.11.2014 09:19
unvollständig
 
Aufgabenverlauf

Workflow-Nachrichten
Zeit
Ereignis
Nachricht
Ergebnis
10.11.2014 09:19
Workflowkommentar
Fehler bei Web Service aufrufen. Vom Server gelieferte Fehlermeldung: 401 UNAUTHORIZED
 
10.11.2014 09:19
Workflowkommentar
Fehler beim Ausführen der User Defined Action. Der Remoteserver hat einen Fehler zurückgegeben: (401) Nicht autorisiert.
Fehler
10.11.2014 09:19
Fehler
Fehler in List to CSV UDA Workflow.

Monday, November 10, 2014 8:06:28 AM

re: Nintex Workflow - List to CSV UDA

Hi Michael,

401 is all about permissions. You're using credentials for an account that doesn't have the permissions needed to do what you're trying to do.

cheers,

Vadim

Monday, June 29, 2015 11:12:13 AM
Aaron Hubbart
Gravatar

re: Nintex Workflow - List to CSV UDA

Would you be able to provide the details of each action so that this could be recreated in 2010 version?

Tuesday, June 30, 2015 8:48:40 AM
Aaron Hubbart
Gravatar

re: Nintex Workflow - List to CSV UDA

Vadim,

Can you give details for each action?  I don't have 2013, and would like to recreate using 2010.

Thanks!

Tuesday, June 30, 2015 9:33:17 AM

re: Nintex Workflow - List to CSV UDA

Hi Aaron,

the UDA and NWF not import into your 2010 environment?

cheers,

Vadim

Monday, July 6, 2015 9:27:56 PM
Sam
Gravatar

re: Nintex Workflow - List to CSV UDA

Hi Vadim,

I get the following errors;

Failed to invoke web service. Error returned from server: <soap:Fault xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>soap:Server</faultcode><faultstring>Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.
 
 and
 
Failed to run User Defined Action. The remote server returned an error: (500) Internal Server Error.

Could you please assist with this?

 

Cheers

Sam

Thursday, July 9, 2015 10:07:01 AM

re: Nintex Workflow - List to CSV UDA

Hi Sam,

This sounds like something that is passed to the web service, is invalid and causing the call at the server to fail.

You'll need to debug the data going to the action to see what it could be.

cheers,

Vadim

Tuesday, September 1, 2015 5:09:51 AM
Imran
Gravatar

re: Nintex Workflow - List to CSV UDA

Hi Vidam,

 

I need to design a workflow(Export to excel) for Nintex2010. Can you please provide its detail or UDA and workflow for nintex 2010.

 

Thanks in advance

Imran

 

Tuesday, September 1, 2015 8:03:05 AM

re: Nintex Workflow - List to CSV UDA

Hi Amrin,

the details of the UDA are described in the Video at the top of the post.

cheers,

Vadim

Sunday, November 8, 2015 9:03:05 PM
Josh
Gravatar

re: Nintex Workflow - List to CSV UDA

Any tips on how to get this to export the CSV including headers? I'm new to XSL and pretty new to Nintex so I'm trying to wrap my head around it.

Sunday, January 3, 2016 6:41:12 AM
Jarrad
Gravatar

re: Nintex Workflow - List to CSV UDA

Hi Vadim,

 

I have two questions, firstly, I have the UDA working however noticed that when converting Dates, it includes a time field, admittedly 0. is it possible to remove this ? and secondly, how would I go about filtering the CSV output based on a date range ?

Best regards,

Jarrad

Thursday, January 7, 2016 9:43:29 PM
Sam
Gravatar

Adding headers to the CSV export

@Josh

Hi Josh,

I added two lines to the xslt stylesheet code in the "Transform XML to CSV" action in the UDA to include the headers.

It will use the Field Names parameter which you input into the UDA and display it in the top row of the exported CSV. Hope that helps!

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="iso-8859-1"/>

<xsl:template match="/child::*">
<xsl:text>{WorkflowVariable:Field Names}</xsl:text>
<xsl:text>&#xa;</xsl:text>

<xsl:for-each select="child::*">
{WorkflowVariable:textXSLChunk}
<xsl:text>&#xa;</xsl:text>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Kind Regards,

Sam

Thursday, January 7, 2016 10:06:05 PM

re: Nintex Workflow - List to CSV UDA

Hi Jarrad,

If you want to completely remove the date value, then I believe you're looking at using some xsl:if commands to check the value and maybe compare it to zero (0).

As for filtering, are you trying to do it with XSL, or after you've opened the CSV in Excel?

 

Sam,

Thanks for sharing that.

cheers,

Vadim

Tuesday, March 29, 2016 11:28:04 PM
Sven
Gravatar

re: Nintex Workflow - List to CSV UDA

Hi Vadim,

this is a great post - as always.

Everything is working fine, even using SPF 2010. The only problem I'm facing is that I'm not able to get more than 1000 items exported. Is there a certain limit?

I've tried to use the standard SharePoint functionality "Export to Excel" and here I'm getting no more than 1000 items. I've tried different list, views, etc.

Could it be a issue that we are using SharePoint Foundation?

Many thanks

Sven

Wednesday, March 30, 2016 7:19:14 AM

re: Nintex Workflow - List to CSV UDA

Hi Sven,

I'd be looking in Central Admin, and Application Management.  There's something there about throttling.  Maybe there is a limit there that is set to 1000.

Just a guess.

Vadim

Thursday, March 31, 2016 7:37:24 AM
Sven
Gravatar

re: Nintex Workflow - List to CSV UDA

Hi Vadim,

thanks for your response. We didn't touched the default threshold of 5000 item. Unfortunately our list contains more than 8000 items. Perhaps that's the problem. Other lists which doesn't reached the limits are working fine.

Sven

Thursday, March 31, 2016 9:32:22 AM

re: Nintex Workflow - List to CSV UDA

This works great thank you sooooo much Vadim smiley

Friday, June 3, 2016 9:35:26 AM
Steve Smolik
Gravatar

re: Nintex Workflow - List to CSV UDA

After updating the Call Web Service credentials I am getting the error below. I took that URL and ran it in a browser and it returned the lists web services page. Any ideas?

 

The remote server returned an error: (500) Internal Server Error.

Monday, June 6, 2016 11:35:16 AM

re: Nintex Workflow - List to CSV UDA

Hi Steve,

I'm afraid I don't know what would cause this.

Did you try clicking on the Run Now button to see if that works?

Vadim

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





 

Statistics

  • Entries (246)
  • Comments (1725)

Categories