Excel Upload

Overview

Sometimes, your end-user might have to fill out your Unqork application more than once. For usability, you wouldn't want them to have to log in and out of the application over and over. Instead, it'd be easier to collect mass amounts of data in an Excel sheet instead. You could then upload that sheet, populating your application automatically. An Excel Upload feature makes that possible. Let's take a closer look.

NOTE  There is a known issue with Chrome and Chromium-based browsers where uploading XLSX and XLS files from non-US locales might incorrectly parse the timestamp value. For example, the timestamp might be off by one-tenth. To avoid this issue, use a different browser (like Firefox or Safari), change your browser's locale, or manually adjust incoming date values to YYYY-MM-DD-T12:mm:ss.sssZ as a workaround.

What You’ll Learn

In this article, you’ll learn how to configure an Excel Upload.

What is an Excel Upload

An Excel upload feature might sound self-explanatory. It allows your end-user to upload an Excel sheet to your application. But your application doesn't just store the Excel sheet. You can set up the feature to parse the Excel sheet data into your application's fields.

Here's a look at an example. Say a broker has a client applying for insurance on multiple golf courses. There are several questions the client must answer about each golf course. It would be time-consuming for the client to answer the questions for each golf course. Instead, you can give them an Excel template. The client can fill out that Excel template and then upload it into your Unqork application once. This simplifies the process and saves time. To demonstrate, let's build this example.

Here's how your module will look in Express View:

Here's how your completed module will look in the Module Builder:

What You’ll Need

To set up this use case, you’ll need:

• 1 Advanced Datagrid component

• 1 Text Field component

• 3 Number components

• 1 File component

• 1 Data Table component

• 1 Calculator component

• 1 Hidden component

• 1 Data Workflow component

In your Data Workflow component, you’ll need:

• 2 Input operators

• 1 Map Keys operator

• 1 Output operators

• 2 Console operators

Pre-Configuration

Configure the Advanced Datagrid Component

The Advanced Datagrid works as the display for the golf course data. You’ll include a Text Field component to hold the name of the golf course. You’ll also include 3 Number components. These will hold the number of annual visitors, the number of holes on the course, and the course's acreage.

1.

Drag and drop an Advanced Datagrid onto your canvas.

2.

Enter Golf Course Information in the Label.

3.

Enter adgGolfCourses in the Property Name.

4.

In the Display table, enter the following:

5.

Click Save.

Configure the Text Field Component

Next, you’ll use a Text Field component to hold the name of the golf course. You’ll place this component inside your Advanced Datagrid.

1.

Drag and drop a Text Field component onto your canvas. Place your Text Field component inside your Advanced Datagrid.

2.

Enter golfName in the Property ID.

3.

Enter Golf Course Name in the Label Text.

4.

Click Save.

Configure the Number Components

Next, you’ll use 3 Number components to hold data about the golf course. Since these fields all apply for each course, you’ll place them inside your Advanced Datagrid.

1.

Drag and drop 3 Number components onto your canvas. Place your Number components inside your Advanced Datagrid.

2.

Enter the following Property IDs and Label Text:

3.

Save each component as you add it.

Configure the File Component

Your File component is how your end-user will actually upload their Excel sheet. On its own, the component doesn't interpret any data. Later, you'll add a Data Workflow to take the files uploaded here and interpret them.

1.

Drag and drop a File component onto your canvas. Place your File component above your Advanced Datagrid.

2.

Enter fileGolfCourses in the Property ID.

3.

Enter Please Upload Your File in the Label Text.

4.

Click Save.

Configuration

Configure the Data Table Component

Next, let’s add a Data Table to your module. The Data Table is where you’ll lay out which columns of the Excel sheet belong in which fields of your application.

NOTE  We'll create an Excel template as the last step in this configuration. But keep in mind that your Source column here must match the label headings you use in your Excel sheet later.

1.

Drag and drop a Data Table component onto your canvas. Place your Data Table component above your File component.

2.

Enter golfCourseMapping in the Label and Property Name.

3.

Configure the Data Table as follows, starting with the dark blue row (Row 1):

