Importing Data into the Database
It is possible to upload data to the database using Query Analyzer. This function is available from the Tools menu.
Workbench supports import of source files in the following formats:
- Microsoft Office Excel 97-2003 (.xls) and Microsoft Office Excel 2007 (.xlsx)
- Comma Separated Values (CSV)
- Fixed Column Widths File
- External Database
Files in all the supported formats can be imported directly from an archive (zip, gzip, bzip2) without creating any intermediate disk file that may be quite large. Multiple CSV files can be imported at a time.
Currently, import from external databases is limited to uploading data only from another dashDB database.
Import Data from an External Database
Data import from another database involves generating DDL for the source tables and creating the tables from the DDL to hold the data.
Currently, only import from another dashDB database or an IBM PureData Systems for Analytics (Netezza) database is supported.
Importing data into an table that exists in a target dashDB database is not currently supported.
To import data from an external database:
- On the Tools menu, point to Import and then click from External Database.
- In the Database Type dialog box, select a database type.
- In the Connect to IBM PureData Systems for Analytics dialog box, in the Saved list, select a connection entry.
A connection entry stores the information that is necessary for connecting to a database. The Saved list displays all previously created connection strings. If no connection strings were created, you need to create one. Click New, type the name for a connection entry, specify appropriate connection information in the Connect to IBM PureData Systems for Analytics dialog box, and click Save. To connect to a Netezza database, you must have an ODBC or OleDb driver installed on your system. In the Driver list, select a driver.
How to get an ODBC or OleDb driver
Use the IBM Fix Central web site (http://www-933.ibm.com/support/fixcentral) for finding and downloading the IBM Netezza drivers that are suitable for your processor architecture (32-bit or 64-bit). Also, note that each IBM client accessing the Fix Central is required to have an individual IBM ID.
The connection information from the connection entry is put into the Connect to IBM PureData Systems for Analytics dialog box.
Options in the Connect to IBM PureData Systems for Analytics dialog box
|New||Creates a new connection entry.|
|Delete||Deletes a saved connection entry.|
|Rename||Renames an existing connection entry.|
Provides access to a shortcut menu with the following functions:
- Duplicate Connection Entry - Creates a copy of the current connection entry.
- Connection String to Clipboard - Copies the connection string for the current connection entry to the clipboard.
- Create an Entry from Connection String in Clipboard - Parses a connection string that is currently in the clipboard, if any, and creates a new connection entry based on its contents.
- Export to File - Saves a user-defined set of connection entries to a password protected disk file. This is useful for transferring connection entries from one computer to another.
- Import from File - Reads connection entries from a file created by the Export to File function.
|Save Password||Select this check box to not have to enter your password every time you need to connect to the database.|
- Click OK to connect to the database.
- On the Data Transfer Wizard (step 1 of 3) page, select the tables in the database from which you want to import data.
You can apply a filter to select or unselect tables according to appropriate conditions. Click to define a filter that selects tables or click to define a filter that unselects tables.
- On the Data Transfer Wizard (step 2 of 3) page, select the appropriate options to generate DDL for the selected source tables.
In the Char case list, select a case option to apply to column names in the generated DDL.
Aginity Workbench begins analyzing the selected tables against the target database. If any issues are encountered, the Data Transfer Wizard (step 3 of 3) page displays warnings. Review the warnings and take appropriate action if necessary.
- Click Finish to generate the DDL for the table.
- In the Data Transfer Options window, select appropriate options.
- Table Name Prefix and Table Name Suffix. Type a prefix and a suffix to append to the source table names or leave these boxes blank if you don't want a prefix or suffix to be appended to the table names.
- Transfer Primary Keys and Transfer Unique Keys. Select these check boxes if you want to import the primary keys and unique keys from the source table along with the data.
- Parameters Override. You can override the selected data transfer options for a specific table. Select the table in the Source Entities pane, select the
Override default settings for selected file﴾s﴿
check box, type a name for the table, and change the setting of the Transfer Primary Keys and Transfer Unique Keys check boxes.
- Target DB. Select the target database into which you want to import the data.
- Schema. Select the schema into which you want to import the data.
Click OK to start importing the data.
The Workbench Job Monitor shows the progress of the import. When the import is complete, the Status column displays "Number out of number objects transferred successfully."
If the data import takes longer than the specified Default query time-out value (set in the application options), the import will time out with an error. If you want to import a large amount of data, consider setting a larger Default query time-out value.
The tables that contain the imported data are displayed in the Object Browser.
If the tables are not visible in the Object Browser, refresh it. Right-click the schema into which you imported the data and click Refresh Schema Objects on the shortcut menu.
Import Data into a Destination Database
To import data from a local file:
- On the Tools menu, point to Import and then click the command for the source file format (for example, from Excel).
- If you want to import an Excel file, browse to and select the file and then select the worksheet in the Excel file.
The Data Import Wizard opens.
- If you are importing a JSON/XML file, select the nodes that will be turned into a flattened table during import.
- Select a file encoding and a field delimiter (applicable to a CSV or text file only), or the column widths and file encoding (applicable to a fixed column widths file only).
Specify the range of data to import by setting the Start Column, Start Row, and End Row. If you don't want to import all the rows, clear the Import all rows check box and specify the End Row.
During import of a CSV or text file, Workbench analyzes the data types in the file and makes necessary changes: it adjusts the length of some or all of the columns to the maximum length of the column's data type or changes the data type of columns. By default Workbench analyzes a maximum of 1,000 rows. If the file you are importing contains more than 1,000 rows, the Data Import Wizard displays a message that only 1,000 rows were analyzed. You can select the Analyze more option later in the wizard to have Workbench analyze all the rows in the file.
- Specify whether the first row in the source file contains the column names to be used in the destination table. Ensure that the Include Columns box lists only the columns that you want to import. Use the less than button to remove columns from the Include Columns box and the greater than button to add columns.
- You can specify the order of columns in the target table by using the Up and Down buttons in the Include Columns box.
- The column that is currently selected in the Include Columns box is highlighted in the Data Preview box.
- Specify data types, precision, length, and scale for the columns. Leave the default values that Aginity Workbench has determined to be valid syntax for the source data, or change the values. Select the check box in the Null column if the column can contain null values.
A negative value of Scale means that scale is not applicable to the column.
- Treat all columns as varchar - Selecting this check box activates the Override length to be box. You can override the automatically determined length for all the columns by typing a value in this box and clicking Set.
- Trim strings - Select this check box to remove trailing spaces from strings in the file. This will apply to all string columns in the file (VARCHAR, NVARCHAR, and so on). If you want to remove trailing spaces from individual string columns, clear the Trim strings check box and select the Trim check box next to each column.
During import from an Excel file, trailing spaces are trimmed automatically, and this behavior cannot be changed. To preserve trailing spaces, we recommend that you choose a different file format for import.
- Analyze more (Re-check data types) - Click this button to have Aginity Workbench analyze the data types in the file and automatically make necessary changes. During analysis, the application adjusts the length of some or all of the columns to the maximum length according to the column's data type or changes the data type. The Analyze more option appears for a file that contains more than 1,000 rows (as shown below); the Re-check data types options appears for a file of fewer than 1,000 rows. Click the button and then click Yes to start the analysis. The progress dialog box appears. You can click Stop on the dialog box to stop the analysis for currently processed rows. When the analysis is complete, a dialog box shows a total number of rows where the column lengths or data types were adjusted and lists the changes that were made, as shown below. If the application could not correct an error in data type (such as an incompatible data type for a column), an error message is displayed. You will need to correct the error manually.
- Select a destination database and a schema in the database, and type a name for the table to hold the imported data. Under Existing Table Action, select an action to take if the specified table exists in the destination database. Select Ignore empty lines (all NULLS) if you don't want to import the empty lines or rows in the source file.
- Click Finish.
The Job Monitor displays the progress of a job for the file import. When the import is complete, the Status description column displays a corresponding status.
If you have selected the Existing Table Action of Do Not Import and the specified destination table exists in the database, the Job Monitor displays an error message.
If you chose to import the data into a new table, the table is displayed within the specified schema node in the Object Browser.
You may need to refresh the Object Browser for the table to be displayed.
Aginity Workbench for dashDB *
Topic last updated August 30, 2018