invantive documentation
Invantive Support ForumsLog In / Sign Up

Syntax

The result set from the last query can be exported to a file using the syntax:

local export results 
(as "<FILENAME-WITH-PATH>"|using filename column <COLUMN-NAME>) 
format FORMAT 
[split on <SPLIT-COLUMN-NAME>] 
[tablename "<TABLE-NAME-FOR-SQL>"] 
[columns <COLUMN1>[,<COLUMN2]*] 
[(exclude|include|include technical) headers]
[fieldseparator "<FIELD-SEPARATOR-TEXT"] 
[recordseparator "<RECORD-SEPARATOR-TEXT>"] 
[quotingcharacter "<QUOTING-TEXT>"] 
[escapedquotingcharacter "<ESCAPED-QUOTING-TEXT>"] 
[(include|exclude) sql] 
[limit <NUMBER> rows] 
[when contains at least <NUMBER> rows] 
[((remove|keep) field separator in content)|replace field separator in content by "<REPLACEMENT-TEXT>"] 
[((remove|keep) record separator in content)|replace record separator in content by "<REPLACEMENT-TEXT>"]

The options which follow format may be written in any order. Available from release 27.0; up to and including release 26.0 they had to be written in the order shown above and any other order was refused. An option written twice is refused, and so is a pair of options which contradict each other, such as include headers together with exclude headers.

A value between double quotes follows the rules of Quoting.

The most frequently used variant is:

local export results as "c:\temp\output.xlsx" format xlsx

which exports the results as an Excel workbook.

Output File Name

The name of the output file can either be hard-coded using FILENAME-WITH-PATH or taken from a column using

using filename column <COLUMN-NAME>

Output Format

The following output formats are supported:

  • xlsx: Excel xlsx file.
  • csv: comma-separated values.
  • tsv: tab-separated values.
  • txt: text.
  • sql: SQL insert statements.
  • html: HTML. Available from release 27.0.
  • rtf: Rich Text Format. Available from release 27.0.
  • json: JSON format.
  • ndjson: NDJSON format.
  • jsondataset: JSON data set format.
  • xml: XML.
  • sqlselect: SQL select query.
  • sqlcreatetable: SQL create table statement. The table name can be specified by specifying a value for TABLE-NAME-FOR-SQL.

Three names of page formats are accepted by the statement, but none of them can be produced by it. Each of them is refused with itgenqtl034, so no output file is written:

  • docx: Word docx format.
  • pdf: PDF page format.
  • xps: Microsoft XPS page format.

These three are produced by the results grid of Invantive Query Tool for Windows. Such a document is laid out from the grid as it is shown on screen, which a statement does not have: a statement has a result set and no grid. Up to and including release 26.0 html and rtf were refused the same way.

The csv, tsv and text output formats allow specification of separator and quoting behaviour:

  • fieldseparator: the characters to use as field separator.
  • recordseparator: the characters to use as record separator.
  • quotingcharacter: the character to use as quoting character for field and record separator in content.
  • escapedquotingcharacter: the character to use to escape the quoting character.

The field and record separators in content can also be removed or replaced by a value using the remove/keep specification.

HTML and RTF Output

The html output format writes a document which stands on its own: the results as a table, a style sheet which makes a table of many columns readable, and nothing which has to be fetched from elsewhere. The rtf output format writes the results as a table of an RTF document, in which the columns share the width of the text on a portrait A4 page. A table of many columns runs past the right margin rather than shrinking a column to the point where nothing in it can be read.

Both formats write the same document whether the export comes from a statement or from the results grid of Invantive Query Tool for Windows.

NDJSON Output

The ndjson output format writes one JSON object per row of the result set, each on a line of its own. There is no array around the objects and no comma between them. The lines are separated by a line feed, as the NDJSON specification prescribes. Every line holds every column of the result set, so a column without a value is written as null and all lines carry the same keys. That is the shape from release 27.0 onward; up to and including release 26.0 a line held only the columns with a value on that row, and the lines were separated by a carriage return plus a line feed.

A query with a for json clause yields a result set of one column holding a JSON document, and that document is exported as the value of that one column. Leave the for json clause out to obtain a line per row of data.

Split Result Sets across Files

Multiple output files can be generated, each with different part of the results, when split on is specified. A new output file is started on every change on the value of the column SPLIT-COLUMN-NAME.

The json, ndjson, jsondataset and xml formats put the rows belonging to a file in that file from release 27.0 onward; up to and including release 26.0 each of the files held the complete result set.

Columns

The list of columns to include can be specified using a list of column names specified after columns. The columns appear in the order they occur in the result set, whichever order they are listed in, and a name matching no column of the result set is refused, with itgendid085 for CSV and itgendid782 for the other formats.

The formats json, ndjson, jsondataset and xml write the listed columns from release 27.0 onward. Up to and including release 26.0 a column list was refused by json with itgendid180, by jsondataset with itgendid090 and by xml with itgendid088, while ndjson ignored it and exported every column. On an xlsx export a name matching no column of the result set is refused from release 27.0 onward as well; up to and including release 26.0 such a name was ignored and left the workbook one column short of what was asked for. The html and rtf formats write the listed columns from release 27.0, the release in which they became available.

Headers

The headers can be configured using one of the following three options:

  • include technical headers: headers with the column names are included (default).
  • exclude headers: no headers are included.
  • include headers: headers with user-friendly labels are included. Headers are only included for the following formats: csv, tsv, txt, html, rtf and xlsx.

Include/Exclude SQL

The SQL statement used to collect the results is by default not exported. The formats json, jsondataset and xml include it as part of the audit information when include sql is specified, and leave it out when the specification is absent or exclude sql is specified. That is the behaviour from release 27.0 onward; up to and including release 26.0 the statement was part of such an export whatever was specified.

The xlsx format puts the statement on a worksheet of its own when include sql is specified, next to the worksheets holding the rows. That worksheet names the worksheet the rows went to and then the statement which collected them, so an export of several result sets stays readable. This holds from release 27.0 onward; up to and including release 26.0 the specification was accepted on a workbook and had no effect.

The html and rtf formats put the statement below the table of results when include sql is specified, under a heading of its own.

The remaining formats have no room for the statement, so specifying include sql on one of them is refused. Each refusal names the format:

  • ndjson: refused with itgendid429, since the output is a bare stream of one JSON object per row of data without an envelope around it.
  • csv, tsv and txt: refused with itgendid439, since a file of separated values is rows of values and a line carrying the statement instead would break every reader of the file.
  • sql: refused with itgendid440, sqlselect with itgendid441 and sqlcreatetable with itgendid442, since a file of SQL is meant to be replayed. Putting the statement in a comment does not survive a statement of several lines: every line after the first would run as SQL.

The refusals hold from release 27.0 onward. Up to and including release 26.0 the specification was accepted on each of these formats and had no effect on the output.

Rows

The maximum number of rows to include in the export can be specified using a limit specification. The minimum number of rows the export must contain for it to create a file can be specified using a when contains at least specification.

The json, ndjson, jsondataset, xml and xlsx formats apply the limit specification from release 27.0 onward; up to and including release 26.0 they exported every row of the result set. The html and rtf formats apply it from release 27.0, the release in which they became available.

Examples

To export the results of a query to an Excel file when there is at least one row:

select *
from   dual@DataDictionary

local define OUT_PATH "c:\temp"

local define MY_FILE_NAME "my-file.xlsx"

local export results as "${OUT_PATH}${system:directoryseparator}${MY_FILE_NAME}" format xlsx include headers when contains at least 1 rows