Scripting - Export Function

The Export function specifies that the SQL statements which follows are to have their output exported to a file.

The Export function takes the following parameters, most of which correspond to parameters specified on the Export Data window.

It is recommended that you generate an Export function by (on the Export Data window) clicking on File > View Export options.

Note in earlier releases of AQT, the Export function was called Output. Either Export or Output can be used.

Parameter	Description	Notes
File*	The name of the output file	It is recommended that a fully qualified name is used. If the name is not fully qualified, the directory used will be your query directory if you are running AQT online the current-working-directory you are running the script in batch mode. The current-working-directory is generally the directory the .bat file is located in. For Excel, it is strongly recommended that a fully-qualified file name is used. If not, Excel may place the file in a default location that is not “known” to AQT. If you refer to this file a second time, AQT will not find it and will create a new one. You can include the keywords <schema>* or <table> in your file name. These will be replaced with the schema and name of the table you are exporting.
Type*	The type / format of the output file	Valid values are: fixed, csv, tab, html, insert, excel, delim, xml. Delim is used if you have specified an "other delimiter". In this case the delimiter is specified with a delim clause.
Delim*	The character used to delimit values.	This is only used if type=delim is specified. If your delimiter character is a single-quote, double-quote, comma or semicolon, use the keywords squote, dquote, comma, semi respectively. If type=delim is specified and delim is not specified, a comma will be used.
Filemode		By default, AQT will not overwrite an existing file. If your file already exists, you must specify a Filemode of either Append (add the data to the existing file) or Replace (replace the output file). Specify New for AQT to always create a new file.
Prompt		Specifies (Yes or No) whether you want to be prompted with the message "Do you wish to view a sample of the file?”. You are prompted for this at the end of the script (after all the data has been written) so only works on the last file written, plus the last Excel file written. The default is No. Do not code this as Yes if you are running AQT in unattended batch mode.
Header		Specifies (Yes or No) whether the output file is to include a header line with the column names Defaults to values last specified for it.
Maxrows	The maximum number of rows to output	This cannot be specified if you are running the evaluation version of AQT. Defaults to the value last specified for it.
Nulls	How nulls are to be displayed.	Options are: Null, Blank, (null) Defaults to Null (for Type of insert or xml), Blank (for Type of html or Excel), otherwise (null). If specifying this parameter, you should code it after you have coded the Type.
Sdelim	The character used to enclose string values.	If your delimiter character is a single-quote, double-quote, comma or semicolon, use the keywords squote, dquote, comma, semi respectively. Specify sdelim=none for no string delimiter.
remdel	Whether to remove the delimiter character from strings	When this is specified, the sdelim character will be removed from string values. This can prevent a problem should this value appear in the text. By default this option is set. Code remdel=no to switch off.
remlf	Whether to remove linefeed characters from strings	When this is specified, linefeeds will be removed from string values. Linefeeds can cause problems when appearing in a delimited file. By default this option is set. Code remlf=no to switch off.
Insert_Tab	Specifies the Table Name to be used in the Insert statements.	This option is only used when Type is Insert. You can include the keywords <schema> or <table> in your insert table name. These will be replaced with the schema and name of the table you are exporting.
Sheet	Specifies the name of the Excel Worksheet to be written to	This is used for Excel only. If you code Sheet it is recommended that you also code Filemode, which specifies what to do if the Worksheet already exists. This parameter is optional – if not coded a new Worksheet is always created (and Filemode is ignored). AQT supports appending data to worksheets (to do this, set Filemode=append). You can include the keywords <schema> or <table> in your Sheet name. These will be replaced with the schema and name of the table you are exporting.
Stext	Specifies text that is to be written before the query results.	Within this text you can code keywords <cr> (newline, this allows you to specify multi-line text) or <blank> (blank character, which would not normally be accepted as a valid parameter). Example: stext=Customer Report<cr>Run on 23Feb2003. You cannot have a comma in this text.
Etext	Specifies text that is to be written after the query results.
Startrow, Startcol	Specify the position in the Worksheet where AQT will start to write the results.	If not specified: startcol is 1 and startrow is 1 (for Replace mode) or the row past the current data (for Append mode).
genctl	Whether to generate a control file	Specify Yes or No. This option is only relevant for Fixed File format.
ctlfile	Name of control file	This option is only relevant for Fixed File format and when genctl is yes.
incblank	Specifies whether a blank is to be written between columns in the output file	Specify Yes or No. This option is only relevant for Fixed File format.
userdate	Formats date columns as specified in Options > Display Format	If this is not specified, date columns will be formatted in yyyy-mm-dd format, which is the format most databases require for data being loaded. If this is specified as Y, date columns will be formated as per Options > Display Format > Date Format
todate	Whether the TODATE function is added to date values.	Specify Yes or No. Oracle only. This option is only used when Type is Insert.
maxlen	Maximum length of a line in the output file.	This option is only used when Type is Insert.
isrtcols	Whether a column list is to be included in the generated insert statements	This option is only used when Type is Insert. The column names are given in colnames. If colnames is not given, the column names are taken from the table/query being exported.
colnames	The column names to be used in the generated insert statements.	Must be comma delimited and enclosed in brackets. Example: (columna, columnb, columnc). This option is only used when Type is Insert.
insert_delim	The delimiter used to delimit the Insert statements.	The default is the semicolon; other options are go or / This option is only used when Type is Insert.
uniwrite	Whether the output file is to be in Unicode format.	Specify Yes or No. By default, the file will be written in Unicode format if Unicode is enabled. This option overrrides this setting. This option is only used when Type is Insert.
batchonly	When set to YES, the export statement will only be active in batch mode.	This is useful if you run scripts both online and in batch. When this is specified, the export statement is ignored in online AQT; results from queries will go to display grids.
xlpwd	The worksheet password for exporting to Excel	You can specify this as either a clear-text password, or as an encrypted password (see Generate Encrypted Password). If using a clear-text password, this cannot start with either *, #, @ or $ (AQT uses these to recognize encrypted passwords).
batchonly	When set to YES the --aqt export statement will be ignored when run in interactive AQT.	This is useful when scripts are run in both batch and interactive AQT. When run in batch, query results will go to the specified export file. When run in interactive AQT, the --export statement will be ignored, so the query results will go to a Data Display window.
xlnative	Specifies whether the Native export to Excel is used
xlfiletype	Specifies the Excel File Format	Only used when xlnative=yes is specified. Specify one of: 2003, 2007, 2010
replacex00	Replace x'00' characters with spaces

Parameters marked * are mandatory

Examples:

--aqt export,file=customer_details.csv,type=csv,filemode=replace,prompt=no

select * from Customer_Details;

--aqt export,file=output.xls,type=excel,nulls=blank,sheet=Customer_Contact,filemode=replace

Select * from Customer_Contact;

--aqt export,file=order_details.csv,type=csv,filemode=replace,prompt=no

Select * from Order_Details;

Note on Filemode=New

When this is specified, AQT will can write to a file different to the one specified in File. AQT will add a sequence number to the end of file name so that it is a unique file.

One must be aware of this if you use the file in a subsequent step. For instance, the following would be very dangerous:

--aqt export,file="C:\AQT\customer_details.sql",type=insert,filemode=new

Select * from Customer_Details;

--aqt include,file="C:\AQT\customer_details.sql"

The export may write to a file other than "C:\AQT\customer_details.sql". This means that the include statement may not pick up the file that was created during the export.