Creating a Delimited Sequential File

How to:

Reference:

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, whether to include a header record that lists the names of the fields, whether to preserve leading and trailing blank spaces in alphanumeric data, and whether to insert a delimiter between records in the resulting file. (Note that when RDELIMITER is included, the RECFM is UB).

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), whether to preserve leading and trailing blank spaces in alphanumeric data, whether there is a header record, and the record delimiter, if there is to be one. 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}]
 [PRESERVESPACE {YES|NO}]  [RDELIMITER rdelimiter]

where:

filename
Is the name of the file to be created. If you do not specify a name, the default name is HOLD.
delimiter

Is the delimiter sequence consisting of up to 30 printable or non-printable non-null characters. This represents character semantics. For example, if you are using DBCS characters, the delimiter can be up to 60 bytes. Characters may also be represented by their 0x hex values which is the required specification method for non-printable characters. 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). To create a single-quote delimited file, you must specify the single quote DELIMITER value as its hexadecimal equivalent (0x27 on ASCII platforms or 0x7D on EBCDIC platforms), otherwise the request will be mis-interpreted and result in an unusable HOLD file.

enclosure

Is the enclosure sequence. It can be up to four printable or non-printable characters used to enclose each alphanumeric value in the file. This represents character semantics. For example, if you are using DBCS characters, the enclosure can be up to 8 bytes. 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.

If you use a mixture of printable and non-printable characters, you must enter them all as hexadecimal values. For printable characters, you can either use the characters themselves or their hexadecimal equivalents (for example, the ampersand character may be interpreted as the beginning of a variable name rather than as part of the enclosure.)

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.
PRESERVESPACE {YES|NO}

Specifies whether to retain leading and trailing blanks in alphanumeric data. YES preserves leading and trailing blanks. NO only preserves leading and trailing blanks that are included within the enclosure characters. NO is the default value.

Note: PRESERVESPACE is overridden by the ENCLOSURE option. Therefore, exclude the enclosure option in order to have the PRESERVESPACE setting respected.

rdelimiter

Is the record delimiter sequence consisting of up to 30 printable or non-printable non-null characters. This represents character semantics. For example, if you are using DBCS characters, the delimiter can be up to 60 bytes. 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. For printable characters you can either use the characters themselves or their hexadecimal equivalents (for example, the ampersand character may be interpreted as the beginning of a variable name rather than as part of the delimiter). To use a tab character as the record delimiter, you can specify the delimiter value as TAB or as its hexadecimal equivalent (0x09 on ASCII platforms or 0x05 on EBCDIC platforms). The comma (,) is not supported as a record delimiter.

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. When RDELIMITER is included, the RECFM is UB.

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 is ftm.
  • HOLD FORMAT DFIX with the PRESERVESPACE YES option creates a file in which leading and trailing blank spaces are preserved in alphanumeric data. It also adds the attribute PRESERVESPACE=YES in the Access File. This attribute causes leading and trailing blank spaces to be preserved when reading a FORMAT DFIX file.

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

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, $

Example: Creating a Delimited File With Blank Spaces Preserved

The following request against the GGSALES data source creates a comma-delimited file. The original alphanumeric data has trailing blank spaces. The PRESERVESPACE YES option in the HOLD command preserves these trailing blank spaces: 

APP HOLDDATA APP1
APP HOLDMETA APP1
TABLE FILE GGSALES
SUM DOLLARS UNITS
BY REGION
BY CATEGORY
BY PRODUCT
ON TABLE HOLD AS DFIX1 FORMAT DFIX DELIMITER , PRESERVESPACE YES
END

The following Master File is generated: 

