|
|
|
|
|
|
Import/Export facilities are provide in QED by means of redirecting normal file input/output to special import/export (Xlate) programs that reformat the input/output appropriately. For input, the external file format is converted to the internal e5 format and would be used within a Mapping as a normal File. For output, the internal e5 format of a record is converted to the external format before being written to the file.
To make use of an Xlate file within QED, enter a program name relating to the Xlate type and a parameter for the program in the device component of a QED Mapping. To set this up, use the Expand action from Devices - Amend MQM8 with the cursor on the device which is required as an Xlate device. Enter the program name and parameter on separate lines in the window that appears. For example:
xlate_prog SZ1
xlate_param 'DELIMITER=,SURROUND=' RECORD_TYPE=4 SURROUND_OPTIONAL=N RECORD_END=|FLOW_OUT_FIELD=N OUT_RECORD_LEN=00132'
or
XLATE_PROG SZ2
XLATE_PARAM 'XML_TYPE=1 SCHEMA_GEN_DEV=KQXMLSCH STYLE_GEN_DEV=KQXMLSS
MAX_LINE_LEN=132 DATE_MASK="YYYY-MM-DD" TIME_MASK="HH:MM:SS"
The different Xlate types which are available are described below.
The delimited format allows QED Mappings to read text files which have fields delimited by a given character (normally a comma and often with the fields held within quote marks).
This format can be accessed in many PC based packages, including Lotus 123, Microsoft Excel and Microsoft Access.
There can be many different types of delimited file. As a result, the parameter string for this Xlate type is quite complex. The following table lists the parameter string definition.
| Keyword | Description |
| DELIMITER= <any single character> | Field delimitation character. This is used as a separator between each piece of data in the xlate file. A comma is often used as the delimiter. Two legal hex characters can also be used to set non display characters as the delimiter, for example, 09 for the TAB character in the ASCII character set. |
| SURROUND= <any single character/space for no surround character> | Field surround character (space if no surround character is to be used). If stated this character will be placed around each item of data on the xlate file. For example, for a file which is defined DELIMITER=,SURROUND=' data would look like: "Data...","Data...","Data..." A quote or double quote character is often used as a surround character. Two consecutive surround characters are interpreted as a single occurrence of the surround character in the data and not as a real surround character. |
| RECORD_TYPE= <1/2/3/4> | Record continue indicator. A value of 1 means that either a single record (or table row) may be held on one or more lines of the output file, or a single line of the output file may contain one or more records. For output purposes the OUT_RECORD_LEN parameter is used to determine when a new line should be started so that a single record may be split over a number of lines. A value of 2 means that a single record is held on a single line of the output file. A value of 3 is a similar format to 2 but has the e5 table name at the front of each line. This allows for faster and less error prone importing. A value of 4 is similar to 3 but the first field of each record must be the record name preceded by a colon character. This is the internal e5 ship data format. When set to 1 for output purposes, the OUT_RECORD_LEN is used to determine when a new line should start. This means that a single record can be split over a number of lines. For export of data, a new export record is always started at the beginning of each new e5 record. This means that for export purposes there will never be multiple e5 records on a single export record. |
| SURROUND_OPTIONAL=<Y/N> | Optional surround character (on import). Some PC software only places the surround character around certain fields (for example, all numeric fields or all character fields or all fields with the delimiter character in them). If the surround character is not always used this indicator should be set to Y, otherwise it should be set to N. This option is not used for export files. |
| RECORD_END= <any single character/space if no special character used> | End of record character. The character used to indicate that an xlate record is complete. This is a space if no end of record character is used. |
| FLOW_OUT_FIELD= <N/Y> | Flow indicator. Only used in output files that have the record type of 1. If set to Y, the output field will be written up to the output record size (given in the OUT_RECORD_LEN parameter). If set to N, an attempt will be made to write an export file which has only complete fields on each line (but if any fields are longer than the output record size they will flow across output lines). |
| OUT_RECORD_LEN= <5 digit Number> | Output record length. Used when the record type is 1, for example, OUT_RECORD_LEN=00080. |
| DATE_MASK= <QED date mask> | Date mask. When set all dates are interpreted as being in the given QED date mask format. If not set dates are interpreted as standard e5 dates. |
| TIME_MASK= <QED time mask> | Time mask. When set all times are interpreted as being in the given QED time mask format. If not set times are interpreted as standard e5 times. |
| DATE_SURROUND= <Y/N> | Date surround indicator. A value of Y means that output xlates dates are always surrounded (when surround is optional). A value of N means that dates are never surrounded. |
| TIME_SURROUND= <Y/N> | Time surround indicator. A value of Y means that output xlates times are always surrounded (when surround is optional). A value of N means that times are never surrounded. |
| SURROUND_REPLACE= <any single character> | Surround replace character. The character used to replace an embedded surround character in a data field when writing out xlate records rather than the default of doubling up any embedded surround characters. |
| NULL_PROCESSING= <1> | Null processing indicator. A value of 1 means that if the status of the field is null then no text will be output for the field when xlate records are written out i.e. two consecutive delimiter characters will occur. |
| TRIM_TYPE= <1/2/3> | Trim type indicator. A value of 1 indicates that blank spaces are trimmed from the right of character type fields. A value of 2 indicates that if the field is spaces (blank) then 1 space is returned in the xlate record, otherwise all spaces are trimmed from the right of character fields with text in them. A value of 3 indicates that character fields are not trimmed at all. |
Each record must be less than 32000 bytes long.
The output file must be defined with the following characteristics:
MVS mainframe |
Variable Block (VB) with a record length of 32004 and a block size of 32008. |
UNIX |
Line sequential. |
A limit of 16 delimited import/export devices can be used in any one Mapping.
Comma delimited, double quote surrounds (not optional), no record continue, no end of record marker, use masked time and dates.
'DELIMITER=, SURROUND=' RECORD_TYPE=2 RECORD_END= DATE_MASK="YYDDMMCC" TIME_MASK="SS:HH:MM" SURROUND_OPTIONAL=N '
"AA","100.10","10","96201220","30:14:32","aa description line text",
"BB","100.10","10","96211220","30:15:32","bb description line text",
Semi-colon delimited, dollar surrounds (non optional), record continue, pipe symbol (bar) as end of record marker, use e5 time and dates, flow fields, record size 30.
'DELIMITER=; SURROUND=$ RECORD_TYPE=1 RECORD_END=| SURROUND_OPTIONAL=N OUT_RECORD_LEN=00030 FLOW_OUT_FIELD=Y'
$AA$;$100.10$;$10$;$20/10/2096
$;$14:32:30$;$aa description line text$|
$BB$;$100.10$;$10$;$21/10/2096
$;$15:32:30$;bb description l
Semi-colon delimited, dollar surrounds (not optional), record continue, pipe symbol (bar) as end of record marker, use e5 time and dates, do not flow fields, record size 30.
'DELIMITER=; SURROUND=$ RECORD_TYPE=1 RECORD_END=| SURROUND_OPTIONAL=N OUT_RECORD_LEN=00030 FLOW_OUT_FIELD=N'
$AA$;$100.10$;$10$;
$20/10/2096$;$14:32:30$;
$aa description line text$|
$BB$;$100.10$;$10$;
$21/10/2096$;$15:32:30$;
aa description l
Caret (hat) delimited, no surrounds, no record continue, pipe symbol (bar) as end of record marker, use e5 time and dates.
'DELIMITER=^ SURROUND= RECORD_TYPE=2 RECORD_END=| OUT_RECORD_LEN=00000'
AA^100.10^10^96201220^30:14:32^aa description line text|
BB^100.10^10^96211220^30:15:32^bb description line text|
The XML format allows QED Mappings to read and write files which have fields delimited by tags according to the eXtensible Markup Language 1.0 specification.
This format is becoming increasingly popular for data transfer within the e-commerce environment as attendant DTD's, Schemas and Stylesheets can allow any such data to be easily validated to a common standard before it is sent to its destination by a suitably equipped XML browser. Different industry groups (including government departments) are now starting to define XML data transfer format standards.
| Keyword | Description |
| XML_TYPE= <1/2> | The XML type value. Use 1 for best human readability with structured indenting and a line per tagged value. A value of 2 makes the XML compressed. Defaults to 1. |
| WHITE_SPACE= <Y/N> | If set to Y will make all space characters significant. That has the effect of turning off the XML_TYPE 1 indentation as with spaces being significant it would cause extra spaces to be returned for each XML value. Defaults to N. |
| SCHEMA_GEN_DEV= <Device> | If given will cause automatic schema generation to occur with the output being sent to the given device. Defaults to none. |
| STYLE_GEN_DEV= <Device> | If given will cause automatic stylesheet generation to occur with the output being sent to the given device. Defaults to none. |
| MAX_LINE_LEN=nnnn | When writing out XML this sets the maximum line length. This value must be in the range 40 to 4096 otherwise a length of 132 is assumed. |
| DATE_MASK=QED Date Mask | This can be set to any valid QED Date mask if the default e5 Date format is not required. |
| TIME_MASK=QED Time Mask | This can be set to any valid QED Time mask if the default e5 Time format is not required. |
| SCHEMA_FILE_NAME=schema file reference | This should be the full schema file name to be referenced within the generated XML if a link to a schema is required. Defaults to none. |
| STYLE_SHEET_FILE_NAME=stylesheet file reference | This should be the full stylesheet file name to be referenced within the generated XML if a link to a stylesheet is required. Defaults to none. |
| READ_TYPE=<1 / 2> | Set XML read type internal storage mechanism. Type 1 is optimised for speed, were as type 2 is optimised to use less memory. Default is type 1. |
Each record must be less than or equal to 4100 bytes long.
The output file must be defined with the following characteristics:
MVS mainframe |
Variable Block (VB) with a record length of 4100 and a block size of 23476. |
UNIX |
Line sequential. |
A limit of 1 input and 1 output XML file can be used in any QED Mapping.
Structured XML with the standard Schema and Stylesheet generation devices, a maximum line length of 132 and ISO standard Date and Time formats.
'XML_TYPE=1 SCHEMA_GEN_DEV=KQXMLSCH STYLE_GEN_DEV=KQXMLSS MAX_LINE_LEN=132 DATE_MASK='YYYY-MM-DD' TIME_MASK='HH:MM:SS' '
<?xml version='1.0'?>
<?xml-stylesheet type="text/xsl" href="e:\qed\xml\basda\v3.00 production\basda_order_style.xsl" ?>
<body xmlns="x-schema:w:\qed\xml\basda\v3.00 production\ebis-xml_order_v3.00.xml">
<!--E50XML NAME='POP_ORDER_BASDA3"-->
<!--E50XML MAPPING_VERSION="043"-->
<!--E50XML XML_VERSION="-->
<!--E50XML ORIGINATOR="POPX_ORDER_BASDA3" USER='TSO61"-->
<!--E50XML TARG="C30D' DATE="2003-02-12" TIME="09:18:18"-->
<!--E50XML RELEASE="4.07.01" OS="MVS.XA" FUNCTION="TEST" DB="DB2.5"-->
<!--E50XML HW="370" CODESET="EBCDIC"-->
<Order>
<OrderHead>
<Schema>
<Version>3.00</Version>
</Schema>
<Stylesheet>
<StylesheetOwner>BASDA</StylesheetOwner>
<StylesheetName>
eBIS-XML_simple.xsl
</StylesheetName>
<Version>3.00</Version>
<StylesheetType>xsl</StylesheetType>
</Stylesheet>
<Parameters>
<Language>en_GB</Language>
<DecimalSeparator>.</DecimalSeparator>
<Precision>15.2</Precision>
</Parameters>
<OriginatingSoftware>
<SoftwareManufacturer>Arelon Ltd.</SoftwareManufacturer>
<SoftwareProduct>e5 </SoftwareProduct>
<SoftwareVersion>5.0</SoftwareVersion>
</OriginatingSoftware>
See also
QED Mapping Development Home Page
|
© Advanced, Jan 2021 |
|
