| Contents: | Main | Chapter | See Also: | Getting Started Manual | Programmer Manual | |||
The fiollowing fields in the FOREIGN FORMAT file correspond to attributes of the formatted data that you wish to export or import:
When exporting records, all fields in this file are used in the export process. When importing records, only three fields are used in the import process:
In this section, each format characteristic is described. Some combinations of characteristics are unacceptable; these situations are mentioned.
Also, some of the fields allow you to enter M code. Export-specific variables you can use in this M code are described in the "Variables Available for Programmer Use" section.
To set up a FOREIGN FORMAT file entry, use the Define Foreign File Format Option; to print out a format, use the Print Format Documentation Option.
Many applications can import and export data, if the values of fields in each record are separated by a known character or sequence of characters. The application puts (or expects) data before the first delimiter into its first field, between the first and second delimiter into the second field, and so on. Therefore, the ability to specify and recognize these field delimiters is a crucial aspect of many data exchanges.
The Import and Export Tools' FIELD DELIMITER fields allow you to specify up to 15 characters to be placed between each field. You can directly enter any string of characters except ones that begin with a number or consist of characters that have special meaning when editing VA FileMan data (e.g., "^" or "@").
If your field delimiter begins with one of these restricted characters or consists of an unprintable control character (like <TAB>), you can enter the ASCII-value of the delimiter. When entering the ASCII values, always use three digits. Thus, <TAB> (ASCII 9) becomes "009" and "@" (ASCII 64) becomes "064". You can enter up to four ASCII values. If more than one is needed, separate the values with commas (e.g., "048,094").
If you want the user to be prompted for a field delimiter at the time the EXPORT template is being created, enter "ASK" in this field.
CAUTION: Using unprintable control characters (ASCII values less than 32) as delimiters may not have the effect you want. During either export or import, often control characters are intercepted by terminal software, communication programs, or network links; they may not be passed through unaltered as regular printable characters usually are. For example, ASCII value 5 is interpreted by many terminals as a request for their Answerback Message. Thus, putting "005" in the FIELD DELIMITER field might cause an Answerback Message to be returned by your terminal instead of the ASCII value 5 being inserted between field values.
NOTE: The importing application will find the delimiting character, if it occurs in the data. This will cause an incorrect determination of the boundary between fields. For example, if a comma (,) is the field delimiter and the data for a field was Smith,John, the importing application would put Smith into the first field and John into the second field. You can avoid this problem by specifying that data in non-numeric fields be surrounded by quotes (e.g., "Smith,John"). Most importing applications will ignore delimiters, if they occur within a quoted string. See the discussion of "QUOTE NON-NUMERIC FIELDS?" below.
Some importing applications expect a field delimiter following every field, including the final field in a record. Other applications only expect delimiters between fields; nothing follows the final field. This field allows you to specify whether or not a field delimiter should be exported after the last field. A YES answer will send the delimiter, a NO answer will not.
The contents of this field does not affect whether or not a delimiter is sent after each record.
Applications that import delimited fields need to know when one record ends and a new one begins. In most cases, records are separated by a carriage return (or by a line feed and a carriage return). This is the same as pressing the Enter/Return key at the end of a line. The Export Tool automatically puts this separator after each record; every record begins on a new line of output. You do not need to put the ASCII values for carriage return and line feed in this field.
Some applications may also require that additional characters be placed after each record. If this is the case, put those characters into the RECORD DELIMITER field. The requirements for coding the field are the same as for the FIELD DELIMITER field.
A second common way to import and export data (in addition to using delimited data) is with fixed length records. In a fixed length record, each field has a predetermined, constant data length. For example, a name field might be 30 characters long. The name "Smith,John" is only 10 characters long; thus, 20 spaces would be added to the field value to fill the required 30 characters. The next field's value would begin in the thirty-first column.
If you want to import or export fixed length records, answer YES to this field. At the time that the EXPORT template is created (or an import is done), the user will be prompted for the length of each field in the target or source file.
During export, in most cases data will be truncated when the length of a field is reached. Thus, if a field contains 32 characters but the user-defined length is 30, the last 2 characters will not be exported. However, DATE/TIME-valued fields will always be exported in their entirety. For dates, the user must indicate a data length at least as long as the exported date, which is 11 characters for standard VA FileMan dates.
NOTE: Fixed record lengths cannot be used in conjunction with field delimited data. Also, the maximum record size for exports for a fixed length format is 255 characters. There is no limit on record length during import, however.
CAUTION: Fixed length exports will succeed only if all fields are exported on the same physical line. Therefore, the total of all the field lengths must not be more than the value stored in the MAXIMUM OUTPUT LENGTH field.
In many cases, data import will be much easier if an entire record is contained on a single "line" of output. That is, there are no carriage returns within a single record, only between records. (This is a requirement for a successful fixed length export.)
In a regular VA FileMan print, the amount of data printed before a carriage return is dependent on the type of device being used for output -- a CRT screen would normally have 80 characters on a line, a printer 80 or 132. For data export, however, the physical characteristics of the output device is not controlling. Rather, the capabilities of the application importing data is overriding. Therefore, you can use the MAXIMUM OUTPUT LENGTH field to specify how long a physical record will be. For field delimited (as opposed to fixed length) exports, this record length can be larger than the traditional M data limit of 255 characters.
Put a number from 0 through 9999 into this field. The default record length is 80. If you want the user to be prompted for a record length at the time that an Export template is being created, put "0" (zero) into this field.
Regardless of the length of the maximum record, a carriage return will be written after each record is output.
NOTE: The length of a record cannot exceed 255 characters when using a fixed length format.
CAUTION: When sending exports to a Host file on a DSM for OpenVMS (e.g., VAX) system, you must add a parameter to the OPEN command, if any of your exported records are longer than 512 characters. See the "Export Data" section for details.
If this field is answered YES, the user is prompted for a field name for each exported field when the EXPORT template is created. The field names are stored in the NAME OF FOREIGN FIELD field in the EXPORT FIELD Multiple in the PRINT TEMPLATE file (#.4). See the discussion below of the FILE HEADER field for one way to use this information.
When importing data, VA FileMan will ignore the field delimiter in a quoted string when this field is set to YES.
When exporting data, if you want all values that do not belong to a NUMERIC DATA TYPE field to be surrounded by quotation marks, answer YES to this field.
Many importing applications treat data within quotation marks (") in a special way. Sometimes such data is automatically considered to be text, as opposed to numbers. Also, the importer may ignore the field delimiter character, if it falls within a quoted string. Quoting a null value from a non-numeric field will result in two double quotes ("") being exported.
During export, the DATA TYPE of a field is automatically determined for fields in the primary file and its Multiples. NUMERIC DATA TYPE fields are considered NUMERIC. There may be other fields that you want treated as NUMERIC: COMPUTED-type fields with numeric results, fields referenced by the extended pointer syntax, replies to the "EXPORT FIELD:" prompt that are computed expressions with numeric results. By default, these fields are assigned a FREE TEXT DATA TYPE. If you want the user to choose the DATA TYPE when the EXPORT template is created, answer YES to the PROMPT FOR DATA TYPE? field.
If the Export Tool assigns a non-numeric DATA TYPE or if the user chooses one of those DATA TYPEs, the field's values will be surrounded by quotes when this field contains YES.
NOTE: Do not set this field to YES if a fixed length record is being exported or imported.
The Export Tool will determine the DATA TYPE for fields in the primary file and its Multiples based on their definition in the data dictionary. Other fields are automatically assigned a DATA TYPE of FREE TEXT. If you want the user to choose the DATA TYPE of each field when creating an EXPORT template, answer YES to this field. The only DATA TYPEs recognized by the Export Tool are FREE TEXT, NUMERIC, and DATE/TIME.
The DATA TYPE entered by the user controls whether or not the values from that field will be surrounded by quotes if the QUOTE NON-NUMERIC FIELDS? field is set to YES. The user supplied DATA TYPE does not affect how numbers are exported; numeric export is controlled by the DATA TYPE in the data dictionary only.
Numeric fields with no data (a "null" value) will result by default in nothing being exported for that field. For fixed record length exports, this should not be a problem. However, if your importing application uses spaces as a delimiter, you may need a printable character to be exported for null-valued numeric fields. If you want a character or characters (such as "0" or ".") substituted for numeric nulls, put them into this field. Null values for NUMERIC DATA TYPEs in the primary file (including its Multiples) will have this character exported. If you want quotes ('') in your substitute string, enter two quote marks ("") for each quote you want.
NOTE: Do not put anything in this field when defining a fixed length format.
CAUTION: There will be no substitution for null values if the field being exported is not in the primary file; that is, if it is reached using relational navigation.
The native, or default, format for dates varies from application to application. VA FileMan uses two formats: an internal, or storage, format (YYYMMDD, where YYY is the year minus 1700) and an external, or default display, format (MON DD,YYYY). When data from a DATE/TIME DATA TYPE field is exported, it is in the external format.
Since the importing application may recognize a different format, you can change the exported value by placing M code in this field (only those with programmer access can enter code in this field.) When this M code is executed, the local variable X will contain the date in VA FileMan internal format. Your M code should result in the local variable Y containing the date in the format you want exported.
If your format will be used with Kernel, it is recommended that you make use of the date extrinsic functions provided by Kernel, if possible. See the Kernel Systems Manual for details.
Data from fields with DATE/TIME DATA TYPEs in the primary file, its Multiples, and pointed-to files is altered by the code in this field; date values from other sources is not. There is another way to change the exported output; you can use a VA FileMan function when selecting fields for export:
THEN EXPORT FIELD: NUMDATE(DATE OF BIRTH)
The DATE FORMAT field will have no effect on that output.
Some applications require special information to process the data in the file that is imported. For example, the field names might be needed. Also, you may want to put some special data into the file for identification or documentation purposes.
The FILE HEADER field allows you to output information before the stream of exported data. This field can contain either a literal string surrounded by quotation marks (e.g., "Data for Lotus 1-2-3") or M code that, when executed, will write the desired output.
You can put M code here only if you have programmer access. The local variable DDXPXTNO, which equals the internal entry number in the PRINT TEMPLATE file (#.4) of the EXPORT template being used for data output, is defined when the code is executed. You can use this variable to access information about the export. The DATA TYPE, length, and foreign field name are stored in the EXPORT FIELD Multiple (#100).
NOTE: See the data dictionary for the PRINT TEMPLATE file for additional information.
You can use this field like the FILE HEADER field. The literal or M code will be output after the exported data.
Some of the fields in the FOREIGN FORMAT file allow you to enter M code, if you have programmer access. You may want to use data stored in the EXPORT template entry at the time the export is performed. You may also want to access information in the FOREIGN FORMAT file entry used for the export.
Two variables are available for use in the M code entered in FOREIGN FORMAT file fields:
Consult the data dictionaries of the two files for fields that may contain useful information about either the format or the specific export itself. The EXPORT FIELD Multiple in the PRINT TEMPLATE file might be of particular interest. This Multiple contains information about each field being exported.
Reviewed/Updated: March 4, 2007