Exporting Data

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 Export to continue with the export, or Cancel to cancel the export.

• 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

• Grid. The Data will be exported as a Saved Grid. This can be later opened in AQT.

• XML. AQT will export the data in dbunit XMLDataset and FlatXMLDataset formats.

• JSON. AQT will export the data in JSON format.

Options for all export types

Filename Keywords

The filename can contain keywords a number of keywords:

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.

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 (Large) 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.

Advanced Options

Text Before/After

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

Export to Delimited File

Note that CSV stands for comma-separated variables and refers to a Delimited File where delimiter character is a comma.

Export to Fixed Format

This export format is useful for use with the DB2 z/OS load utility; this requires the data to be in fixed format, eg. each column has a fixed width.

Options that can be specified with this format are:

Limitations of fixed-format export

• Numeric variables are not padded on left with zeros (which is required for import into the DB2 z/OS load utility).

• AQT currently doesn't generate the control statements for the DB2 z/OS load utility.

• A maximum of 10,000 bytes of a column will be exported.

Export to HTML

Exporting to HTML can be a useful way of displaying your data:

• it can be a simple way of sending the result of a query to another member of your workgroup, especially if they do not have AQT or other data formatting tool on their PC

• it provides an alternate mechanism for printing your data

HTML Options

These options are on the HTML tab of the Export Options window.

Most of these options are related to the formatting of the HTML file generated by AQT. The options should be familiar to users familiar with HTML.

AQT uses styles for formatting the data in the cells of the HTML table. You have the choice of where AQT writes the style information:

• inline. AQT will write the style information as part of the export file

• write to css file. When this option is selected, AQT will write the style information to a css (cascading style sheet) file. The export file will then reference this css file

• use existing css file. In this case, AQT will not write a css file; instead it will reference one you have supplied. This option would be useful if you wish to apply your own styles to the file AQT generates

It is recommended that the css file resides in the same directory as the export file. If it is not, AQT will use a fully-qualifed name for the css file in the HTML file - this can cause problems with some web browsers.

The AQT export file uses four styles:

• background. This is the area outside the table containing the exported data. It will apply to the Title plus the Write Text Before / After text.

• heading. This applies to the heading row of the HTML table containing the Column Names

odd / even rows. AQT uses separate styles for odd and even rows in the HTML table. This enables you to use a two-color display for the results.

LOBs

If your data has LOBs (such as images) AQT can export these to separate files and create hyperlinks to them. See Exporting LOBs for more on this.

Hyperlinks

If AQT sees that a column value starts with http:// or https:// it will assume that this is a url and will create a hyperlink to that address.

Export as Inserts

This option will export the data as a series of Insert statements which can then be used for loading the data into another table. This provides a very simple mechanism for transferring data between tables, especially if the tables exist on different databases or in different database types.

LOBs

If your data has LOBs, AQT can export these in a way that enables them to be loaded into another table. See Exporting LOBs for more on this; this has some important information if you are planning the load LOBs into Oracle.

Export to Excel

Type of Excel Interface

AQT offers three different methods for exporting to Excel:

Both Normal and Bulk export work by using a COM interface to Excel. Excel is invoked on your PC and commands are passed to it. There are sometimes problems with this interface - this can cause the export to fail or Excel being left in an unresponsive state.

Native Export writes directly to Excel files, so Excel is not invoked during the export. We recommend that this option is used.

Options

Options you can specify for Export to Excel are:

After it has finished writing the data, AQT will automatically save the workbook and leave it open for you to view.

• AQT will set the format of the cells in the worksheet according to the data type of the columns. Date, time and timestamp columns will be displayed in Excel with the same date format as defined to AQT (see Options > Display Format).

• The decimal part of timestamp values will be truncated. Excel timestamps do not have a decimal component.

• If you use dates prior to 1 Mar 1900, you may notice the dates may be out by one day. This is a bug with Excel, which is described here.

Do not use Excel while this is running....

When the export starts, AQT will start Excel and open the Excel file. Do not switch to Excel and edit this worksheet while the export is running!

Editing the worksheet will lock the worksheet, preventing AQT from writing to it. The export will fail with code 0x800AC472.

