Introduction
The Import component of Ultimate Forms, can be used to set up automated import profile for data found in various external sources. Email messages, databases, web services, REST API's, SMS messages and even regulat tab/comma delimited text files can all serve as source of the automatic import. It opens up fantastic possiblities to create automated, connected solutions, such as help desk systems or even email-based approvals.
Import from Excel
In this article I am going to demostrate the ability to import data from Excel files (.xlsx), which many of our customers requested. You can map the location of an Excel file, specify the worksheet to use and we will automatically import tabular data we find there. For simplicity sake, we expect the most common layout scenario: a table of data, with a header row, starting from A1 cell. We will automatically detect the boundaries of the table. Note that legacy Excel file formats (for instance, .xls files) are not supported.
Configuration
First you need to tell us where the file is located. If the file is in a SharePoint document library within the same tenant, simply provide the URL of the file and under Authentication leave it at Anonymous. We will know to use our app credentials to fetch the file. The file can also be placed at any location acceesible through a URL, you might need to provide user name and password to get the file from that URL.
Click on Connect, it will load the file and show the worksheets for you to select one. 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. You can also leave this field empty, this way we will always import the complete file.
Once you establish the connection, you can create one or more actions. Those actions specify what you want to do with the data once imported. 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 (think about updating Status based on external data). Sync action combines the two, it will update existing items when found or create new ones when needed.
A quick note about mappings. You can perform direct mappings (such as Title column in the file to Title column in the list) or you can perform various calculations and function to convert the raw value into something different (for instance, extract the year part of a date and store just that). You can also map conditions that will instruct the profile to only import certain item and disregard the rest.
Additional Settings
A couple of additional settings I would like to mention:
- The polling schedule can be set to None. The profile will not be executed automatically by the timer, you will run it manually when needed. Use this option to also temporarily disable a profile if needed.
- The Description field allows you to enter any additional information about a profile when needed, for example explaining its purpose to other users in your organization.
