Altova MapForce 2024 Enterprise Edition

This example shows you how to create a JSON document from an Excel file with MapForce. The source Excel workbook consists of two worksheets: Office and Staff. The Office worksheet provides information about the company's address, while the Staff worksheet lists all the employees.

Staff worksheet

The business requirements are as follows:

1.Extract data from the Excel file and convert it into a JSON document.

2.The JSON file must specify, for each employee, whether their phone extension can be shared for external contracts. Only phone extensions of employees from the Marketing and Administration departments can be shared externally.

3.The shares of each employee must be indicated in the JSON file as a numeric value, which may include zero. If not known, shares must be indicated as null in the JSON file.

The mapping that accomplishes the goal above is illustrated below. This mapping is available at the following path: <Documents>\Altova\MapForce2024\MapForceExamples\\Excel_Company_To_JSON.mfd.

Excel_Company_To_JSON.mfd

As shown above, the mapping consists of a source Excel component and a target JSON component. Notice that the two worksheets of the Excel file appear as on the source Excel component. The company name is mapped from the Office worksheet, while the rest of the fields are mapped from the Staff worksheet. To view how the mapping is configured to read row data from each worksheet, click the corresponding button. For the Staff worksheet, the settings are illustrated below.

The settings above instruct the mapping to read rows starting from 1 up to the end (dynamically). The first row is a header with column names, so the First row is header... check box is selected. The dialog box also specifies the type of columns A through G, and the name with which each column appears on the mapping. For more information about Excel components, see Microsoft OOXML Excel 2007+.

The target component is a JSON structure. You can add JSON components to the mapping as described in Adding JSON Files as Mapping Components. The structure reflects the JSON schema, employees.schema.json, which is available in the same folder as the mapping. For more information about configuring the JSON component, see JSON Component Settings.

There also several intermediate components on the mapping. The first one is a value-map component. The value-map decides, based on the department, whether the phone number can be shared externally. This corresponds to business requirement #2 above. You can view the settings of the value-map component by double-clicking its title bar:

The configuration above can be summarized as follows: If Department is "Administration" or "Marketing", return true. Otherwise, return false. The true or false value computed this way is then written to the target JSON property. For more information about value maps, see Using Value-Maps.

Finally, the mapping makes use of the exists function whose purpose is to determine, in the context of each row, if a Shares value exists in the source Excel file. This function returns a Boolean true if the value exists, or false otherwise. The resulting true or false value is then supplied as input to an If-Else condition. On false, an empty string provided by a constant is passed on to the Shares as null target item. This empty value will become a null in the resulting JSON file, because this is the data type of the Shares as null item, as indicated by the icon.

If the employee's share value is numeric, it will no longer meet the If-Else condition. Instead, it will be written to the Shares as number target item (which is of numeric type ), through a separate connection.

For more information adding functions to a mapping, see Add a Function to the Mapping. If-Else conditions are covered in more detail under Filters and Conditions.

Running the mapping

To preview the generated file in MapForce, click the Output pane. The image below illustrates only a fragment of the resulting JSON file.

Mapping output

To save the mapping output to a file, do one of the following:

•On the Output menu, click Save Output File.

•Click the Save generated output toolbar button.

Automation with MapForce Server

If you have licensed MapForce Server, you can also run the mapping at the command line, on a Linux, macOS, or Windows machine. Note that running mappings on a server typically requires a few extra steps as outlined in Preparing Mappings for Server Execution. Here are the steps required to run this particular mapping on a server:

1.Right-click the mapping and select Mapping Settings from the context menu. Make sure to clear the Make paths absolute in generated code check box and save the mapping.

2.On the File menu, click Compile to MapForce Server Execution File. This generates an executable .mfx file in the <Documents>\Altova\MapForce2024\MapForceExamples\ directory.

3.Copy the generated .mfx file to a directory on the server machine (let's call it the "working directory").

4.Copy the Excel file from <Documents>\Altova\MapForce2024\MapForceExamples\\Nanonull Inc.xlsx to the working directory on the server machine.

5.Open a terminal and change the current directory to the working directory.

6.Run MapForce Server with the command below. Adjust mapforceserver_exec to be the path to the MapForce Server executable as applicable for your operating system.

In server execution, you can also run mappings as an API call, or as FlowForce Server jobs, either on demand or on recurring basis. For more information, see Compiling Mappings to MapForce Server Execution Files.