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, in Sharepoint Online there is no such powerful mechanism (well, there are Excel Services available via REST API, but it doesn’t provide that much functionality). Moreover Nintex products for SharePoint Online (neither Nintex Workflow Cloud 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. So how can I import (and preferably automate it) data from XLSX file into SharePoint?

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. 

Anyway, I decided to give it a try.

Flow’s boundaries in Excel actions

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:

1. To anyhow work with the Excel files’ data, the data must be put in a table. So once you have a data set in the file, you must convert it into table and name it (Rename an Excel table – Office Support, Excel Tutorial: How to Name Excel Tables For Beginners Excel 2016 Tutorial Excel 2013 Tutorial – YouTube):
   1. Select your data set;
   2. From “Tools” choose “Format as a table”;
   3. Then go to “Design” tab;
   4. Set your table’s name in the left top corner.
2. If you decide to put a variable in the “File name” configuration field of the action, Flow will not allow you automatically to get table’s name and to use its columns later, as variables. The same thing happens if you decide to set “Table name” using a variable, not as a direct string:
   
3. If you choose, that data from the Excel will be uploaded to SharePoint List, there you again cannot use variables, to set list dynamically, because in that case you will not be able to bind list columns with Excel table columns:
   

Step-by-step solution

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:

1. SharePoint Import Library – this is where the Nintex workflow operates. In my case it takes the uploaded file, then uploads it into OneDrive for Business specific folder under a specific name (the one set as a source for the Flow). After it uploads the file, it then calls the Flow workflow using the “Web Request” action:
   
   1. “Authorizing user” – parameter visible in the “One Drive upload file” – it expects a valid email address to which an email with the authorization request will be sent. The email looks like this:
      
      Once user clicks “Provide OneDrive account credentials and authorize access” and then will let the app to access account info (in the next screen)
      the workflow will move on.

      According to the documentation (source: OneDrive upload file) the workflow will wait up to 7 days for the user’s decision.

   2. “Body” – the parameter in the “Web Request” action needs to be a valid JSON string (Flow is expecting a JSON request body). As there is a known bug, that prevents you from straightforward JSON declaration (the opening and ending brackets are somehow omitted), you need to do it the workaround way: Declare a variable, where opening and ending bracket are some specific tokens –> Regex, replace these tokens with { and } accordingly.
2. SharePoint List – the one, where the data from the Excel is going to be imported. I added 3 columns – text and date.
3. OneDrive for Business – there must be a specific folder, that will be used to save a file and then be queried by the Flow.
4. Microsoft Flow workflow – the one that takes the Excel file, pulls out the data, and then for each row does the insert into the list from point no. 2:
   

Working example

• I have created a simple Excel file:

• I have uploaded it into the “Import Library”

• That action triggered my Nintex workflow. I then received an email with the request to authorize access to my OD4B. Once I did that, I noticed the file gets uploaded:
  
• Then I opened status page of the Flow, and was observing how the rows are getting queried and then uploaded to SharePoint:

• And voilla! List is filled with data:

Next steps

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!