LOBs

If your data has LOBs (such as images or documents) AQT can export these to separate files and create hyperlinks to them from your Excel worksheet. See Exporting LOBs for more on this.

Hyperlinks

If AQT sees that a column value starts with http:// or https:// it will assume that this is a url and will create a hyperlink to that address.

Excel Crash when using exporting large columns

Some users have experienced crashes of Excel when exporting very large column columns (several thousand bytes). This problem only happens when Bulk Exporting has been selected. To prevent this problem from happening, de-select Use bulk exporting of data.

Exporting to xlsx Files and XML Worksheets

AQT supports both these types of Excel files. When you select an Excel file to save, enter a file name with an extension of:

• xlsx (for Excel 2007 / 2010 / 2013 / 2016 files)

• xml (XML Worksheet)

If you are creating a new file, the Windows "Save as" dialog will not add this file extension to your file name, even if you have selected this in the "Save as Type" dropdown. You must manually include this file extension in your file name.

Native Export can export to Excel files in a number of different formats. This is specified in the Excel File Format dropdown.

Row Limit

• Exporting to an xls file has a limit of 65536 rows. If you exceed this you will get error "Exception from HRESULT: 0x800A03EC".

• Exporting to an xlsx file has a limit of 1048575 rows. If you attempt to export more rows than this you will get error "Specified argument was out of the range of valid values"

You can avoid Row Limit problems by using Exporting to Multiple Files.

Example of an Excel Macro

The following is an example of macro that can be run after the export has completed. This will sum a column (column 1 in this example) and format the worksheet.

Sub format_wb()
Dim xs As Worksheet
Dim r As Range
Dim lastcell As Range
Set xs = ActiveSheet

'--This will Sum a particular column
Dim column As Integer
column = 1
Set lastcell = xs.Cells(xs.Rows.Count, column).End(xlUp)
Set r = xs.Range(Cells(2, column), lastcell)
lastcell.Offset(1, 0) = WorksheetFunction.Sum(r)

'--Set the Font for all the Cells
Set r = xs.Range(xs.Cells(1, 1), xs.Cells.SpecialCells(xlCellTypeLastCell))
r.Font.Name = "Arial"
r.Font.Size = 8

' Set a background color for the Header Row and set font to Bold
Set r = xs.Range(xs.Cells(1, 1), xs.Cells(1, xs.Columns.Count).End(xlToLeft))
r.Font.Bold = True
r.Interior.ColorIndex = 40

End Sub

Export to Grid file

This allows you to export your data as a Saved Grid file. This file can later be displayed by AQT as a normal data display grid.

This is useful if you are running queries in unattended Batch Mode, then wish view the results later in AQT.

Export Directory

It is recommended that you export your data to the default export directory - this is sub-directory Exports from the Queries directory. Then, when you display the Query Explorer, you can see and your exports and display the data in the Data Display window.

Grid Caption and Parameters

When you run a query, you can specify the caption for the grid with an --aqt caption command. Your query can also prompt the user for parameter values with an --aqt queryparm command.

Export to XML

Notes:

• binary data will be encoded using Base64 encoding (in accordance with the dbunit specifications). This includes DB2 columns defined as CHARACTER FOR BIT DATA. This is done even if the binary columns contain ascii data.

• BLOBs can be either written to external files or written inline. This is governed by Write BLOBs to Files export option. If BLOBs are written inline, they are encoded using Base64 encoding. This can be a time-consuming operation if you are exporting a large number of large BLOBs. Note that this option applied only to BLOBs and not CLOBs or other large objects.

Export Scripting

You have the ability to Script the Export function. This allows you to export data automatically, without having to specify the Export Options. Instead, all the required information is specified in a scripting control statement. See the section on scripting for more information on this, and for the export script statement in particular.

This scripting function is also used for running AQT in unattended (batch) mode.

To make it easy to set up scripts, use File > View Export script (or Ctrl+G) from the Export Options window. This will generate a scripting control statement for the Export as per the options you have specified. You can run this script control statement from either the Run SQL window, or in a batch script.

Example:

