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.