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.

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:
   

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.

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"

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.

• 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:

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 

• 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!

Regards,

Tomasz