FILENAME=DFIX1   , SUFFIX=DFIX    , $
  SEGMENT=DFIX1, SEGTYPE=S3, $
    FIELDNAME=REGION, ALIAS=E01, USAGE=A11, ACTUAL=A11, $
    FIELDNAME=CATEGORY, ALIAS=E02, USAGE=A11, ACTUAL=A11, $
    FIELDNAME=PRODUCT, ALIAS=E03, USAGE=A16, ACTUAL=A16, $
    FIELDNAME=DOLLARS, ALIAS=E04, USAGE=I08, ACTUAL=A08, $
    FIELDNAME=UNITS, ALIAS=E05, USAGE=I08, ACTUAL=A08, $

The following Access File is generated:

SEGNAME=DFIX1, DELIMITER=',', HEADER=NO, PRESERVESPACE=YES, $

In the DFIX1 file, the alphanumeric fields contain all of the blank spaces that existed in the original file: 

Midwest    ,Coffee     ,Espresso        ,1294947,101154
Midwest    ,Coffee     ,Latte           ,2883566,231623
Midwest    ,Food       ,Biscotti        ,1091727,86105
Midwest    ,Food       ,Croissant       ,1751124,139182
Midwest    ,Food       ,Scone           ,1495420,116127
Midwest    ,Gifts      ,Coffee Grinder  ,619154,50393
Midwest    ,Gifts      ,Coffee Pot      ,599878,47156
Midwest    ,Gifts      ,Mug             ,1086943,86718
Midwest    ,Gifts      ,Thermos         ,577906,46587
Northeast  ,Coffee     ,Capuccino       ,542095,44785
Northeast  ,Coffee     ,Espresso        ,850107,68127
Northeast  ,Coffee     ,Latte           ,2771815,222866
Northeast  ,Food       ,Biscotti        ,1802005,145242
Northeast  ,Food       ,Croissant       ,1670818,137394
Northeast  ,Food       ,Scone           ,907171,70732
Northeast  ,Gifts      ,Coffee Grinder  ,509200,40977
Northeast  ,Gifts      ,Coffee Pot      ,590780,46185
Northeast  ,Gifts      ,Mug             ,1144211,91497
Northeast  ,Gifts      ,Thermos         ,604098,48870
Southeast  ,Coffee     ,Capuccino       ,944000,73264
Southeast  ,Coffee     ,Espresso        ,853572,68030
Southeast  ,Coffee     ,Latte           ,2617836,209654
Southeast  ,Food       ,Biscotti        ,1505717,119594
Southeast  ,Food       ,Croissant       ,1902359,156456
Southeast  ,Food       ,Scone           ,900655,73779
Southeast  ,Gifts      ,Coffee Grinder  ,605777,47083
Southeast  ,Gifts      ,Coffee Pot      ,645303,49922
Southeast  ,Gifts      ,Mug             ,1102703,88474
Southeast  ,Gifts      ,Thermos         ,632457,48976
West       ,Coffee     ,Capuccino       ,895495,71168
West       ,Coffee     ,Espresso        ,907617,71675
West       ,Coffee     ,Latte           ,2670405,213920
West       ,Food       ,Biscotti        ,863868,70436
West       ,Food       ,Croissant       ,2425601,197022
West       ,Food       ,Scone           ,912868,72776
West       ,Gifts      ,Coffee Grinder  ,603436,48081
West       ,Gifts      ,Coffee Pot      ,613624,47432
West       ,Gifts      ,Mug             ,1188664,93881
West       ,Gifts      ,Thermos         ,571368,45648

Creating the same file with PRESERVESPACE NO removes the trailing blank spaces: 

