# 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 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. - `xls`: Excel xls 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`. 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` or `xls` 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`, `xlsx` and `xls`. ## 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` and `xls` formats put 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`, `xlsx` and `xls` 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: ```sql 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 ```