Creating a Delimited Sequential File

You can use the HOLD FORMAT DFIX command to create an alphanumeric sequential file delimited by any character or combination of characters. You can also specify whether to enclose alphanumeric values in quotation marks or some other enclosure and whether to include a header record that lists the names of the fields.

A Master File and an Access File are created to describe the delimited sequential file that is generated. The SUFFIX value in the Master File is DFIX. The Access File specifies the delimiter, the enclosure character (if any), and whether there is a header record. The Master and Access Files are useful if you will later read the sequential file using WebFOCUS.

Syntax: How to Create a Delimited Sequential File

ON TABLE {HOLD|PCHOLD} [AS filename] FORMAT DFIX
         DELIMITER delimiter [ENCLOSURE enclosure] [HEADER {YES|NO}]

where:

filename: Is the name of the file to be created. If you do not specify a name, the default name is HOLD.
delimiter: Consists of up to 30 printable or non-printable non-null characters. For a non-printable character, enter the hexadecimal value that represents the character. If you use a mixture of printable and non-printable characters, you must enter them all as hexadecimal values. To create a tab delimited file, you can specify the delimiter value as TAB or as its hexadecimal equivalent (0x09 on ASCII platforms or 0x05 on EBCDIC platforms).
Note that numeric digits and symbols used in numbers, such as a period (.), plus sign (+), or minus sign (-) cannot be used in the delimiter sequence.
enclosure: Consists of up to four printable characters used to enclose each alphanumeric value in the file. Most alphanumeric characters can be used as all or part of the enclosure sequence. However, numeric digits and symbols used in numbers, such as a period (.), plus sign (+), or minus sign (-) cannot be used in the enclosure sequence. Also note that, in order to specify a single quotation mark as the enclosure character, you must enter four consecutive single quotation marks. The most common enclosure is one double quotation mark.
HEADER {YES|NO}: Specifies whether to include a header record that contains the names of the fields in the delimited sequential file generated by the request.

Reference: Usage Notes for HOLD FORMAT DFIX

Missing data is indicated by no data. So, with enclosure, a missing alphanumeric field is indicated by two enclosure characters, while a missing numeric field is indicated by two delimiters.
Text fields are not supported with HOLD FORMAT DFIX.
While HOLD FORMAT DFIX creates a single segment file, you can manually add segments to the resulting Master and Access File. In the Access File, you can specify a separate delimiter and/or enclosure for each segment.
The extension of the generated sequential file ftm.

Top of page

Example: Creating a Pipe-Delimited File

The following request against the CENTORD data source creates a sequential file named PIPE1 with fields separated by the pipe character (|). Alphanumeric values are not enclosed in quotation marks, and there is no header record:

TABLE FILE CENTORD                           
SUM QUANTITY LINEPRICE BY REGION BY YEAR  
ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER |
END

The PIPE1 Master File specifies the SUFFIX value as DFIX:

FILENAME=PIPE1   , SUFFIX=DFIX    , $                           
  SEGMENT=PIPE1, SEGTYPE=S2, $                                  
    FIELDNAME=REGION, ALIAS=E01, USAGE=A5, ACTUAL=A05, $        
    FIELDNAME=YEAR, ALIAS=E02, USAGE=YY, ACTUAL=A04, $
    FIELDNAME=QUANTITY, ALIAS=E03, USAGE=I8C, ACTUAL=A08, $     
    FIELDNAME=LINEPRICE, ALIAS=E04, USAGE=D12.2MC, ACTUAL=A12, $

The PIPE1 Access File specifies the delimiter:

SEGNAME=PIPE1, DELIMITER=|, HEADER=NO, $

The PIPE1 sequential file contains the following data. Each data value is separated from the next value by a pipe character:

EAST|2000|3907|1145655.77    
EAST|2001|495922|127004359.88
EAST|2002|543678|137470917.05
NORTH|2001|337168|85750735.54
NORTH|2002|370031|92609802.80
SOUTH|2000|3141|852550.45    
SOUTH|2001|393155|99822662.88
SOUTH|2002|431575|107858412.0
WEST|2001|155252|39167974.18 
WEST|2002|170421|42339953.45