--aqt export,file="C:\Apps\Exports\order_line.csv",type=csv,filemode=replace,nulls=blank,prompt=yes,header=yes

Select * from Order_Line;

Multiple query results

In AQT you can run SQL that produces multiple result-sets. This might happen if:

1. You are running a script containing multiple Select statements (either in online-AQT or batch-mode AQT).

2. You are running a stored procedure or a block of code that produces more than one result-set.

When you running a script (as in case 1) AQT not prompt you with the Export Data dialog when you click on Export Data/F7, instead it will prompt you for this when it processes the first result-set.

Creating a Single File

By default, AQT will write all data to a single file. After the first result-set has been written to the file, AQT will switch to Append mode - subsequent result sets are then appended to the file.

If you are doing this, you may wish to code a value for Write Text after data so that the sets of data are separated by a blank line or other delimiter.

Creating Multiple Files

It is possible to have your results-sets go to different files. You can do this by coding the keywords <schema> or <table> in your file name. AQT will replace these with the schema and table name of the table being exported, then will use this file name for the exports. AQT will only switch to append mode if it detects that the same file is used in subsequent result-sets.

If you are doing Export as Inserts, you can also include the <schema> and <table> parameters in the Table Name for Insert statements. AQT will replace these parameters in this table name before including it in the insert statement.

Exporting LOBs

If your table contains LOBs, AQT has options for exporting these in a useful way. These options are specified on the Advanced tab.

This is available for Delimited Files, Inserts, Excel and XML export formats.

When Write LOBs to files is checked, AQT will export the LOB values as files, and embed links in the export file to these BLOB files.

• for HTML and Excel exports, the export file will have hyperlinks to the LOB files.

• for XML export, only BLOBs (and not CLOBs) will be exported (the XML specification we follow only allows BLOBs to be exported to file). When this is specified, the attribute values will contain a reference to the LOB file. If Write BLOBs to files is not checked, the LOB values are encoded in Base64 and included in the export file. For large BLOBs (many megabytes) this can be slow, and can result in a very large export file.

• for Export as Insert statements, the LOB files are referenced as parameters. This is the format used by AQT for loading LOB values; the export file can therefore be used to load the LOBs into another table. However only AQT can be used for loading the LOBs like this; the export file cannot be processed by another script processor.

File extension

In order to hyperlink to a LOB file, you need to specify the file extension for the LOB files. For instance, if your LOBs were images, you would specify a file extension of jpg.

LOB file management

LOBs files are handled differently for Export as for a normal table display. For a normal table display, the LOB files are held in a temporary directory and automatically deleted when the display window is closed.

For Export, you do not want the LOB files deleted but may wish to retain them for a period of time. As a result, AQT will not delete the LOB files after the export has completed, or when AQT is closed. The LOB files will remain permanently on your disk until manually deleted by yourself, or overwritten by another export.

To give you more control of organising and managing the LOB files, Export allows you to specify the Sub Directory and the File Prefix.

• the Sub Directory is a subdirectory from the directory used for the export file. By default, the Sub Directory is files, however you may change this to a more meaningful name.

• the File Prefix gives the first part of the name of LOB file. By default this is the name of the table being exported. In this way, AQT will generate fairly unique names for the LOB files, reducing the chance that an export may override the LOB files from a different export.

• the File Prefix may contain either <schema> and/or <table>. The table schema and name will be substituted for these.

Delimited-File Export

For a delimited-file export, the Write LOBs to files option is only meaningful when the file is going to be used as input to the Oracle SQL Loader. In this case the LOB file name is included in the export file. When the control file for the SQL Loader is coded, the LOBFILE keyword is used.

For delimited-file export, you can also specify Use qualified file names. This specifies whether qualified or unqualified file names are to be used in the export file. For other types of export this option is ignored (qualified file names will always be used).

Exporting to Multiple files

This feature allows your exported data to be spread over multiple files. This is useful when exporting a very large amount of data, and you don't want the data for any one file to be too large.

On the Advanced tab, check the Write to multiple files option.

Notes

• Write to multiple files must be checked before any of the features described in this topic will be active.

