# 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. - `html`: HTML. - `rtf`: Rich Text Format. - `docx`: Word docx format. - `xps`: Microsoft XPS page format. - `sql`: SQL insert statements. - `pdf`: PDF page format. - `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`. 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. ## 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`. ## 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: - `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`, `docx`, `html`, `pdf`, `rtf`, `tsv`, `xlsx`, `xls` and `xps`. ## Include/Exclude SQL The SQL statement used to collect the results is by default not exported. The SQL statement can be included in the output using the `include sql` specification for the following formats: , `docx`, `html`, `json`, `jsondataset`, `ndjson`, `pdf`, `rtf`, `xml`, `xlsx`, `xls`, `xps`. ## 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. # 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 ```