Excel to JSON Operator

Overview

The Excel to JSON operator can read the contents of an Excel file, parsing them into JSON. This is particularly useful for getting your Excel data into Unqork. You can upload Excel files with a single tab or multiple tabs using this operator.

This operator uses the base64 of an Excel file (xlsx, csv, xml) and converts the data into two attributes: headings and JSON. Headings are the header fields of the file (the labels of your columns). The JSON portion includes the actual contents of your file. Here, you'll see an index for each row. And in each index, you'll see a key/value pair for each data point. If a cell is empty, you'll see a null value for that key. Here's an example of how this might look in the DevTools Console:

For the Excel to JSON operator to work, you must input the base64 of your Excel file. The operator can't read your entire Excel file. So, if you're using a File component, you'll use a Get operator to retrieve the base64 of the uploaded file. You'll see this in the lab below. If you don't use a base64 input, you'll receive a circular reference error. Similarly, if you're using a File component, the Parse Files on Upload setting must be set to ON. This setting should be ON by default, but if it's OFF, you'll also receive a circular reference error.

The Excel to JSON operator is great if you need to upload data to your Unqork environment from an Excel file. This can help you import data that was previously collected elsewhere. By importing your data, you can take advantage of all a Data Workflow has to offer.

You’ll find the Excel to JSON operator under the Value/String group at the left of the Data Workflow canvas.

What You'll Learn

In this article, you'll learn:

About the Info Window

Here's what the Excel to JSON operator looks like, along with its Info window:

And here's a breakdown of each setting in the Info window:

Setting

Description

Category

Grayed out and non-adjustable setting indicating the operation type.

Label

The name applied to the operator. This label displays within the workflow. Labels should be user-friendly and well associated with the role of the operator.

multiTabExcel

Tells the operator if the Excel file has a single tab or multiple. When set to Yes, the operator creates a separate array for each tab in the file. The name or key of each array will be the name of the original Excel tab.

Adding an Excel to JSON Operator

To see this operator in action, let's look at an example. Say you have a list of application IDs you need to import into your module. You have these IDs in an Excel file and want to make use of the Excel to JSON operator to convert the data.

To complete this use case, you'll need a sample Excel file to upload. This configuration can accept any single-tab Excel file you have on hand. The end result you see in the DevTools Console varies depending on the data you upload.

Here's how the completed use case will look in the Module Builder:

And here's how the completed use case will look in Express View, along with a sneak peek at that DevTools Console:

What You'll Need

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

  • 1 File component

  • 1 Button component

  • 1 Data Workflow component

To set up your Data Workflow, you'll need:

  • 1 Input operator

  • 1 Get operator

  • 1 Excel to JSON operator

  • 1 Console operator

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

Configure the File Component

First, let's add a File component to handle the file upload.

1. Drag and drop a File component onto your canvas.
2. Enter fileUpload in the Property ID field.
3. Enter Single Tab Excel Upload in the Label Text field.
4. Click Save.

Configure the Data Workflow Component

Next, let's add the Data Workflow. You'll include an Input operator to grab the data from your File component. But you'll also use a Get operator to retrieve the base64 of your Excel file. Remember, the Excel to JSON operator can only accept the base64, not the entire Excel file. From there, you'll pass that data to the Excel to JSON operator and finally to a Console operator. This lets you see the output in the DevTools Console.

1. Drag and drop a Data Workflow component onto your canvas. Place your Data Workflow below your File component.
2. Enter dwfExcelToJSON in the Canvas Label Text and Property Name fields.

Configure the Input Operator

1. Drag and drop an Input operator onto your Data Workflow canvas.
2. Configure the Input operator's Info window as follows:

Setting

Value

Category

Input

Component

fileUpload

Required

Yes

Source

Default

Configure the Get Operator

1. Drag and drop a Get operator onto your Data Workflow canvas.
2. Configure the Get operator's Info window as follows:

Setting

Value

Category

Get

Label

[0].data

Path

[0].data
3. Connect the output port (right) of the Input operator to the input port (left) of the Get operator.

Configure the Excel to JSON Operator

1. Drag and drop an Excel to JSON operator onto your Data Workflow canvas.
2. Configure the Excel to JSON operator's Info window as follows:

Setting

Value

Category

Excel to JSON

Label

Single Tab

mutliTabExcel

No
3. Connect the output port (right) of the Get operator to the input port (left) of the Excel to JSON operator.

Configure the Console Operator

1. Drag and drop a Console operator onto your Data Workflow canvas.
2. Configure the Console operator's Info window as follows:

Setting

Value

Category

Console

Label

Output
3. Connect the output port (right) of the Excel to JSON operator to the input port (left) of the Console operator.
4. Click Save.

Configure the Button Component

Finally, let's add a button to trigger the whole operation.

1. Drag and drop a Button component onto your canvas. Place your Button between your File component and your Data Workflow.
2. Enter btnExcelToJSON in the Property ID field.
3. Enter Single Tab Excel to JSON in the Label Text field.
4. Select Event as the Action Type.
5. Enter dwfExcelToJSON in the Trigger on Click field.

6. Click Save.
7. Save your module.

Now it's time to test your work. Open your module in Express View and upload a single-tab Excel file. Then, with the DevTools Console open, click your Single Tab Excel to JSON button. You'll see a new JSON object populate with the data from your Excel file. In the below example, you'll see data pulled from an Excel file with a single column labeled applicationId.

You'll get a different result depending on what data is in your Excel file. Try uploading different sorts of Excel data to see what your output looks like. From there, you can change settings in your Data Workflow to get the output you want. Once you have the JSON data you need, you can pass it onto another process.

Changing an Excel to JSON Operator's Settings

You can revisit and make changes to this operator.

1. Click the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator to open its Info Window.
4. Make changes to the operator's settings as needed.
5. Click Save.
6. Save your module.

Copying an Excel to JSON Operator

You can make a copy of your operator using familiar keyboard settings. You can copy and paste an exact copy of your operator, matching all settings.

1. Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator you want to duplicate.
4. On your keyboard, press Command + C (Mac OS) or Ctrl + C (Windows/Linux) to copy the operator.

You can copy more than one operator at a time. Hold Command or Ctrl on your keyboard and click all the operators you want to copy. Follow the rest of the steps as usual.

5. On your keyboard, press Command + V (Mac OS) or Ctrl + V (Windows/Linux) to paste the copied operator(s) to the Data Workflow canvas.
6. Click Save.
7. Save your module.

Did you know you can copy an operator and paste it into a different Data Workflow? You'll use the same steps outlined above.

Removing an Excel to JSON Operator

Lastly, you can delete this operator from your Data Workflow canvas. You can also use these same steps to delete a connection between two operators.

1. Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2. Click the (Settings) button to open the Data Workflow canvas.
3. Click the operator you want to delete.
4. On your keyboard, press Delete.

Once you delete this operator, make sure to connect your remaining operators. If your Data Workflow path doesn't end with an Output or Console operator, your Data Workflow won't work. So, once you delete an operator, make sure to update any remaining paths to end at an Output or Console operator.

5. Click Save.
6. Save your module.

Lab

You can view this complete use case here: https://training.unqork.io/#/form/603920229752112f9857799a/edit.