Data & Intelligence

How to Import a Source File into Netezza Database

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

1

We will use a CSV file as an example.

  1. After you select your source file, a data import wizard will be displayed. Give the field delimiter of CSV files here. Click Next.

2

  1. Import the wizard analysis column number and rows. Only 1000 rows of the file were read and analyzed. Click Next.

3

3. Here, specify the columns to import. The first row contains column names. Click Next.

4

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.

5

  1. 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.
Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

6

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

EXTERNAL ‘C:\\Users\\Test.csv’

USING

(

DELIMITER ‘,’

Y2BASE 2000

ENCODING ‘utf8’

REMOTESOURCE ‘ODBC’

ESCAPECHAR ‘\’

)

7

8

 

About the Author

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up