KnowledgeBase 00029: Handling Comma Separated Variable Data

This article was originally published as a Tip of the Week.

Introduction

QM provides QMBasic statements and a query processor option that make reading and writing comma separated variable data very easy.

CSV Format

There are several variations on comma separated variable format but the implementation defined by RFC4180 is probably the most widely used. This requires that any items containing double quotes or commas are enclosed in double quotes with embedded double quotes replaced by two adjacent double quotes. This is the default behaviour of QM when creating comma separated data.

QM also supports a variation where all non-null values are enclosed in double quotes except for numeric items that do not contain a comma, again replacing embedded double quotes with two adjacent double quotes.

A third variation encloses all values in double quotes with the same handling of embedded double quotes.

Writing CSV Data in QMBasic

The WRITECSV statement writes data in CSV format to a file previously opened with OPENSEQ.

  WRITECSV var1, var2, var3 TO fvar

There may be any number of variables forming the data to be written and each field in a variable is treated as a separate element of the CSV output thus allowing an entire CSV item to be constructed from a statement such as

  WRITECSV var TO fvar

where var is a dynamic array. The CSV data is written in the style set by previous use of the CSV.MODE statement or, if this has not been used, the default RFC4180 style.

Reading CSV Data in QMBasic

The READCSV statement reads CSV format data from a file previously opened with OPENSEQ.

  READCSV FROM fvar TO var1, var2, var3

If there are fewer data items in the line of text than the number of variables supplied, the remaining variables will be set to null strings. If the line of text has more data items than the number of variables supplied, the excess data is ignored. Parsing of the data is subject to the rules selected by the current CSV.MODE setting.

Other QMBasic CSV Operations

There are functions provided to apply the CSV mode rules when converting a string to CSV format, FORMCSV(), and when parsing a CSV item, CSVDQ().

The PRINTCSV statement is similar to WRITTECSV but directs the output to a print unit.

The MATREADCSV statement reads CSV format data, placing each item into successive elements of a dimensioned array.

The INPUTCSV statement is similar to READCSV but reads CSV format data from the keyboard or from data queued with the DATA statement.

The DPARSE.CSV statement provides a more generalised way to parse a CSV item, placing each component into a simple variable, a matrix element or a field, value or subvalue of a dynamic array.

CSV in the Query Processor

The CSV option in a query processor command specifies that the report should be produced in CSV format.

  CSV {mode}

where the mode selects the rules to be applied and defaults to the RFC4180 format.

None.