The following version of the HOLD command specifies both the delimiter and an enclosure character (“):

ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER | ENCLOSURE "

The Master File remains the same, but the Access File now specifies the enclosure character:

SEGNAME=PIPE1, DELIMITER=|, ENCLOSURE=", HEADER=NO, $

In the delimited file that is created, each data value is separated from the next by a pipe character, and alphanumeric values are enclosed within double quotation marks:

"EAST"|2000|3907|1145655.77     
"EAST"|2001|495922|127004359.88 
"EAST"|2002|543678|137470917.05 
"NORTH"|2001|337168|85750735.54 
"NORTH"|2002|370031|92609802.80 
"SOUTH"|2000|3141|852550.45     
"SOUTH"|2001|393155|99822662.88 
"SOUTH"|2002|431575|107858412.01
"WEST"|2001|155252|39167974.18  
"WEST"|2002|170421|42339953.45

This version of the HOLD command adds a header record to the generated file:

ON TABLE HOLD AS PIPE1 FORMAT DFIX DELIMITER |  ENCLOSURE "  HEADER YES

The Master File remains the same, but the Access File now specifies that the generated sequential file should contain a header record with column names as its first record:

SEGNAME=PIPE1, DELIMITER=|, ENCLOSURE=", HEADER=YES, $

In the delimited file that is created, each data value is separated from the next by a pipe character, and alphanumeric values are enclosed within double quotation marks. The first record contains the column names:

"REGION"|"YEAR"|"QUANTITY"|"LINEPRICE"
"EAST"|2000|3907|1145655.77           
"EAST"|2001|495922|127004359.88       
"EAST"|2002|543678|137470917.05       
"NORTH"|2001|337168|85750735.54       
"NORTH"|2002|370031|92609802.80       
"SOUTH"|2000|3141|852550.45           
"SOUTH"|2001|393155|99822662.88       
"SOUTH"|2002|431575|107858412.01      
"WEST"|2001|155252|39167974.18        
"WEST"|2002|170421|42339953.45

Top of page

Example: Creating a Tab-Delimited File

The following request against the CENTORD data source creates a sequential file named TAB1 with fields separated by a tab character:

TABLE FILE CENTORD                              
SUM QUANTITY LINEPRICE BY REGION BY YEAR     
ON TABLE HOLD AS TAB1 FORMAT DFIX DELIMITER TAB
END

As the tab character is not printable, the TAB1 Access File specifies the delimiter using its hexadecimal value.

The following is the Access File in an EBCDIC environment:

SEGNAME=TAB1, DELIMITER=0x05, HEADER=NO, $

The following is the Access File in an ASCII environment:

SEGNAME=TAB1, DELIMITER=0x09, HEADER=NO, $

Top of page

Example: Missing Data in the HOLD File

The following request against the CENTORD data source creates missing alphanumeric and numeric values in the resulting comma-delimited HOLD file:

DEFINE FILE CENTORD                                                 
AREA/A5 MISSING ON = IF REGION EQ 'EAST' THEN MISSING ELSE REGION;  
MQUANTITY/I9 MISSING ON = IF REGION EQ 'WEST' THEN MISSING ELSE 200;
END                                                                 
                                                                    
TABLE FILE CENTORD                                                  
SUM QUANTITY MQUANTITY LINEPRICE BY AREA BY YEAR                 
WHERE AREA NE 'NORTH' OR 'SOUTH'                                 
  ON TABLE HOLD AS MISS1 FORMAT DFIX DELIMITER , ENCLOSURE "        
END

In the MISS1 HOLD file, the missing alphanumeric values are indicated by two enclosure characters in a row (““) and the missing numeric values are indicated by two delimiters in a row (,,):

"",2000,3907,600,1145655.77       
"",2001,495922,343000,127004359.88
"",2002,543678,343000,137470917.05
"WEST",2001,155252,,39167974.18   
"WEST",2002,170421,,42339953.45