Tutorial

Import from Excel Workbook

Infowise allows us to easily import information from email, a webservice or database and now an Excel worksheet!

Instructions

First and foremost, find yourself the Excel workbook that you plan to use. Note that Legacy Excel formats such as .xls are not compatible. Make sure that the worksheet contains a table with header rows and horizontal columns starting at A1.

I will start out with a workbook titled Extract.xlsx with the table titled “Table” as shown below.

Save your Excel file into a Document Library in your site as shown below:

Next, we will create our Custom List that we want this information to import to. I created a List titled Stocks. Now go ahead and create your columns to correspond with the import columns in your Excel workbook similar to the below screenshot:

Inside your newly created list, navigate to the Infowise Design Screen and select ‘Import’. In General Settings, give your Profile a name and decide on the Frequency of your Import.


In the Data Provider Settings tab, select ‘Database / Web Service’ from the ‘Type’ dropdown and ‘Excel’ from the ‘Database Type’ dropdown just like below:

Now we need the URL of the Excel file to connect it to the Profile. Navigate back to your Document Library and select the ellipses next to your Excel file. Copy the URL from the pop up box. 

It should look something like this:

https://<site URL>/Shared Documents/Extract.xlsx?d=w44b7...

We will need everything up to the .xlsx extension, but everything afterwards needs to be removed. Paste this into the URL field in your Profile and click Connect.

You will know that the connection has been successful once the name of your worksheet shows in the Worksheet field as shown above.
We can also optionally specify Update date column. We will use this column value and compare it to the latest item imported during the previous run. Only items newer than the ones already imported during the last run will be imported. If you leave this field empty, the system will always import the complete file.

Now that we have created our overall Import Profile, we will want to set the Values of our columns to the Excel spreadsheet columns we plan to import. However, before this can be done, you should deice which type of Action best fits your needs. Here is a brief explanation of the Action Types:

The most common action is Create list item. It will simply convert every data row from Excel into a SharePoint list item, according to column mappings you provide. Update list item action will update existing SharePoint list items with new information, based on the unique identifier found both in the Excel file and in the list. Sync action combines the two, it will update existing items when found or create new ones when needed. An Update Date column must be specified for both the Sync and Update List Item Actions.

In the Values to set section, the dropdown on the left is for the Columns in your List. Once your List column is selected, choose an Excel column to load from the dropdown on the right which contains a Value Picker that is highlighted below:


The Value Picker will appear and the Columns in your Excel workbook will be on the left-hand side as shown below.


Set each of your List columns to correspond with an Excel import column. When done, your Action Settings section look similar to what is shown below and you will want to save by first clicking the ‘Add/ Update Action’ button and then the ‘Add Profile’ button.


You can test your new Import simply by scrolling to the top of the Import page and selecting the ‘Run Now’ action and you can debug any issues you might have using the Import History.

Once your Import has finished, your newly created list should look something like this:


You can easily add functionality to your Import by adding Conditions, changing the Action Type or updating the Frequency.

Summary

Infowise Import ability allows us to work with information both inside and outside of our Office 365 tenant. The ability to connect directly to Excel workbooks is just one of the functionalities. Infowise Import simplicity enables a set it and forget it approach that will reduce man hours in your organization.

Last modified: 10/23/2018 12:18 PM
Loading...

Add your comment

Comments are not designed to replace support calls. If you have a specific issue with one of our products, please send an email to support@infowisesolutions.com to open a support ticket.