Description |
Flat File Properties (Magic xpi 4.13)
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 and Delimited. You can select an example file that already is defined with delimiters or positions.
The Properties pane contains the following properties:
|
|
Name
|
Enter a name for the schema.
|
Description
|
Enter a description for the schema.
|
Source/Destination Type
|
Select where the Source information is located, or where to create the Destination file.
-
File: If you select file, the File Path property (below) 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: Click to 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/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
-
Custom
|
Code Page
|
Enter the desired Code page value. It is mandatory to select the Code page when the Custom option is selected in the Data Source/Destination Encoding parameter.
The default Code page value is set in the ifs.ini file under the property ([MAGIC_IBOLT]DefaultCustomCodePage). If no value is provided, the default code page is ibm-930_P120-1999
See: List of Supported Code Pages
Since version: 4.9
|
File Path
|
If you selected File in the Source/Destination Type property (above), click to use the Expression Editor to create an expression or condition that will locate the file.
|
Variable
|
If you selected Variable in the Source/Destination Type property (above), click to open the Variable List. Select a variable and then click Select.
|
Type
|
A read-only field that displays whether the flat file's type was defined as Delimited or Positional in the Flat File dialog box.
|
Columns Delimiter
|
A read-only field that displays the value that you selected in the Flat File dialog box's Columns Delimiter parameter.
|
Record Delimiter
|
A read-only field that displays the value that you selected in the Flat File dialog box's Records Delimiter parameter.
|
Include Delimiter in Data
|
Determine how you want to treat a delimiter (comma or space) that is surrounded by quotation marks ("). The default is No; 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
![](/PublicKnowledge/servlet/rtaImage?eid=ka23z000000H09Y&feoid=00ND0000005rAjf&refid=0EM3z000007lCP2)
|
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.
|
|
Create Header Line
|
Select Yes from the drop-down menu to create a flat file with a header constructed from the field names that are delimited according to your selections.
By default the field is marked as No.
|
Lines
|
The flat file schema's details and layout. To add information about the flat file's layout, click to open the Flat File dialog box.
Since version: 4.5
|
The Flat File dialog box contains the following properties:
|
|
Example File
|
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.
You can select an example file that already is defined with delimiters or positions. If you want to use an example file to define the data to map from your flat file, click to select a flat file to use as an example.
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.
Once you have set the file, you can change it later on if necessary.
|
Type
|
Determines how you want to arrange the data. Select one of the following from the drop-down list:
-
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.
-
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.
|
Columns Delimiter
|
Enter the delimiter that is used to arrange the data (,/ ;) You can also use space and tab delimiters by selecting SPACE or TAB from the drop-down list.
|
Records Delimiter
|
Enter a delimiter to indicate the records in the flat file data.
|
You can add information about the flat file layout in this dialog box. Click Add 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, a new entry is added between lines two and three when you click Add. Add information in each column as shown below:
|
Name
|
Enter the field name. The name can contain up to 260 characters. It cannot contain a dot (.) or a clothing parenthesis.
|
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.
|
|
---|