• When this option is used, it is recommended that Replace if File Exists option is specified. You may get confusing results if either Append or Create New File options are specified.

• Each file created will have the header line (with column names) plus (if specified) the Before Data and After Data text.

Maximum Lines per File

This is the simplest way of spreading the export over multiple files.

AQT will write a maximum of Max rows per file rows to a file, at which point it will switch to a new file.

• If Max rows per file is zero, AQT will write to a single file and this feature will not be active.

• The new files will have a sequence number added to the name. So, if your export file is customers.csv, AQT will export to customers.csv, customers_0001.csv, customers.0002.csv etc.

• If you are exporting to an Excel file, AQT will create a new sheet rather than a new Excel file. However this behavior can be overridden - see the section Using <n>.

Splitting Data based on a column value

You may wish to spread your data over multiple files based on the value of a column.

To do this you need to change the name of your export file to include the column name surrounded by diamond brackets.

Examples:

customers_<customer_type>.csv

customers_<3>.csv

customers_<n>.csv

• You can either specify a column by either column name or column number.

  If the column name is not found or the column number is invalid, you will get unknown substituted.

• Your query MUST return data in the order of the column you are splitting the data by. In the above example you MUST have an Order By Customer_Type clause in your SQL.

  If this is not done, and AQT tries to write to a file it has written to previously, the export will stop with an error.

• You can include multiple columns in the file name - example customer_<customer_region>_<customer_type>.csv. Again, make sure your order-by clause has both these columns.

