Home

	Expand Search

Utility Guide

Chapter 3 Using bcp to Transfer Data to and from Adaptive Server

Changing the defaults: interactive bcp

Field and row terminators

Field and row terminators

You can use a terminator to mark the end of a column or row, separating one from the next. The default is no terminator.

• Field terminators separate table columns.

• A row terminator is a field terminator for the last field in the row of the table or file.

Terminators are very useful for dealing with character data because you can choose human-readable terminators. The bcp character option, which uses tabs between each column with a newline terminator at the end of each row, is an example of using terminators that enhance the readability of a data file.

When you prepare data for use with other programs, and when you want to use bcp to prepare tabular data, supply your own terminators. The available terminators are:

• Tabs, indicated by

  \t

• New lines, indicated by

  \n

• Carriage returns, indicated by

  \r

• Backslash, indicated by

  \

• Null terminators (no visible terminator), indicated by

  \0

• Any printable character, for example, *, A, t, |

• Strings of up to 10 printable characters, including some or all of the terminators listed above (for example, **\t**, end, !!!!!!!!!!, and \t--\n)

Control characters (ASCII 0-25) cannot be printed.

Choose terminators with patterns that do not appear in any of the data.

For example, using a tab terminator with a string of data that also contains a tab creates an ambiguity: which tab represents the end of the string? bcp always looks for the first possible terminator, which in this case would be incorrect, since the first tab it would encounter would be the one that is part of the data string.

Data in native format can also conflict with terminators. Given a column that contains a 4-byte integer in native format, if the values of these integers are not strictly limited, it will be impossible to choose a terminator that is guaranteed not to appear inside the data. Use bcp's native format option for data in native format.

"No terminator" is different from a "null terminator," which is an invisible, but real, character.

• A field terminator string can be up to 30 characters long. The most common terminators are a tab (entered as

  \t

  and used for all columns except the last one), and a newline (entered as

  \n

  and used for the last field in a row). Other terminators are:

  \0

  (the null terminator),

  \

  (backslash), and

  \r

  (Return). When choosing a terminator, be sure that its pattern does not appear in any of your character data, because bcp always looks for the first possible terminator.

  For example, if you used tab terminators with a string that contained a tab, bcp would not be able to identify which tab represents the end of the string. bcp always looks for the first possible terminator, so, in this example it would find the wrong one.

  A terminator or prefix affects the actual length of data transferred:

  When a terminator or prefix is present, it affects the length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is immediately followed by the terminator or the prefix for the next field. The entry is not padded to the full storage length (char, nchar, and binary data is returned from Adaptive Server already padded to the full length).

  When bcp is copying in from a file, data is transferred until either the number of bytes indicated in the "Length" prompt has been copied or the terminator is encountered. Once the number of bytes equal to the specified length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed.

• Fields stored as char (except char, nchar, and binary fields) instead of their database datatypes take less file storage space with the default length and prefix or a terminator. bcp can use either a terminator or a prefix to determine the most efficient use of storage space. bcp suggests the maximum amount of storage space required for each field as the default. For char or varchar data, bcp accepts any length.

• Table 3-6 and Table 3-7 show the interaction of prefix lengths, terminators, and field length on the information in the file. "P" indicates the prefix in the stored table; "T" indicates the terminator; and dashes, (--) show appended spaces. An ellipsis (...) indicates that the pattern repeats for each field. The field length is 8 bytes for each column; "string" represents the 6-character field each time.

  Adaptive Server char data

  	Prefix length = 0	Prefix length-1, 2, or 4
  No terminator	string--string--.	Pstring--Pstring--.
  Terminator	string--Tstring--T.	Pstring--TPstring--T.

  Other datatypes converted to char storage

  	Prefix length = 0	Prefix length-1, 2, or 4
  No terminator	string--string--.	PstringPstring.
  Terminator	stringTstringT.	PstringTPstringT.