Excel Upload

Preconfiguration

These instructions assume you have a new Front-End module open, saved, and with a title.

Configure the Advanced Datagrid Component

The Advanced Datagrid component will display the golf course data, including the name of the golf course, the number of annual visitors, the number of holes on the course, and the course's acreage.

1. In the Module Builder, drag and drop an Advanced Datagrid component onto your canvas.

2. In the Property ID field, enter Golf Course Information.

3. In the Label Text field, enter adgGolfCourses.

4. In the Display table, enter the following:

   #	ID	Formula	Heading
   1	golfName		Course Name
   2	golfVisitors		Number of Annual Visitors
   3	golfHoles		Number of Holes
   4	golfAcreage		Property Acreage

   The entries in the Display table are for the components you’ll add in the next steps.

   

5. Click Save Component.

Configure the Text Field Component

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

1. Drag and drop a Text Field component inside your Advanced Datagrid component.

2. In the Property ID field, enter golfName.

3. In the Label Text field, enter Golf Course Name.

4. Click Save Component.

Configure the Number Components

Next, you’ll use three Number components to store data about the golf course, placing them inside your Advanced Datagrid component.

1. Drag and drop three Number components inside your Advanced Datagrid component.

2. In the Property ID and Label Text fields, enter the following for each component:

   #	Property ID	Label Text
   1	golfVisitors	Number of Annual Visitors
   2	golfHoles	Number of Holes
   3	golfAcreage	Property Acreage

3. Click Save Component for each component as you add it.

Configure the File Component

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

1. Drag and drop a File component onto your canvas, placing it above your Advanced Datagrid component.

2. In the Property ID field, enter fileGolfCourses.

3. In the Label Text field, enter Please Upload Your File.

4. Click Save Component.

Configuration

Configure the Data Table Component

Next, let’s add a Data Table component to determine which columns of the Excel sheet belong in which fields of your application.

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

1. Drag and drop a Data Table component onto your canvas, placing it above your File component.

2. In the Property ID and Label Text fields, enter golfCourseMapping.

3. Configure the data table as follows:

   Source	Target
   Golf Course Name	golfName
   Number of Annual Visitors	golfVisitors
   Property Acreage	golfAcreage
   Number of Holes	golfHoles

   

4. Click Save Component.

Configure the Calculator Component

A Data Workflow can’t process an Excel sheet on its own. First, you must retrieve the Excel data and add it to a JSON file using a Calculator component.

1. Drag and drop a Calculator component onto your canvas, placing it below your Data Table component.

2. In the Property ID and Canvas Label Text fields, enter calcGolfCourseUpload.

3. Set the Trigger Type as Watch.

4. In the Inputs table, enter the following:

   Source	Alias	Required
   fileGolfCourses[0].json	A	(unchecked)

5. In the Outputs table, enter the following:

   Source	Formula
   golfCourseUpload	=A

   This configuration outputs the data to the golfCourseUpload Hidden component you’ll add next.

   

6. Click Save Component.

Configure the Hidden Component

Next, let’s add a Hidden component to act as a storage 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, placing it below your Calculator component.

2. in the Property ID and Label Text fields, enter golfCourseUpload.

3. Click Save Component.

Configure the Data Workflow Component

The JSON object stored by your Hidden component contains all data from the Excel sheet. Let's add a Data Workflow component to retrieve that data and separate it by golf course and field.

1. Drag and drop a Data Workflow component onto your canvas, placing it below your golfCourseUpload Hidden component.

2. In the Property ID and Canvas Label Text fields, enter dwfGolfCourseUpload.

Configure the First Input Operator

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

2. Configure the operator’s Info window as follows:

   Setting	Value
   Category	Input
   Component	golfCourseUpload
   Required	Yes
   Source	Default

Configure the Second Input Operator

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

2. Configure the operator’s Info window as follows:

   Setting	Value
   Category	Input
   Component	golfCourseMapping
   Required	Yes
   Source	Default

Configure the Map Keys Operator

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

2. Configure the operator’s Info window as follows:

   Setting	Value
   Category	Map Keys
   Label
   Source Column	Source
   Target Column	Target

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 operator’s Info window as follows:

   Setting	Value
   Category	Output
   Component	adgGolfCourses
   Action	Value

Configure the First Console Operator

These Console operators let you troubleshoot if any issues arise.

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

2. Configure the operator’s Info window as follows:

   Setting	Value
   Category	Console
   Label

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 operator’s Info window as follows:

   Setting	Value
   Category	Console
   Label

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

4. Click Save Component.

5. Save your module.

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

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 component. You can also set up fields with validation criteria in the Excel template. Keep in mind that data mapping is case sensitive, so you must 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 accept numeric values greater than 0.

1. In Excel, open a blank workbook.

2. In the first row, enter the following headers:

   1. Golf Course Name

   2. Number of Annual Visitors

   3. Property Acreage

   4. Number of Holes

   

3. Highlight columns B, C, and D.

4. Click the Data tab and select Data Validation.

   

5. From the Allow drop-down, select Whole Number.

6. From the Data drop-down, select Greater Than.

7. Set the Minimum as 0.

   

8. Click OK.

9. Save your Workbook.

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