PRB: Can't BCP in NULLs from Fixed-Length Files

ID Number: Q75285

1.10 1.11 4.20

OS/2

Summary:

SYMPTOMS

When using the bulk copy program (BCP) utility to import

fixed-length records, NULLs are translated as 0 (zero).

CAUSE

It is common when transferring data from mini/mainframe machines

for the data files to be in fixed-length format, with no

delimiters. A NULL field can be represented as spaces (ASCII 0x20)

in such a file. BCP can accommodate this format either in

interactive mode or by using a format file. However, a field with

all spaces in the source file will be translated to 0 in the

destination SQL column. This is because there is no standard

representation for NULL, and a source field with all spaces is

considered to have possible significance, with 0 as the closest

reasonable interpretation.

RESOLUTION

The only unambiguous way to represent NULL is by using two adjacent

delimiters in a character-delimited file. Manipulation of the

source file to achieve this state is possible via several

techniques. Alternatively, if 0 in the destination SQL column has

no valid meaning for the particular database, NULLs could

temporarily be represented as 0 and then translated to NULL via a

bulk SQL UPDATE statement.

Additional reference words: bcp