• This option is not suitable for columns whose values contain characters which are invalid in file names (such as \ / : * ? " < > |).

Using <n>

The specification <n> in your file name is used to specify the file sequence number. You would use this if you wish to switch to a new line base on Maximum lines per file, but with greater control over the file name.

• when you specify this, you need to have a non-zero value of Max rows per file

• for an Excel file, if you specify this in the Excel file name, AQT will create a new file, rather than a new sheet, whenever the Max rows per file limit is reached.

Notes

• Write to multiple files must be checked before any of the features described in this topic will be active.

• When this option is used, it is recommended that Replace if File Exists option is specified. You may get confusing results if either Append or Create New File options are specified.

• Each file created will have the header line (with column names) plus (if specified) the Before Data and After Data text.

Maximum Lines per File

This is the simplest way of spreading the export over multiple files.

AQT will write a maximum of Max rows per file rows to a file, at which point it will switch to a new file.

• If Max rows per file is zero, AQT will write to a single file and this feature will not be active.

• The new files will have a sequence number added to the name. So, if your export file is customers.csv, AQT will export to customers.csv, customers_0001.csv, customers.0002.csv etc.

• If you are exporting to an Excel file, AQT will create a new sheet rather than a new Excel file. However this behavior can be overridden - see the section Using <n>.

Splitting Data based on a column value

You may wish to spread your data over multiple files based on the value of a column.

To do this you need to change the name of your export file to include the column name surrounded by diamond brackets.

Examples:

customers_<customer_type>.csv

customers_<3>.csv

customers_<n>.csv

• You can either specify a column by either column name or column number.

  If the column name is not found or the column number is invalid, you will get unknown substituted.

• Your query MUST return data in the order of the column you are splitting the data by. In the above example you MUST have an Order By Customer_Type clause in your SQL.

  If this is not done, and AQT tries to write to a file it has written to previously, the export will stop with an error.

• You can include multiple columns in the file name - example customer_<customer_region>_<customer_type>.csv. Again, make sure your order-by clause has both these columns.

• This option is not suitable for columns whose values contain characters which are invalid in file names (such as \ / : * ? " < > |).

Using <n>

The specification <n> in your file name is used to specify the file sequence number. You would use this if you wish to switch to a new line base on Maximum lines per file, but with greater control over the file name.

• when you specify this, you need to have a non-zero value of Max rows per file

• for an Excel file, if you specify this in the Excel file name, AQT will create a new file, rather than a new sheet, whenever the Max rows per file limit is reached.

Fast Export

If you need to export a large amount of data, AQT has a fast but basic export tool. How to use this is described in this section.

A lot of the time taken to export your data is the time taken to process all the columns in your table. This is particularly true if your table contains a lot of columns. You can speed the export considerably by generating a single column from the table. For instance, instead of doing a csv export of:

select col1, col2, col3 from table

you run a query such as:

select '~' || col1 || '~,' || col2 || ',~' || col3 || '~' from table

here (for the sake of clarity), the string delimiter is a tilde ~. col1 and col3 are character, col2 is numeric. This query generates a single column from the database; the data in this column is a csv export of the data in the table. You then do a fixed-format export of this query.

Secondly, AQT has a fast export module designed for use with queries such as this. This is a pared-down and optimized export module. This is described later.

Generating an export Query

AQT can generate an SQL statement that creates a csv-export from your table. This is done as follows:

• select a table in the Database Explorer

• click on Export Data

• click on File > Generate Fast Export script

• copy the resultant text to the Run SQL window

• close the Export dialog by clicking on Cancel

The generated SQL statement will use the column delimiter and string delimiter as specified in the Export options.

All the formatting of the export data is done within this SQL statement. You will need to manually change the SQL statement if you have any particular data formatting requirements. For instance, if your data is likely to contain either line-feed characters or the string-delimiter character, you may wish to amend the SQL to remove these characters.

Viewing the csv file

If you are generating a csv file, and wish to see whether it is formatted correctly, the Data Loader window has a tool that allows you to do this:

• from the Database Explorer window, click on Load

• select a Load Data From > File

• enter your file name

• ensure that Column Delimiter and String Delimiter are correct

• click on View

• click on Abort once AQT has processed the number of rows you wish to view.

This will split the file into separate columns based on your delimiters. This makes it very easy to view the contents of a csv file, and to check that the data is being generated correctly.

Fast Export module

When exporting your data, you can specify that AQT is to use a Fast Export module. This is done by coding fastmode=yes on the --aqt export scripting statement. The Fast Export module is a highly trimmed down and optimized module for these types of exports. It is not to be specified for normal exports:

• it only exports a single column

• it does not export Unicode data

• it only does a fixed-format export (which makes sense for a single column)

• cannot export a row longer than 20K bytes of data

Exporting to Document Locator file

If you have enabled the interface to Document Locator, you will be able to export to a Document Locator file.

The following options on the Document Locator tab give some options for the export.

Notes:

• Create New File mode is not supported.

Exporting Multiple Tables

You can export multiple tables in a single operation.

To do this, from the Database Explorer window, click Tools > Export all tables in schema (Ctrl + F7). The Tables to Export tab will show you all tables in your schema. Select the tables you wish to export.

Export Files

In most cases you would want each table export to go to a different file. You can do this by including <schema> and/or <table> in the filename.

In the above example: if you specify an export filename of C:\Apps\AQT\Exports\<schema>_<table>.csv then the following files will be created:

C:\Apps\AQT\Exports\dbo_AdventureWorksDWBuildVersion.csv

C:\Apps\AQT\Exports\dbo_DatabaseLog.csv

C:\Apps\AQT\Exports\dbo_DimAccount.csv

etc

When exporting to Excel, the Worksheet can also contain the the <schema> and <table> keywords.

Only save Excel file once all exports have completed

This option will appear when you are exporting to Excel.

When you have a large Excel file, saving the file can be a time-consuming operation. The export will run significantly faster if AQT only saves the file once all exports have been completed.

When this option is not checked, AQT will save the file every time a table has been exported.

Reset

Once you have completed your export, you can click on the Reset button to return the selection to the way it was before you ran the export. This is useful if you wish to rerun your export with different options.

Export History

AQT maintains a History of exports. This can be useful for keeping track of export files, particularly those created by batch / unattended processes.

• in Options > History you can specify whether AQT will maintain the Export History. By default this will be done.

• when you run an export, you can select whether the export will be logged in the History. This is done on the Export dialog, Advanced > Misc Options.

• when running an export script, you can specify whether it is logged in the History with hist=y or hist=n.

• the Export History file is file export_history.txt in the History File directory.

• you can delete entries from the History with Edit > Delete selected entries, or File > Purge History file.