Flat File Properties (Magic xpi 3.x)
The Data Mapper lets you use data from any unformatted plain text file (flat files) and map them to a Destination. This data can be used for any Destination type. You can use the flat file's data to make changes in a database, to pass parameters and call a flow, or to create XML or HTML files.
The Flat File properties let you define how to break up the data in a flat file so that the Data Mapper can map the correct data. When you select Flat File as your Destination, you create a flat file. In this case, you define how to arrange the data in the flat file you are creating.
You can arrange the data in the following ways:
Positional: When you arrange the data in this way you define how many places to count for each record. In this case you enter where you want to start counting and how many spaces to count. For example, you can create a piece of data from a flat file that begins on the third space and is 13 spaces long. If the data in the flat file is:
1.new york city new york 2. chicago illinois 3. Los Angeles California
The Data Mapper will read New York City as the data because the n in New York is in the third space and New York City is 13 spaces long. You must define all positions in your flat file for any data you want to map. If you do not want to map the data, you do not have to define its position.
Delimited: Enter a delimiter, usually a comma (,). The Data Mapper will recognize a new chunk of data each time the defined delimiter appears. If you also want to use records, you can use a record delimiter. In the example above with delimiters, we can define a regular delimiter as a comma (,) and a record delimiter as a semicolon (;).
1.,new york city,new york;2.,chicago,illinois;3.,Los Angeles,California;
You can break up each element as a piece of data and specific groups of data as a record. For example, each number, city, and state are separate pieces of data, but a number, city, and state together are a record.
Example File: You can select an example file that already is defined with delimiters or positions. When you select an example file, click Refresh and the table data is automatically entered according to the defined positions or delimiters in the example file.
The Flat File Source and Destination Properties dialog boxes have the following properties:
|
|
|
|
Name
|
Enter a name for the schema.
|
|
Description
|
Enter a description for the schema.
|
|
Example File
|
If you want to use an example file to define the data to map from your flat file, browse to select a flat file to use as an example.
An example file is a flat file that is in the same format with the same elements as the flat file used in the current mapping operation.
The Data Mapper will use the information in the example file to define how to arrange the flat file data for your flat file.
Once you have set the file, and have mapped a Source to a Destination, you cannot change it. If you try to change it, an information message tells you that it is not possible.
|
|
Data Source/Destination
|
Select where the Source information is located, or where to create the Destination file.
-
File: If you select file, a field is available to add the name and location of the flat file with the Source data or the Destination of the file you are creating. Click  to open the Expression Editor. Use the Expression Editor to create an expression or condition that will locate the file. This allows you to define a dynamic location for the file. If you want to work with a static location, enter the full path to the file in the field or create a (flow, context, global, or business process) variable that holds the full path to the file and select the variable from the Expression Editor.
-
Variable: Select a variable to hold the XML file with the data you want to use. Only variables of the BLOB type are available. The XML data must be contained in the BLOB.
|
|
Data Source File
|
If you selected File in the Data Source property, browse to find the file used for the operation.
If you selected Variable, click  to open the Variable List. Select a variable and then click Select.
|
|
Data Source/Destination Encoding
|
The encoding of the flat file that you want to work with. Select one of the following from the drop-down list:
-
ANSI (default)
-
Unicode
-
UTF-8
Since version: V3.3
|
|
Append Data (Destination only)
|
Select the check box to append the new mapping to work with existing flat files.
|
Note:
|
-
You need to map at least one compound node when you select the Append Data check box.
-
You cannot append several Destinations to the same variable (due to overwriting) or to the same physical file (due to OS locking).
|
|
|
Create Header Line (Destination only)
|
Select the check box to create a flat file with a header constructed from the field names that are delimited according to your selections.
For example: ID,NAME,DESCRIPTION,SIZE
This field is not available if you select the Append Data check box (above), or if you select the Positional flat file type (below).
Since version: V3.2 SP1
|
|
Positional/Delimited
|
Select Positional or Delimited.
|
|
Delimiter
|
Enter the delimiter that is used to arrange the data (,/ ;) You can also use space and tab delimiters by typing SPACE or TAB in all capital letters.
|
|
Record Delimiter
|
Enter a delimiter to indicate the records in the flat file data.
|
|
Include Delimiter in Data
|
This property lets you determine how to treat a delimiter (comma or space) that is surrounded by quotation marks ("). The default is unchecked; in this case the delimiter character is treated as a delimiter and each section followed by that character represents a new field.
For example: "new york city, new york", chicago, illinois – includes four fields:
new york city
new york
chicago
illinois
When the check box is selected, Magic xpi will search for pairs of double quotes ("). Any delimiter character appearing inside double quotes will not be treated as a delimiter.
For example: "new york city, new york", chicago, illinois – includes 3 fields:
new york city, new york
chicago
illinois
|
Note:
|
For flat files used in the Destination, the output text is always surrounded by single straight quotation marks (' ') to keep any delimiters as part of the data.
|
|
You can add information about the flat file layout in the table at the bottom of the dialog box.
-
Click New to add a new field to the table. A new entry is added in the line directly below the line that is selected. For example, if you select the second entry in a list of five items, when you click New, a new entry is added between lines two and three. Add information in each column according to the table below:
|
|
|
|
Name
|
Enter the field name. The name can contain up to 260 characters. It cannot contain spaces, or special characters other than a dot (.).
|
|
Data type
|
Select Alpha, Numeric, Date, or Time.
|
|
Format
|
Enter the data type format. For example, if you enter 15 and the Data type is Alpha, this indicates a 15 character alphanumeric parameter, or if the data type is Date, the format can be MM/DD/YY, indicating that the date will be in a Month/Day/Year format each indicated by two digits (for example, 05/16/04 for May 16, 2004). For a full description, see Picture Formats.
|
|
From
|
Enter the Positional location as a figure greater than 0, e.g. 1. This field is enabled only for positional flat files.
|
|
Length
|
Enter a number to indicate the field length (for example, Alpha 30 indicates that the data type is Alpha of 30 characters in length).
|
-
To delete a field, select a field in the table and then click Delete.
-
To move a field up or down in the list, select the line with the field you want to move and click the arrows to the right of the list to move the selected line up or down in the list.
-
Click Refresh to reload the example file and replace the currently existing fields according to the new delimiter and example file. This is available when the schema is a delimited flat file and there is an example file.
-
Click Save to save your flat file's properties as a .txt file.
-
Click Load to load flat file properties that you previously created and saved as .txt files.
-
Click OK to save your new entries, and to close the dialog box.
-
Click Cancel to exit the dialog box without saving your new entries.
|
Note:
|
A single flat file field is limited to 32,000 characters.
|
The Properties pane contains the following properties:
