VA FileMan V. 22.0 Programmer Manual Banner


 

Main Chapter Getting Started Manual Advanced User Manual

Import and Export Tools API


FILE^DDMP: Data Import

This procedure imports data from ASCII host files into VA FileMan file entries. Each record (line of data) in the host file is stored as a new entry in a specified FileMan file.

For additional information about the Import and Export Tools, see the "Import and Export Tools" chapter of the "VA FileMan Advanced User Manual."

Format
FILE^DDMP([FILE],[[.]FIELDS],[.CONTROL],.SOURCE,[.]FORMAT)
Input
FILE (Optional) File number into which imported data will be filed. Do not pass this parameter if the import file specifies the destination VA FileMan file and fields. The file must already exist.
[.]FIELDS

(Optional) Array specifying the fields into which imported data will be filed. The array can either:

  1. Name an IMPORT template, or

  2. Directly specify the fields for import.

Do not pass this parameter if the import file specifies the destination FileMan file and fields.

If you have the import fields stored in an IMPORT template, simply set the top-level, unsubscripted node to the name of the template, surrounded by [brackets].

If you are directly specifying fields in this array, set the top-level, unsubscripted node in the FIELDS array to the list of destination field numbers at the top level of the file. Separate each field number with a semicolon. The list of field numbers should match the order of the corresponding data pieces in each import file record.

For any field number that identifies a multiple, include the top-level field number of the multiple in the top-level node of the FIELDS array. Then, set an additional node in the FIELDS array for the multiple, subscripted by the data dictionary subfile number of the multiple. Set this additional node to the list of subfield numbers in the multiple into which to have data filed, separated by semicolons. The order of subfield numbers in this node should match the order of the corresponding data pieces for the multiple in the import file record.

For subfiles within subfiles, repeat this process of identifying the top-level field number of the multiple in the appropriate FIELDS node (one data dictionary level above that of the multiple). Then add an additional node subscripted by data dictionary number identifying the fields in the subfile into which data is to be filed.

You can add more than one subentry for the same subfile (see "Importing into Subfiles" in "Details and Features" below).

If the import is based on fixed length (rather than character-delimited) data, follow each field's number with the length of the data for that field enclosed in square brackets. For example, ".01[30];.02[30];.03[10]".

.CONTROL (Optional) Pass this array by reference. You can control the behavior of FILE^DDMP by setting the following nodes in the CONTROL array:
CONTROL("FLAGS") (Optional) Concatenated string of character flags to control processing of the import.
E External values are contained in the import file. Convert the values to FileMan internal format and verify during import. If the E flag is not present, data is assumed to be in internal format and is not verified.
F Import File contains identity of destination FileMan file and fields. If F flag is not present, the FILE and FIELDS parameters are required and must contain file and field information.
CONTROL("MSGS") (Optional) Set to the root of an array (local or global) into which error messages should be returned. If a value is not passed, messages are returned in nodes descendent from ^TMP("DIERR",$J).
CONTROL("MAXERR") (Optional) Set to the number of errors which may be encountered before aborting the import. Default is not to abort.
CONTROL("IOP")

(Optional) Set to the name of the device (as stored in the DEVICE file) on which to print the Import Report. This pre-selects the output device. You can also set CONTROL("IOP") to match any of the additional formats for the IOP input variable recognized by ^%ZIS entry point (see the Kernel Systems Manual for more information on ^%ZIS and IOP).

Default is to ask the user for output device.
CONTROL("QTIME") (Optional) Set to the time for queuing the data filing and subsequent printing of the Import Results report. This pre-selects the time for queuing. The time can be in any format that ^%DT recognizes. Default is to ask the user whether or not to queue and for the queuing time.
.SOURCE

(Required) An array that identifies the import file. Pass this array by reference.

SOURCE("FILE") (Required) Set this node to the import file name.
SOURCE("PATH") (Optional) Path or directory where the file can be found. If this node is not defined, the default path is used to locate the file.
[.]FORMAT

(Required) Specifies the format of the incoming data. You can either:

  1. Pass the name of a FOREIGN FORMAT File entry in the top-level, unsubscripted node of this array, or
  2. Set individual nodes in this array to define the import format (pass by reference).

If you set individual nodes in the array to define the format, you can set:

FORMAT("FDELIM") Set this node to the field delimiter used for the imported data, if a field delimiter is used.
FORMAT("FIXED") Set this node to "YES" if the incoming data is in fixed-length format. If not set to "YES", the default format is field-delimited.
FORMAT("QUOTED") Set this node to "YES", if you would like FileMan to ignore the field delimiter in any quoted strings in the incoming data.
Output

