Altova MapForce 2024 Enterprise Edition

This example walks you through the steps required to create a mapping design which parses string data. The example is accompanied by a sample file. If you want to look at the sample file before starting this example, you can open it from the following path: <Documents>\Altova\MapForce2024\MapForceExamples\Tutorial\ParseString.mfd.

Let's assume a scenario where you have some legacy text data stored as a single database field. The text data is a list of employees, stored in the RESOURCE column, and formatted as fixed-length fields, as follows:

Because the text data is stored as a single database field, you cannot easily access and manipulate each individual employee record. This makes it difficult to add or remove new employees, or to sort data. For the purpose of this example, your goal is to extract the text data from the RESOURCE database field and split it into a structure so that you can easily process the records.

This task can be accomplished by using a "Parse/Serialize String" component. First, the "Parse/Serialize String" component will take the text data as input. Then it will parse it and convert it into a structure. Finally, it will write the structure to a target format. In this example, the target format is an Excel spreadsheet; however, in general, it can be any other output format supported by MapForce.

To summarize, the mapping described in this example will convert the contents of the RESOURCE database field to a table. After the mapping is executed, each table row will correspond to an employee and each column will correspond to one of the fixed-length fields, in this order: ID, Last Name, First Name, Extension, Job Title, Email.

Expected output after parsing the string

To accomplish the goal, follow the steps below:

1.Add to the mapping area the source database. The database is available as a standalone SQLite database file at the following path: <Documents>\Altova\MapForce2024\MapForceExamples\Tutorial\\dbresources.db. (To add the database component, use the Insert | Database menu command, see also Connecting to a Database ).

2.When prompted to insert the database objects, select the resources table.

3.Add to the mapping area a Text component (use the Insert | Text File menu command). Since the source data is field-delimited text, choose Use simple processing... when prompted by MapForce.

4.Configure the structure of the Text component to map to the structure of the source text data stored in the database. As you may have already noticed, in this example, the source text consists of six fixed-length fields of fixed size, as follows:

To achieve this, declare the Text component as Fixed, and add to it six fields that correspond to positions above. (To open the Component Settings dialog box, right-click the component, and then select Properties from the context menu.)

Observe the Field4 field, which uses integer as data type. Although declaring the Field4 as numeric type is optional for the scope of the current example, this ensures that the phone extension (Field4) extracted from the source text is validated as a numeric value.

5.Click the, and then select Parse Strings to FLF from the context menu. This instructs MapForce that this component will parse a string to fixed-length field (FLF) format.

6.Add to the mapping area the target Excel 2007+ component. When prompted to select a sample file, click Skip. (You can add the Excel component using the Insert | Excel 2007+ menu command, see also Adding Excel 2007+ Files as Mapping Components ).

7.Click the button next to Row 1, n=dyn node, and configure the Excel component to write a row for each text field, starting with the first row, as shown below. (For more information about Excel 2007+ component types and their configuration, see About the Excel 2007+ Component ).

8.Draw the connections between component items, as shown below.

On the left side of the mapping, the contents of the resource database column is being converted (parsed) from a string value to a MapForce structure. On the right side of the mapping, the items of the Parse (Text file) component are connected to individual Excel columns, thus splitting the source string into individual sortable cells.

You have now finished creating a MapForce design file which parses string data and creates a structure from it. If you click the Output pane, the legacy text data is now converted to individual rows and columns of the Excel spreadsheet, which was the intended goal of this mapping.