4.

Click Save.

Configure the Calculator Component

A Data Workflow can’t process an Excel sheet on its own. First, you’ll need to take the Excel data and put it into a JSON file. You’ll use a Calculator component to do that.

1.

Drag and drop a Calculator component onto your canvas. Place your Calculator component below your Data Table.

2.

Enter calcGolfCourseUpload in the Property ID and Label Text.

3.

In the Inputs table, enter the following:

a.

ID: fileGolfCourses[0].json.

b.

Alias: A.

4.

In the Outputs table, enter the following:

a.

ID: golfCourseUpload.

b.

Formula: =A.

5.

Click Save.

Configure the Hidden Component

Next, let’s add a Hidden component to act as the container for your converted Excel sheet. You’ll later reference this component and its data in your Data Workflow.

1.

Drag and drop a Hidden component onto your canvas. Place your Hidden component below your Calculator.

2.

Enter golfCourseUpload in the Property ID and Label Text.

3.

Click Save.

Configure the Data Workflow Component

The JSON object held by your Hidden component contains all data from the Excel sheet. So, let's add a Data Workflow to take that data and separate it by golf course and field.

1.

Drag and drop a Data Workflow component onto your canvas. Place your Data Workflow below your Hidden component.

2.

Enter dwfGolfCourseUpload in the Label and Property Name.

Configure the First Input Operator

1.

Drag and drop an Input operator onto your Data Workflow canvas.

2.

Configure the Input operator’s Info window as follows:

Configure the Second Input Operator

1.

Drag and drop a second Input operator onto your Data Workflow canvas.

2.

Configure the Input operator’s Info window as follows:

Configure the Map Keys Operator

1.

Drag and drop a Map Keys operator onto your Data Workflow canvas.

2.

Configure the Map Keys operator’s Info window as follows:

3.

Connect the output port (right) of the golfCourseUpload Input operator to the upper input port (left) of the Map Keys operator.

4.

Connect the output port (right) of the golfCourseMapping Input operator to the lower input port (left) of the Map Keys operator.

Configure the Output Operator

1.

Drag and drop an Output operator onto your Data Workflow canvas.

2.

Configure the Output operator’s Info window as follows:

Configure the First Console Operator

These Console operators serve as your way to troubleshoot later if any issues arise.

1.

Drag and drop a Console operator onto your Data Workflow canvas.

2.

Configure the first Console operator’s Info window as follows:

3.

Connect the output port (right) of the golfCourseUpload Input operator to the input port (left) of the Console operator.

Configure the Second Console Operator

1.

Drag and drop a second Console operator onto your Data Workflow canvas.

2.

Configure the second Console operator’s Info window as follows:

3.

Connect the output port (right) of the Map Keys operator to the input port (left) of the Console operator.

4.

Click Save.

5.

Save your module.

Configure the Excel Sheet

Your final step happens outside of Unqork. A best practice for Excel uploads is to provide your end-user with an Excel template. Remember that the column labels must match what's used in your Data Table. For fields that have validation criteria, you can also set those in the Excel template. Keep in mind that data mapping is case sensitive, so you’ll want to set those restrictions in your template as well. In our example, we’ll set our Number of Annual Visitors, Property Acreage, and Number of Holes columns to only accept numeric values greater than 0.

1.

Open a blank workbook in Excel.

2.

Enter the following headers in the first row:

a.

Golf Course Name

b.

Number of Annual Visitors

c.

Property Acreage

d.

Number of Holes

3.

Highlight columns B, C, and D.

4.

Under the Data tab, click Data Validation.

5.

Select Whole Number from the Allow drop-down.

6.

Select Greater Than from the Data drop-down.

7.

Enter 0 as the Minimum.

8.

Click OK.

9.

Save your Workbook.

Summary

With the above steps finished, you can now test your configuration. In your Excel template, input some sample data. Then, preview your Unqork module in Express View and upload the Excel sheet.

Here's what the feature looks like in action:

Lab

You can view the completed lab for this use case here: https://training.unqork.io/#/form/5f0628a69f486b02052b8cd2/edit.