Exporting Data

In This Chapter

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

from the Database Explorer, Tools > Export Data (F7) after selecting a table or view.
from the Run SQL window, Export To > File (F7)
from the Query Builder window, Query > Export Data (F7)
from the Data Display window, File > Export Results (F7)

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.

if you are overwriting a file, and haven't specified with replace or append, you will be prompted about whether you wish to do so.
while your query is running, you will be taken back to the window from which initiated the export. You will be given regular progress reports on the number of rows written to your file. If you wish to terminate the query, click on Abort.

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

for HTML export, you will be shown your exported data in your default web browser. For XML export, your default XML browser will be used (this may be Internet Explorer).
for other export types, you will be shown a sample of your file using an AQT window. This may not show you your entire file. Note that if you have specified append, your data will be at the bottom of the file and may not be initially visible.
you can copy data from the AQT window by selecting the data then hitting Ctrl+C. Alternatively, clicking on Copy All copies all the data.
if you have written the data in fixed format you need to view the data using a non-proportional font (eg. one where all characters have the same width). Selecting Font > Non Proportional makes it much easier to view fixed-format data.

Export format

You can export your data in a number of formats.

delimited file, such as a csv file (comma separated variables) or some other delimiter
fixed format This is useful for loading into DB2 z/OS
HTML. This publishes your data to a web page.
Insert Statements. This exports your data as a number of insert statements. This is useful for transferring the data to another table, or having a simple backup of the table.
Excel. This exports your data to an Excel Worksheet
XML. AQT will export the data in dbunit XMLDataset and FlatXMLDataset 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.
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 deselect this if your export file is going to be processed by an external script processor that does not recognise 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.
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.
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.