Error messages and information supplied via EN^DDIOL are returned in ^TMP or in the array specified by MSG_ROOT. DIERR is defined if there was an error. Error messages are not returned for individual records whose import fails, however.

Example

In the example below, the import file is PEOPLE2.CSV. The import file is in Excel (Comma) format, which means the data is comma-delimited. There is a corresponding entry in the FOREIGN FORMAT file called "Excel (Comma)" describing the Excel (Comma) format.

The following code calls FILE^DDMP to import data from PEOPLE2.CSV:

    S FILE=16100
    S CONTROL("MSGS")="MYMSGS"
    S CONTROL("FLAGS")="E"
    S FIELDS=".01;14;14"
    S FIELDS(16100.014)=".01;1"
    S SOURCE("FILE")="PEOPLE2.CSV"
    S SOURCE("PATH")="VA6$:[SMITH]"
    D FILE^DDMP(FILE,.FIELDS,.CONTROL,.SOURCE,"EXCEL(COMMA)")

The import data is in external format, so the call to FILE^DDMP uses the E flag. The data in the import file contains records of five comma-delimited values that are to be imported into file #16100, as specified in the FIELDS parameter:

If the data for this import were in fixed length format, the code to set the FIELDS array might look like this:

    S FIELDS=".01[30];14;14"
    S FIELDS(16100.014)=".01[30];1[25]"

Note that the field numbers that specify a multiple at the top level have no length associated with them.

Error Codes Returned
202 Incorrect parameter was passed.
312 Required identifier is missing.
409 File does not exist.
501 Field does not exist.
520 A word-processing field was specified.
525 Multiple specified, but no fields in subfile chosen.
1810 Data could not be moved into M environment.
1812 No data found in host file.
1820 Format could not be found in the Foreign Format file.
1821 Inconsistencies in the format chosen.
1822 Incorrect data length for a fixed length format.
1833 Inconsistency involving the "F" flag.
1850 Error in device selection or queuing setup.
1870 The IMPORT template does not exist for the file.
Details and Features
Data Formats

Data fields in the import file can be either character-delimited or fixed length. The method used should match the method described in the FOREIGN FORMAT file entry whose name is passed to FILE^DDMP (alternatively, you can specify these values directly in the FORMAT parameter and not reference a FOREIGN FORMAT File entry.) The only fields from the FOREIGN FORMAT file entry used during import are:

Required fields

All required VA FileMan identifier fields for the destination file must have data filed into them from the import record:

  • If a field defined as a required identifier is not a destination field, the import is not performed.

  • If a record being filed has a null value for a required identifier, that record will not be filed.
Identifying Destination File and Fields in Import File

You can store the destination VA FileMan file and fields in the import file, rather than passing them to FILE^DDMP in the FILE and FIELDS parameters. Use the "F" flag to indicate that file and field information is being sent in the import file.

To specify the file and fields in the import file, the first line of data in the import file must be:

    FILE=filename

Don't leave any spaces between the literal tag "FILE=" and the name of the file involved. You can identify the file by file number rather than name, also.

The second line in the import file must contain a list of destination field names, in the order of the data pieces in each import file record. You can use field numbers rather than field names to identify the fields (for instance, you might want to specify a field by number if its name contains punctuation characters).

If the import is delimited, the names should be separated by whatever the specified delimiter is:

    NAME,ADDRESS

If the import is fixed length, the field names should be followed by the field length in [brackets], and then separated by a comma:

    NAME[25],ADDRESS 1[20]

To specify a field in a subfile, show the complete path to the field using the format:

    multiple fieldname:fieldname

Specify as many multiple field names as necessary (separated by colons) to indicate a complete path to the field being imported.

The third and subsequent lines of the import file should contain the data records to be filed.

Here is a listing of an example import file containing destination field information:

    FILE=DA RETURN CODES
    DA RETURN STRING,TERMINAL TYPE STRING
    [=7c,C-QVT103
    [?1;0c,C-WYSE 75
    [?1;2c,C-VT100
    [?1;6c,C-VT100
Importing into Subfiles

Each record (line of data) from an import file is always stored as a new record at the top level of the destination VA FileMan file. However, you can populate more than one entry in a subfile descendent from the new entry, from a single import record.

To file more than one entry in a subfile, repeat the subfile's multiple field number in the field string of the higher level file or subfile. Each import record must add the same set of fields to the subfile in question, however, as specified by the set of fields you list in the subfile's FIELDS(subfile#) node.

Also, new subentries need to be added to every subfile on a path to the lowest level subfile. Because of this, you must include fields for the .01 field and all the required identifiers for every subfile as well as at the top level of the file.

 


Reviewed/Updated: March 10, 2007