Example: Mapping Fixed-Length Text Files to Databases

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > CSV and Text Files >

Example: Mapping Fixed-Length Text Files to Databases

This example illustrates a data mapping operation between a fixed-length text file (FLF) text file and a Microsoft Access database. The files used in the example are available in the <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\ folder.  Both the source text file and the target database store a list of employees. In the source file, the records are implicitly delimited by their size, as follows:

 

Field position and name

Size (in characters)

Field 1 (First name)

8

Field 2 (Last name)

10

Field 3 (Phone extension)

3

Field 4 (Email)

25

Field 5 (Position)

25

 

The goals of the mapping is to update the phone extension of each employee in the database to the one existing in the source file, while adding the prefix "100" to each extension. To achieve the goal, take the steps below.

 

Step 1: Insert and configure the text component

1.Select the menu option Insert | Text file, or click the insert Text file icon ic-text.
2.Click the Input file button and select the file <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\Altova-FLF.txt file. You will notice that the file is made up of a single string, and contains fill characters of type #.

mf_flf_02

3.Select Fixed.

mf_flf_03

4.Clear the Assume record delimiters present check box.

mf_flf_04

5.The three rows highlighted in yellow are editable, and enable you to specify i) the field name ii) the data type and iii) the field size. Type 8 as the new field size, and press Enter. More data is now visible in the first column, which is now defined as 8 characters wide.

mf_flf_05

6.Click Append Field to add a new field, and set the length of the second field to 10 characters.

mf_flf_06

7.Use the same method to create three more fields of the following lengths: 3, 25, and 25 characters, and change the field headers to make them easier to map: First, Last, Tel.-Ext, Email, Title. The preview will then look like this:

mf_flf_07

8.In the Fixed Length Field Settings group, select Custom, and type the hash (#) character. This instructs MapForce to treat the # character as fill character.

mf_flf_08

9.Click OK.

mf_flf_09

10.Click Change component name. The Text file component appears in the Mapping window. Data can now be mapped to and from this component.

 

mf_flf_10

 

Step 2: Insert the database component

1.Select the menu command Insert | Database, select Microsoft Access, and then click Next.
2.Select the altova.mdb database available in the <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\ folder, and click Connect.
3.Select the Person table and click OK.

mf_flf_11

 

Step 3: Design the mapping

1.Drag the core | concat function from the Libraries window into the mapping.
2.Select the menu command Insert | Constant, select Number as type, and enter 100 as value. This constant stores the new telephone extension prefix.

mf_flf_12

3.Create the mapping as shown below.

mf_flf_13

4.On the database component, click the Table Action button btn_dbcomp_action_in next to Person.
5.Next to Action on input data, select Update If, and ensure that the action for First and Last fields is set to equal.  This instructs MapForce to update the Person table only if the first and last name in the source file is equal to the corresponding database field. When this condition is true, the action taken is defined by the mapping. In this case, the telephone extension is prefixed by 100, and copied to the PhoneExt field of the Person table.

mf_flf_14

 

Step 4: Run the mapping

To generate the SQL statement (for preview in MapForce), click the Output tab. To run the SQL statements against the database, click the Run SQL-script button ic-start-sel.


© 2019 Altova GmbH