Nintex Workflow Cloud – Parse CSV
Parsing CSV files is not hard. But with Nintex Workflow Cloud, you also have to take into account where that CSV file will be. It could be as simple as something filling in a form, and passing the url into one of the fields. It could be a workflow you have that has an external start, so that another system can call it and pass in the url that way. Or it could be that you are using something like Box, OneDrive, DropBox, Google Drive or Citrix ShareFile. Someone is either uploading a CSV file there, or some other system is auto-generating it and putting it in there.
In this example, I’m using the later. I’m uploading a CSV file to either Box or OneDrive, and have a workflow that automatically starts and figures out a way to download the file, to get at the file contents and then parse it.
Box
First thing here, is that when we capture the event that a file was uploaded, we don’t automatically get the file contents. In order to do that, I used the Share File Link action to create a Download link. This is a direct link to the file. This is only available for paid version of Box.
The next important part is what differentiates doing this with Box and OneDrive. With Box, the Download url you get, if you put it in a browser, you download the file. But when doing a HTTP Get, you actually get a Redirect URL. Using the Call a web service action, we need to capture the Status Code firstly, because a 301 or 302 comes back, and that means you’re getting a redirect url. Then we need parse the Headers we get back, using a Regular Expression action. Once we have the Redirect Url, we go back through the loop and now try the new Url. With Box, I found there was a redirect Url, followed by another one. So the 3rd attempt, I was able to get the file contents.
One important thing you need if you build this out, is the Regular Expression to pull out the Redirect URL from the Headers:
(?<=\”location\”\=\>\[\”)[\w\d\.\:\/\-\_ ]+(?=\”\]\,)
It’s not pretty, but it works. Putting in here, because pausing the video and typing this in to your workflow would just be evil of me. Plus you can import the workflow using the Workflow key below.
OneDrive for Business
With OneDrive for Business, I believe it still needs to be a proper paid account. I don’t believe this will work with a free OneDrive personal account, but feel free to prove me wrong.
The thing with OneDrive, is that you don’t get the Redirect URLs. The Download URL is all you need.
I mention this, because if you build a workflow like I have for Box, it will work for OneDrive also. It just caters for Redirect URLs, if they come up. So if you work at a company that uses OneDrive, and later move to Box, then make sure you cater for this type of scenario.
Parsing the CSV
Parsing a CSV is not that hard. But it’s going to be slightly different for everyone.
Firstly, once you have the file contents, you want to split the data into lines. What works for me is a Regular Expression that splits on carriage return and line feed – \r\n. In some cases, I’ve had users tell me that doesn’t work for them, so a \n works.
The lines are stored in a Collection Variable and then iterate through each line using a For Each Loop.
Inside there, another Regular Expression action that will split the line by Commas and again store it in another Collection Variable.
Then you use a Get item from collection action to pull out the data you need.
It’s not hard, but will require some tinkering.
Downloads
Workflow Key – import it into the Workflows page in Nintex Workflow Cloud – 109b159a9c03485ea7400dee6d3b3908