Export Results

The results 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 <COLUMN-NAME>]

[tablename <TABLE-NAME-FOR-SQL>]

[columns <COLUMN1>[,<COLUMN2]*]

[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

as "<FILENAME-WITH-PATH>"

or taken from a column using

using filename column <COLUMN-NAME>

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 split on column.

Output Formats

The following export formats are supported:

•xlsx: Excel xlsx file.

•csv: comma-separated values.

•tsv: tab-separated values.

•txt: text.

•html: HTML.

•rtf: Rich Text Format.

•docx: Word docx format.

•xps: Microsoft XPS page format.

•sql: SQL statements.

•pdf: PDF page format.

•json: JSON 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 'tablename'.

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

•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.

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.

Columns

The list of columns to include can be specified using a list of column names specified after 'columns'.

Headers

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

•exclude headers: no headers are included.

•include headers: headers with user-friendly labels are included.

•include technical headers: headers with the column names are included.