Field Terminator
It is possible to use optional terminating characters to mark the end of a field or row, separating one field or row in the data file from the next. Terminating characters indicate to a program reading the data file where one field or row ends and another begins. The default provided by the bcp utility is to use no terminating characters between fields and rows in the data file.
Field terminators are needed when the data file does not contain:
- Length prefixes to indicate the length of each field (perhaps because the program reading the data file does not understand length prefixes).
- Fixed-length data fields (perhaps because storage space needs to be minimized).
The bcp utility allows many characters to be used as field or row terminators.
| Terminator | Indicated by |
|---|---|
| Tab | \t |
| Newline character | \n |
| Carriage return | \r |
| Backslash | \\ |
| Null terminator (no visible terminator) | \0 |
| Any printable character (control characters are not printable, except null, tab, newline, and carriage return) | (*, A, t, l, and so on) |
| String of up to 10 printable characters, including some or all of the terminators listed earlier | (**\t**, end, !!!!!!!!!!, \t--\n, and so on) |
Note Only the t, n, r, \, and 0 characters work with the backslash escape character to produce a control character.
It is possible to change the default field and row terminators using the -t and -r switches of bcp. When using these switches, the bracketed default listed in the interactive bcp prompt changes for all fields and rows to the value specified at the command prompt. Use -t to change the default field terminator and -r to change the default row terminator.
The command to change the default field terminator to a comma (,) and the default row terminator to the newline character (\n):
bcp pubs..publishers out publ.txt -t , -r \n -Sservername -Usa -Ppassword
Important Terminators must be chosen to ensure that their pattern does not appear in any of the data. For example, when using tab terminators with a field that contains tabs as part of the data, bcp does not know which tab represents the end of the field. The bcp utility always looks for the first possible character(s) that matches the terminator it expects. Using a character sequence with characters that do not occur in the data avoids this conflict.
Native format data can also conflict with terminators because this file is in the SQL Server internal binary data format. When using native format, use length prefixes rather than field terminators.
Any data column that contains null values is considered variable length for bulk copy purposes. Therefore, a length prefix or field terminator needs to be used to specify the length of each field.
Note The no terminator value is different from the null terminator (\0) value. The no terminator value places no row terminator character(s). The null terminator value puts a null character after the column. A null character is invisible but real.
Because bcp does not prompt for a row terminator, the field terminator for the last column in a row serves that purpose. Given a row with 10 columns, the field terminator for the tenth column is also the row terminator. Therefore, the terminator for the last field can be (but is not required to be) different from the field terminator used for other fields in the same row. For tabular output, terminate the last field with the newline character (\n) and all other fields with the tab character (\t).
A common row terminator used when exporting SQL Server data to ASCII data files is \r\n (carriage return, newline). Using both characters as the row terminator ensures that each row of data appears on its own line in the data file. However, it is only necessary to enter the characters \r\n as the terminator when manually editing the terminator column of a bcp format file. When you use bcp interactively and specify \n (newline) as the row terminator, bcp prefixes the \r (carriage return) character automatically.