The Bulk Data Utility (BDU) is a command line tool that allows you to load data from a delimited text file into a table. The table and database must already exist.
The BDU, the table, the database, and the Zen database engine must all be located on the same machine. The delimited text file must be locally accessible by the database engine server through a local drive, mapped drive, mounted folder, or shared folder.
You may use a default delimiter or a user-specified delimiter. The delimiting character must not be contained in the data itself. The following tables list the permissible delimiters.
Table 14 Delimiters for Columns
Delimiter
Indicated By
Tab
\t (default)
Any single printable character (control characters are not printable, except null, tab, new line, and carriage return)
(*, A, t, l, and so forth)
Note Zen does not support the use of NULL terminator (\0) or double quote(")as column delimiters.
Table 15 Delimiters for Rows
Delimiter
Indicated By
New line character
\n (default)
Carriage return
\r
Carriage return line feed (CR LF)
\r\n
The BDU supports only the single quote (') and the double quote (") characters as text qualifiers. The data file may contain column values enclosed by single quotes or by double quotes. For example, the following column values are enclosed by double quotes and delimited by the TAB character: "Fred"\t"22"\t"2459"\t"Sales"\t.
The BDU treats consecutive column delimiters as NULL values. If the tool finds consecutive column delimiters, it inserts a NULL value into the column, provided the column is nullable.
No qualifiers are allowed for a NULL value. The following column data inserts a NULL value in the second column. Note that qualifiers are not included for that column: "Fred"\t\t"2459"\t"Sales"\t.
Synopsis
bdu {database_name} {table_name} {data_file}
[-<e|E> max_errors]
[-<r|R> reject_file]
[-<f|F> first_row]
[-<l|L> last_row]
[-<t|T> field_term]
[-<n|N> row_term]
[-<o|O> output_file]
[{-<u|U> login_id} {-<p|P> password}]
[-<c|C> encoding]
[-<h|H>]
Note When loading data with BDU into a secured database for which the Btrieve Security policy is set to Mixed, the supplied credentials (user name and password) must match those of a Zen database user account and an operating system user account.
Parameters
Table 16 Bulk Data Utility Parameters
Parameter
Mandatory/Optional
Default Value
Description
database_name
Mandatory
Database name to connect to the local engine
table_name
Mandatory
Name of the table to be populated
data_file
Mandatory
Name and location of the delimited text file
-<e|E> max_errors
Optional
0 (zero)
The BDU exits when the first error is encountered.
The maximum number of errors that the BDU ignores before exiting
-<r|R> reject_file
Optional
stderr
Name of the file to which to write the rows that failed load. The specified directory must exist. However, if the file does not exist in the specified directory, it will be created.
-<f|F> first_row
Optional
Row 1
The first row in the delimited text file with which the load begins. This parameter allows you to skip a header row. For example, if your header row is row 1, set first_row to 2.
-<l|L> last_row
Optional
End of the source file
Last row in source file (row will be included in load). The load will stop after the end row has been loaded
-<t|T> field_term
Optional
A character, such as a comma or tab character
Column delimiter in the source file
-<n|N> row_term
Optional
A new line character
Row delimiter in the source file
-<o|O> output_file
Optional
stderr
Name of the file to which to write the information and error messages during load. The specified directory must exist. However, if the file does not exist in the specified directory, it will be created.
-<u|U> login_id
Optional
User name to connect to the Relational Engine
-<p|P> password
Optional
Password to connect to the Relational Engine
-<c|C> encoding
Optional
The system code page or “ASCII” if the system code page cannot be determined
The valid values for encoding are:
•ASCII
•UTF-8
•UTF-16LE
•UTF-16BE
The encoding parameter is not case sensitive and does not require quotes. See Examples.
Note: If a data file contains a byte order mark (BOM), BDU uses the encoding specified by the BOM. That is, if a data file uses a BOM to indicate an encoding of UTF-8, UTF-16LE, or UTF-16BE, BDU uses that encoding regardless of what value you specify for the encoding parameter on the command line.
If you provide an invalid value for encoding, the message returned is “Invalid value for command line argument ‘file_encoding.’”
-<h|H>
Optional
Displays the version of BDU and the usage help
Notes
Configuration Settings
You are not required to change any Zen configuration settings to use BDU.
BDU loads data into a table using the accelerated mode. During the load of data, the MicroKernel does not perform transaction logging.
If you use archival logging, back up your data files again.
Error Logging
By default, BDU logs all information and error messages to the standard error stream (stderr). You may specify a log file to which the tool writes the information or error messages.
Two types of errors are not logged: critical and recoverable. With critical errors, BDU exits because it cannot perform error recovery. For example, a missing delimited data file is a critical error.
With recoverable errors, BDU skips the error and continues processing. The tool keeps a count of such skipped errors and exits when it reaches a user-specified threshold. By default, the threshold is set to zero.
Constraints
The following constraints apply to loading data with BDU.
Constraint
Discussion
Any Referential Integrity (RI) error is considered an RI violation
Row is rejected
Any unique or primary key violations
Row is rejected
No value specified for a non-NULL column1
Row is rejected regardless of column’s default value
No value specified for a nullable column1
NULL inserted regardless of column’s default value
Table into which data is being loaded contains insert triggers
BDU returns an error and does not attempt to load the table. Drop the insert triggers on the table then rerun BDU
Table into which data is being loaded contains CLOB or BLOB columns (relational data types LONGVARCHAR, NLONGVARCHAR, or LONGVARBINARY)
BDU does not attempt to load the table and returns the message “The target table contains longvarchar, nlongvarchar, or longvarbinary data types. These data types are not supported.”
Order of rows
BDU treats the delimited data file as unordered. The original order of rows may not be preserved.
Date fields
The only supported format is yyyy-mm-dd
Time fields
The only supported format is HH:MM:SS
Timestamp fields
The only supported format is yyyy-mm-dd HH:MM:SS.MS
White space and column delimiters
No white space must exist between the column delimiters and the data values, even if the data values are enclosed in quotes.
1 BDU is not aware of default values for a column defined during table creation or update
Best Practices
If possible, run BDU when the database load is minimal or when no concurrent sessions exist on the table being loaded.
If the table being loaded contains any indexes, drop the indexes before using BDU. Recreate the indexes after the load is complete.
If the table being loaded contains any columns with check constraints, drop the check constraints before using BDU. Respecify the constraints after the load is complete.
Sample Source File
The following content may be used to create a sample delimited text file. You may use the file to verify the usage examples. The examples refer to the sample file as data_file.txt.
Note that, because the following content is comma delimited, you must specify the -t parameter (-t ,) with BDU. The -t parameter is required for any delimiter except the TAB character.
Note that in any data file being used as source input, no white space must exist between the column delimiters and the data values, even if the data values are enclosed in quotes.
Examples
The following examples assume that a table named BDU_Table is part of the Demodata sample database. To add such a table to Demodata, use the following query:
CREATE TABLE BDU_Table (Name CHAR(20) NOT NULL CASE, PhoneNo INTEGER,BuildingName CHAR(25) NOT NULL CASE, RoomNo UINT NOT NULL,HeadOfDept UBIGINT NOT NULL)
To run BDU with the default options:
bdu demodata BDU_Table D:\data_file.txt
Note The input data must be TAB delimited to use default options. If the input data is not TAB delimited, you must specify the delimiter with the -t parameter.
For example, to use the data from Examples, which is comma-delimited, run BDU as follows:
bdu demodata BDU_Table D:\data_file.txt -t ,
============
To run BDU for the Billing table in the sample database Demodata and load TAB-delimited data from a file that uses UTF-16LE encoding: