Aginity Workbench is a GUI-based tool that enhances your performance when you are working with your Netezza data warehouse. Data import in the Aginity workbench has a fairly easy method to get data into Netezza system. Aginity workbench provides a couple of options to import data from Excel, CSV, Fixed width and external databases.
Best Option: Import Data Using Aginity Menu– Tools – Import
Workbench supports the 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
- Text File
- JSON/XML File
We will use a CSV file as an example.
- After you select your source file, a data import wizard will be displayed. Give the field delimiter of CSV files here. Click Next.
- Import the wizard analysis column number and rows. Only 1000 rows of the file were read and analyzed. Click Next.
3. Here, specify the columns to import. The first row contains column names. Click Next.
4. Specifying the column data types. The import wizard will automatically analyse 1000 rows, but we can overwrite the column length to make sure the column is large enough for all the records. Click Next.
- Specify the target database and table name. If the table exists, it can truncate before the load or create a new table name with a timestamp. Click Finish. You can find the new table that was created in the database you specified.
Table Specify (External Table) Option
After the target table has been created, right click the target table and you will see the import menu. This option only supports txt and CSV. It will generate the corresponding SQL with the specified parameter. You will need to specify the field delimiter and Encoding or the import will fail and display the message: Operation canceled, Unable to write nzlog/bad files. Error: External Table: count of bad input rows reached max errors list. You can also directly run the external table SQL to load data.
Please note: make sure the column length is big enough to store the data otherwise the import will be failed.
INSERT INTO FILE.TEST
SELECT * FROM