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