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.
User Defined Action 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
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.
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.
There are three main things to consider when using this UDA.
- 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.
- 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.
- 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".
After running the workflow, the workflow history or the output CSV file will contain the following: