bdu

The Bulk Data Utility (BDU) is a command line utility that allows you to load data from a delimited text file into a PSQL table. The table and database must already exist.

The BDU, the table, the database, and the PSQL 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 15

Delimiters for Columns

Any single printable character (control characters are not printable, except null, tab, new line, and carriage return)

(*, A, t, l, and so forth)

Note: PSQL does not support the use of NULL terminator (\0) or double quote (") as column delimiters.

Table 16

Delimiters for Rows

New line character

Carriage return

Carriage return line feed (CR LF)

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 utility 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.

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]

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 PSQL database user account and an operating system user account.

Table 17

Bulk Data Utility Parameters

Mandatory/Optional

Database name to connect to the local PSQL engine

Name of the table to be populated

Name and location of the delimited text file

-<e|E> max_errors

The BDU exits when the first error is encountered.

The maximum number of errors that the BDU ignores before exiting

-<r|R> reject_file

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

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

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

A character, such as a comma or tab character

Column delimiter in the source file

-<n|N> row_term

A new line character

Row delimiter in the source file

-<o|O> output_file

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

User name to connect to the Relational Engine

-<p|P> password

Password to connect to the Relational Engine

-<c|C> encoding

The system code page or “ASCII” if the system code page cannot be determined

The valid values for encoding are:

•

•

•

•

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.’”

Displays the version of BDU and the usage help

Configuration Settings

You are not required to change any PSQL 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.

By default, BDU logs all information and error messages to the standard error stream (stderr). You may specify a log file to which the utility 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 utility keeps a count of such skipped errors and exits when it reaches a user-specified threshold. By default, the threshold is set to zero.

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 re-run 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

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. Re-create 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. Re-specify 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.

psqlBDUsample_1,12345,psql,101,18446744073709551615
psqlBDUsample_2,12346,psql,102,18446744073709551614
psqlBDUsample_3,12347,psql,103,18446744073709551613
psqlBDUsample_4,12348,psql,104,18446744073709551612
psqlBDUsample_5,12349,psql,105,18446744073709551611
psqlBDUsample_6,12350,psql,106,18446744073709551610
psqlBDUsample_7,12351,psql,107,18446744073709551609
psqlBDUsample_8,12352,psql,108,18446744073709551608
psqlBDUsample_9,12353,psql,109,18446744073709551607
psqlBDUsample10,12354,psql,110,18446744073709551606

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.

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 C:\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 C:\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:

bdu demodata Billing D:\billing_data_import.txt -c UTF-16LE

============

To run the BDU for a database that requires username and password:

bdu demodata BDU_Table C:\data_file.txt -u <username> -p <password>

============

To run the BDU with max errors option:

bdu demodata BDU_Table C:\data_file.txt -e <no of errors user wants to allow>

For instance, for loading to continue until 100 errors have occurred:

bdu demodata BDU_Table C:\data_file.txt -e 100

============

To run the BDU with a specific column delimiter option:

bdu demodata BDU_Table C:\data_file.txt -t <column delimiter>

When the source file contains text in which each row is separated by ,

bdu demodata BDU_Table C:\data_file.txt -t ,

============

To run the BDU with a specific row delimiter option:

bdu demodata BDU_Table C:\data_file.txt -n <row delimiter>

For instance, when the source file contains text in which each row is separated by \n:

bdu demodata BDU_Table C:\data_file.txt -n \n

============

To run the BDU with a specific start row option:

bdu demodata BDU_Table C:\data_file.txt -f <line no. from which user wants loading to begin>

============

To run the BDU with a specific end row option:

bdu demodata BDU_Table C:\data_file.txt -l <line no. at which user wants loading to end>

============

You may combine parameters. To load the first 15 rows from the source file containing data that is separated by | and is enclosed in ':

bdu demodata BDU_Table C:\data_file.txt -f 1 -l 15 -t |