Select load source
Use the left pane of the Source Tab to select the type of data to be loaded, then specify the source on the right.
Four types of data source can be specified:
File
|
data is in a structured file (such as .csv)
|
|
Excel worksheet
|
data is in a Microsoft Excel file
|
You do not need Excel installed on your PC to load from this
|
Table
|
data is in another table
|
The table can be on a different database, but you must be connected to it in AQT
|
Query
|
data is in a query
|
The query can be run on a different database, but you must be connected to in AQT.
|
File
Excel worksheet
- specify the name of the file by using the Browse button or the drop-down list of recent files.
- once you have selected a file, Excel will be activated and AQT will fetch the list of worksheets in this Excel file. These will be displayed in the worksheet dropdown list. Select the worksheet containing your source data.
- once you have selected a worksheet, the active cells in the worksheet will be displayed in the worksheet Active Area boxes. You can change these values to change the cells that you want loaded into your table.
- clicking on View will show you the worksheet data. This is described in the previous section. Note that this will only show you the data as per the Worksheet Active Area.
- First Line has Column Names specifies that the first line in your file has column names, and not data.
- sometimes Excel gets into a strange state: it will be running but not visible. Use the Activate Excel button to make Excel visible.
- use Open Worksheet as Readonly to open the worksheet in read-only mode. This useful to prevent the load failing because the Worksheet has been opened by other users.
- Format Date values as yyyy-mm-dd. By default, when AQT detects that a value is a Date, it will format it in yyyy-mm-dd format. If this is not done, the values can come through in a format not suitable for loading into a table.
This option can cause a problem in some circumstances. AQT can sometimes think that a non-date value is a date, and will therefore reformat it when this is not wanted. To prevent this from happening, de-select this option.
Use Native Excel Interface
By default, this option is not selected. In this case, AQT will read the Excel file by:
- starting MS Excel
- passing commands to it
This method is slow and can sometimes be unreliable if Excel gets into an odd state.
When this option is selected, AQT will use a Native interface to Excel. This method is fast and reliable.
- AQT reads the file directly
- you do not need Excel installed on your PC. In other words, this option gives you the ability to load data from Excel files even if you do not have Excel installed.
Use Raw Values
With Excel, there are two choices of how the data values are read:
- Raw. The underlying value of the cell will be used.
- Formatted. The formatted value of a cell will be used. This is the value as you see it when you are viewing the worksheet in Excel.
For instance, the raw value might be 23.4521 whereas the formatted value is $23.45
For compatibility with previous versions, when running a batch script and this is not specified:
- when Native Excel interface is specified, raw values will be used
- when Native Excel interface is not specified, formatted values will be used
Table
- select the table by using the Browse button or the drop-down list.
- select the database from the Database drop-down list.
- Use same schema and table name as target table and Use same table name as target table are useful when you are loading data into many tables. These will automatically set the table name when you select another target table. When Use same schema and table name as target table has been specified, the source schema and table name will be set to the same as the target schema and table name. You might use this, for instance, if you are copying data between tables with the same names in different databases. When Use same table name as target table is specified, only the source table name (and not schema name) is set to the same as the source table. You might use this, for instance, if you are copying data between tables in two different schema in the same database.
Query
Enter the query into the Query text box (copy and paste the SQL from your query), and select the database from the Run query against database drop-down list.