SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

 

Previous Topic:
The #INCLUDE Directive
Chapter Index
Next Topic:
Verifying and Filtering Data Using the PIC Clause

 

Import Examples


The following simple examples address each of the steps required to load data from an ASCII file into a database using ndlm. This example assumes that the target database called testdb has been created and configured.


Creating the Destination Table

A table named friend is created in the people schema of the testdb database as follows:

CREATE TABLE people.friend
(
age INTEGER,
first_name CHAR(10),
last_name CHAR(20)
);


Creating the Input File

The ASCII file names.txt has the following appearance:

Ignore these 22 bytes
George    , "Smith",       M:57
Martha    , "Jones",       F:39
Mary      , "Lincoln",     F:78
John      , "Marshall",    M:15

In this example, each record is terminated by a line feed character. Last names are separated from gender by a comma and a tab. The first 22 characters in the first row constitute the file header (including the line feed character) which will be ignored by ndlm during the import.

Note:
The above example applies to a UNIX-format input file. If names.txt was created under Windows, the end-of-record signifier would be the ASCII carriage return and line feed characters together (\r\n) instead of just the line feed character (\n). Because of this extra character, the first line would be "Ignore these 23 bytes" in the equivalent Windows example.


Creating the Import Specification File

The import specification file has the following appearance; it is followed by an explanation of its contents:

IMPORT @names.txt
{

IGNORE 22
RECORD
{
field_first     10,\s
field_last      "*",\t
field_gender    1:
field_age       2\n
}
people.friend
{
first_name     field_first
last_name      field_last
age            field_age
}

}

This import specification file instructs ndlm as follows:

 

Ndlm Invocation Example

For the purposes of this example, assume that the specification file is called names.imp. To load all rows from names.txt into the people.friend table in the database called testdb, started using the SAND CDBMS connection server1_testdb, invoke ndlm from the operating system command prompt with the user name DBA as follows:

ndlm server1_testdb testdb DBA names.imp

Note that if a user name other than DBA is used, that user must have at least SELECT and INSERT privileges on the target table.

ndlm displays a preview screen that shows the columns and their corresponding values for the first three records, and prompts for confirmation that the data is formatted correctly. If the prompt is answered affirmatively, the import proceeds and all the records are loaded into the friend table in the people schema. If answered negatively, the import terminates before any records are loaded into the friend table.

 

Additional Invocation Examples

Suppose you have already tested the import procedure and know that the data is formatted properly. You can suppress the verification prompts by specifying the -b flag as follows:

ndlm server1 -b server1_testdb testdb DBA names.imp


If you want to import only the first 20,000 rows of the input file, you could use the -p flag while suppressing the prompts with -b as follows:

ndlm server1 -b -p 20000 server1_testdb testdb DBA names.imp


If you want to import rows 20,001 through 30,000 you could do so by using the -p and -s flags together as follows:

ndlm server1 -b -s 20000 -p 10000 server1_testdb testdb DBA names.imp
 
Previous Topic:
The #INCLUDE Directive
Chapter Index
Next Topic:
Verifying and Filtering Data Using the PIC Clause