Previous Topic

Next Topic

Book Contents

Book Index

Exporting Data

In This Chapter

Export to Delimited File

Export to Fixed Format

Export to HTML

Export as Inserts

Export to Excel

Export Scripting

Export to XML

Multiple query results

Exporting LOBs

Fast Export

Exporting to Document Locator file

You can export the contents of a table / result of a query in a number of ways:

In the first three of these cases, AQT will run the query against the database, and export the data as it is retrieved.

When exporting from Data Display, Export will not access the database, instead it will just export the data that is displayed in the grid.

If you wish to export a very large table, it is better to use one of the first three methods, as it avoids the overhead of displaying the data in the grid.

Running the Export

Once you have selected one of the above options, you will be shown the Export Options dialog. There are a number of options about the format of the export file - these are described in the remainder of this help topic.

Click on Run to continue with the export, or Cancel to cancel the export.

Once your query has finished you will be asked whether you wish to view the file. If you do:

Export format

You can export your data in a number of formats.

There are a number of options that apply to all export formats. These are given below. In addition, each export format as a number of options specific to that format - click on the above links for information on these.

Options for all export formats

Option

Description

File

Select the file you want the data written to. By default this will be the name of the table you are exporting.

The filename can contain the keywords <schema> and <table>. For more information on the use of these, see Multiple query results.

Replace File if Exists

If this option is not specified, and the file exists, you will be prompted with a message about replacing the file. If you check this option, AQT will replace the file without prompting.

When exporting to Excel, this applies to the worksheet rather than the Excel file.

 

Append Data to File

When this is selected, AQT will append the data to the file.

For some export formats (such as HTML and XML) it is not sensible to specify this as the HTML / XML headers will be repeated, creating an invalid HTML/XML file.

 

Always create a new file

When this is selected, AQT will always export to a new file, and will not override an existing file. If the file specified in File already exists, AQT will create a file with a sequence number added to the name.

Example: if File is customer.csv, and this exists, AQT will write to customer_001.csv (the sequence number will be incremented until AQT finds a file that doesn't already exist).

When exporting to Excel, this applies to the worksheet name rather than the file name.

Prompt to display file

When this options is checked, the user will be prompted to view the export file after the export has completed.

Max Rows

The maximum number of rows to write to the export file. 0 for no limit.

Export data in Unicode format

When this is specified, the export file will be created as a unicode file. You may wish to de-elect this if your export file is going to be processed by an external script processor that does not recognize unicode files.

This option is ignored unless Unicode is enabled within AQT.

This setting will be ignored if you have specified Append Data to File and the file is non-empty. In this case the export will use the existing file encoding.

Replace x'00' characters with spaces

When this is selected, AQT will replace all null characters (x'00') with spaces in the export file.

Include Header with Column Names

Indicates whether column titles are to be included with the export. Clicking on Customize allows you to specify the column titles (they will default to the column names).

Does not apply for Insert and XML exports.

Show Nulls as

Specifies how Null values are to be written to the file. This will default to the sensible value for the export type (in most cases this is blank). You can change this to another value if required.

Export Nulls as single space for char cols

When this option is specified, for character columns Null values will be exported as a single space (eg. a string of length 1).

This is useful if the data is to be loaded into an Oracle Not Null column. For Oracle a zero-length string will be interpretted as Null so will fail to be loaded. When this option is specified, a single space is exported so can be loaded into such a column.

For non-character columns, Nulls will be exported as per Show Nulls as

Write Text before data (Advanced tab)

You can specify text that is to be written to the file before the query-data is written. This might (for instance) be a heading or a title line. It is also useful for separating query-data if you are appending many query-results to the same file.

Not relevant for Insert or XML exports.

Write Text after data (Advanced tab)

You can specify text that is to be written to the file after the query-data is written. This might (for instance) be a closing line.

Not relevant for Insert or XML exports.

Use date format as specified in Options > Display Format (Advanced tab)

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.

Maximum Column Size

If you are exporting character columns, the maximum size of a column exported is given by Options > Display Limits > Max Column Size.

Text Before/After

In your text you can also specify a number of keywords:

<cr>

Line-feed (this enables you to specify a multi-line header).

<blank>

Blank line.

<numrows>

The number of rows exported. Can only be specified in Text after.

<sql>

Your query SQL.

<sqlc>

Your query SQL compacted. The SQL has line-feeds and extraneous blanks removed.

Open / Save Export Options

Once you have set up an export, you can save the options with File > Save Export options. File > Open Export options will retreive an existing set of options.

The options only include the options specified on the Export Options window - it will not save information about the export query.

Fast Export

If you are doing a very large export, you may wish to use AQT's Fast Export module. This provides a faster export mechanism, however can be more work to set up and has a number of limitations.