CSV

CSV

Top  Previous  Next

 

The CSV display option keyword specifies that the report should be produced in CSV format.

 

 

Format

 

CSV {mode} {"delimiter"} {TO pathname | AS file id {NO.QUERY | APPENDING}} {ENCODING name}

 

where

 

modeis a numeric value specifying the format rules to be applied.

 

delimiteris an alternative delimiting character. This may not be a double quote.

 

pathnameis the pathname of the file to receive the output.

 

file  idis a filename and record id pair identifying the destination for the output.

 

nameis the name of an encoding to be applied.

 

 

The CSV keyword produces a report in CSV (comma separated variable) format as used by many software products. In this format, each item in the report is separated by a comma instead of the usual tabular style of report. QM extends this format by allowing use of an alternative delimiter character.

 

The mode option specifies the format rules to be applied. A mode value of 1 (the default if no mode is given) produces output that conforms to the CSV format specification (RFC 4180). This requires that items containing double quotes or the delimiter character are enclosed in double quotes with embedded double quotes replaced by two adjacent double quotes.

 

A mode value of 2 encloses all non-null values in double quotes except for numeric items that do not contain a comma. Embedded double quotes are replaced by two adjacent double quotes.

 

A mode value of 3 encloses all values in double quotes. Embedded double quotes are replaced by two adjacent double quotes.

 

The delimiter may be set to a tab character by use of the special syntax "<TAB>". Other non-printing characters can be specified by use of the ^nnn notation where nnn is the three digit character number from the ASCII character set.

 

Multivalued items will be split over multiple lines of CSV output. If an item is defined as single valued in the dictionary (or by use of the SINGLE.VALUE keyword) but the corresponding data contains value marks, the marks are treated as normal data characters and the entire multivalued item, including the embedded value marks, will be output as a single element of the CSV output.

 

The TO option directs output to an operating system file by pathname. The AS option directs output to a named record in a QM directory file. If the destination item already exists, the user will be prompted to confirm whether it should be overwritten. The NO.QUERY option suppresses this prompt, overwriting the existing file. The APPENDING option causes the output to be appended to the output file if it already exists. The NO.QUERY and APPENDING options may not be used together. The optional ENCODING qualifier can be used to set or override the character encoding for the destination file.

 

Use of TO or AS implies use of HDR.SUP as the output is not paginated.

 

In normal usage, the page heading and record counts would probably need to be suppressed using the HDR.SUP and COUNT.SUP keywords. The COL.SUP keyword can be used to suppress column headings.

 

 

 

Examples

 

The command

LIST CUSTOMERS NAME TEL HDR.SUP COL.SUP COUNT.SUP CSV

would produce a display such as that below.

 

17463,Arkright Tool Hire,01726-48745

56221,"Smith,Price and Samuel", 01876-28414

 

Note how the customer name in the second line has been quoted because it contains a comma.

 

 

LIST STOCK PROD.NO QOH DESCR CSV TO C:\STOCK.CSV

 

This command constructs comma separated format report of the STOCK file into the C:\STOCK.CSV file.

 

 

LIST STOCK PROD.NO QOH DESCR CSV AS $HOLD STK.RPT

 

This command constructs comma separated format report of the STOCK file in a record named SRK.RPT in the $HOLD file.

 

 

A command such as

LIST SALES ITEM.NO CSV

where ITEM.NO is multivalued might produce a report containing

26832,176

,218

,398

 

Modifying the command to be

LIST SALES ITEM.NO SINGLE.VALUE CSV

would change the output to be

16832,176VM218VM398

 

 

See also: DELIMITER