In Nintex 2010, 2013 and 2016 for SharePoint (Standard version even) on-premise of course, there was a possibility to use excel services to query and work with the xlsx and xls files' data. However, insharepoint online there is no such powerful mechanism. Moreover Nintex products for SharePoint Online (neither nwc nor nintex for office 365) don't have any "ootb" actions that would fill that gap. So in the end, there is no straightforward way to achieve it.
The most common workaround is to convert the XLSX file into a plain, csv file and then to work with the data from the file using collections (I will write about it in second post).
Recently I have realized, that there is a set of excel actions in microsoft flow! All of us, who has SharePoint Online, has also a free version of Flow available.
Just be aware, that in the free version Flow does not triggers itself once an event occurs. It just repeatedly checks whether an action occurs and in case it's "check" does not happen in the moment, when an event occurs, it might never get triggered.
For the paid version there is no such risk as it works somehow like the Remote Event Receiver.
Anyway, I decided to give it a try.
First things first. I want to let you know what are the boundaries of this solution. Flow is not that flexible in this scope as I thought:
However, you should still be able to dynamically choose your list using "HTTP Request" action, to get list of its columns, and then another, to insert data.
After you accept all the above boundaries and go into rather "fixed" Flow workflow (still, you can create a Flow per each list/ Excel file, etc...), the working solution is built of the following components:
According to the documentation (source: OneDrive upload file) the workflow will wait up to 7 days for the user's decision.
If you don't plan to make an extra approval or other extra logic before the file gets queried and data gets inserted into list, you can simply just upload the file to OneDrive straight away, and then change the Flow to gets triggered once a new file is uploaded.
Note, that for a simple file, having three columns and 10 rows, it took almost a minute to complete the query and import. For larger files this action can really run for hours
It all depends on your specific requirements. In fact now you can trigger a workflow on the list where data got imported, so that each row will request an approval for example.
In a second post I will show you how to import data from the Excel file, when the file is saved as CSV.
Thanks for reading!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.