Top

How to: Import data from XLSX file into SharePoint Online using Nintex


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. 

Important! 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 SupportExcel 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:
    Microsoft Flow URL to the OneDrive
  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:
    Microsoft Flow Insert Data from Excel

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:
    Nintex contact OneDrive

    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:
      NIntex mail with OneDrive authorization
      Once user clicks “Provide OneDrive account credentials and authorize access” and then will let the app to access account info (in the next screen)
      OneDrive Authorization monitthe 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:
    Microsoft Flow process overview

Working example

  • I have created a simple Excel file:

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:
    Microsoft Flow choosing a file for import
  • Then I opened status page of the Flow, and was observing how the rows are getting queried and then uploaded to SharePoint:

Microsoft Flow execution monitoring

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:

SharePoint Online data imported from Excel via Microsoft Flow

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!


Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

4 Comments
  • Vinay

    Thank you, It’s nice article, I’m trying to extract data from CSV file which will upload to the SharePoint online library and add into a SharePoint List.
    I created the Nintex Office 365 WF with below actions
    – Created a web request, in the web request action given the document encoded url from input reference, username & password (global administrator)
    – Regular action to split the CSV lines
    – For each loop CSV lines, in the container regular expression to split the values from lines
    – Get Item collection, assign names from above collection
    -Finally creating list items from the collection
    When uploading the CSV files into the library, I’m getting the following error from the web request action
    “An exception occurred while processing parameter [InputPassword]” – Need help

    Note: I’m using the Nintex Office 365 trial subscription

    Thanks in Advance

    August 27, 2018 at 9:31 pm Reply
  • vinay bangari

    Thanks for your reply, I’m typing the password and also tried using the variable, I do have the special character but have change and tried but no luck. appreciated for your time and thanks in advance.

    September 11, 2018 at 3:49 am Reply
    • Tomasz Poszytek

      Have you tried to call the endpoint you set up in the “Web Request” action using Postman application for example? Maybe there is something wrong with the URL? Also you can try to use action “Call HTTP webservice” instead.

      September 11, 2018 at 2:27 pm Reply

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.