Midwest,Coffee,Espresso,1294947,101154
Midwest,Coffee,Latte,2883566,231623
Midwest,Food,Biscotti,1091727,86105
Midwest,Food,Croissant,1751124,139182
Midwest,Food,Scone,1495420,116127
Midwest,Gifts,Coffee Grinder,619154,50393
Midwest,Gifts,Coffee Pot,599878,47156
Midwest,Gifts,Mug,1086943,86718
Midwest,Gifts,Thermos,577906,46587
Northeast,Coffee,Capuccino,542095,44785
Northeast,Coffee,Espresso,850107,68127
Northeast,Coffee,Latte,2771815,222866
Northeast,Food,Biscotti,1802005,145242
Northeast,Food,Croissant,1670818,137394
Northeast,Food,Scone,907171,70732
Northeast,Gifts,Coffee Grinder,509200,40977
Northeast,Gifts,Coffee Pot,590780,46185
Northeast,Gifts,Mug,1144211,91497
Northeast,Gifts,Thermos,604098,48870
Southeast,Coffee,Capuccino,944000,73264
Southeast,Coffee,Espresso,853572,68030
Southeast,Coffee,Latte,2617836,209654
Southeast,Food,Biscotti,1505717,119594
Southeast,Food,Croissant,1902359,156456
Southeast,Food,Scone,900655,73779
Southeast,Gifts,Coffee Grinder,605777,47083
Southeast,Gifts,Coffee Pot,645303,49922
Southeast,Gifts,Mug,1102703,88474
Southeast,Gifts,Thermos,632457,48976
West,Coffee,Capuccino,895495,71168
West,Coffee,Espresso,907617,71675
West,Coffee,Latte,2670405,213920
West,Food,Biscotti,863868,70436
West,Food,Croissant,2425601,197022
West,Food,Scone,912868,72776
West,Gifts,Coffee Grinder,603436,48081
West,Gifts,Coffee Pot,613624,47432
West,Gifts,Mug,1188664,93881
West,Gifts,Thermos,571368,45648

Example: Specifying a Record Delimiter

The following request against the GGSALES data source, the field delimiter is a comma, the enclosure character is a single quotation mark, and the record delimiter consists of both printable and non-printable characters, so it is specified as the following hexadecimal sequence:

  • 0x: character sequence identifying the delimiter as hexadecimal character codes.
  • 2C: hexadecimal value for comma (,).
  • 24: hexadecimal value for dollar sign ($).
  • 0D: hexadecimal value for carriage return.
  • 0A: hexadecimal value for new line. 
TABLE FILE GGSALES   
PRINT DOLLARS UNITS CATEGORY REGION
ON TABLE HOLD AS RDELIM1 FORMAT DFIX DELIMITER , ENCLOSURE ''''   
HEADER NO RDELIMITER 0x2C240D0A   
END

The generated Master File follows: 

FILENAME=RDELIM1 , SUFFIX=DFIX    , $
  SEGMENT=RDELIM1, SEGTYPE=S0, $
    FIELDNAME=DOLLARS, ALIAS=E01, USAGE=I08, ACTUAL=A08, $
    FIELDNAME=UNITS, ALIAS=E02, USAGE=I08, ACTUAL=A08, $
    FIELDNAME=CATEGORY, ALIAS=E03, USAGE=A11, ACTUAL=A11, $
    FIELDNAME=REGION, ALIAS=E04, USAGE=A11, ACTUAL=A11, $

The Access File contains the delimiters and enclosure characters: 

SEGNAME=RDELIM1, 
  DELIMITER=',', 
  ENCLOSURE='''', 
  HEADER=NO, 
  RDELIMITER=0x2C240D0A, 
  PRESERVESPACE=NO, $

Each row of the resulting DFIX file ends with the comma-dollar combination and a carriage return and line space. a partial listing follows: 

20805,1387,'Coffee','Northeast',$
20748,1729,'Coffee','Northeast',$
20376,1698,'Coffee','Northeast',$
20028,1669,'Coffee','Northeast',$
19905,1327,'Coffee','Northeast',$
19470,1770,'Coffee','Northeast',$
19118,1738,'Coffee','Northeast',$
18720,1560,'Coffee','Northeast',$
18432,1536,'Coffee','Northeast',$
17985,1199,'Coffee','Northeast',$
17630,1763,'Coffee','Northeast',$
16646,1189,'Coffee','Northeast',$
15650,1565,'Coffee','Northeast',$
15450,1545,'Coffee','Northeast',$
15435,1029,'Coffee','Northeast',$
14270,1427,'Coffee','Northeast',$

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