FLF to Database
This topic explains how to map data of a fixed-length text file (FLF) to an SQLite database. The files used in this topic are available in the Tutorial folder. The source text file and the target database store a list of employees. In the source file, the records are 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 goal of the mapping is to map the FLF data to the database component. We also want to map the phone extensions with a new prefix. To achieve this, take the steps below.
Step 1: Insert a text component
The first step is to add and configure a text component. Follow the instructions below:
1.Select the menu item Insert | Text file or click the 
toolbar button (Insert Text file). This opens the Insert Text Component dialog box. Select Use simple processing... and click Continue.
2.Click Input file in the Component Settings dialog box (see below) and select Altova-FLF.txt.
3.Select Fixed.
4.Clear the Assume record delimiters present check box.
5.The yellow rows are editable and enable you to specify i) the field name, ii) the data type, and iii) the field size. Set the field size to 8 (the third yellow line from the top) and press Enter. More data is now visible in the first column, which is now 8 characters wide.
6.Click Append Field to add a new field and set the length of the second field to 10 characters.
7.Use the same method to create three more fields of the following lengths: 3, 25, and 25 characters. Then change the field headers, as shown in the screenshot below.
8.In Fixed Length Field Settings, select Custom and type the hash (#) character. This instructs MapForce to treat the # character as a fill character.
10.MapForce will ask you if you would like to change the component name to match the instance files. Click Change component name. The Altova-FLF component appears in the mapping window.
Step 2: Insert a database component
The next step is to add a database component. Follow the steps below:
1.Go to Insert | Database, select SQLite, and click Next.
2.Select the Altova.sqlite database and click Connect.
3.Select the Person table (see below) and click OK.
Step 3: Design the mapping
The next step is to create a mapping:
1.Drag the core | concat function from the Libraries window into the mapping.
2.Go to Insert | Constant, select Number as a type, and enter 100 in the text field. This constant stores the new telephone extension prefix.
3.Create connections as shown below.
4.In the database component, click the Table Action button next to Person. This opens the Database Table Actions dialog box (see screenshot below).
5.Next to Action on record data, select Update if. Set the equal action for the First and Last fields. Click OK. MapForce will be instructed to update the Person table only if the first and last names in the source file are equal to the corresponding database fields. When this condition is true, the telephone extension will get a prefix 100 and will be copied to the PhoneExt field of the Person table.
6.To generate the SQL statements (for preview in MapForce), click the Output pane. To run the SQL statements against the database, click the Run SQL-script button 
.