指定字段终止符和行终止符 (SQL Server)Specify Field and Row Terminators (SQL Server)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

对于字符数据字段,可选的终止字符允许在数据文件中使用“字段终止符” 标记每个字段的结尾,以及使用“行终止符” 标记每行的结尾。For character data fields, optional terminating characters allow you to mark the end of each field in a data file with a field terminator and the end of each row with a row terminator. 终止字符是为读取数据文件的程序指明一个字段或行的结束位置和另一个字段或行的开始位置的一种方式。Terminating characters are one way to indicate to programs that read the data file where one field or row ends and another field or row begins.

重要

使用本机格式或 Unicode 本机格式时,请使用长度前缀而不要使用字段终止符。When you use native or Unicode native format, use length prefixes rather than field terminators. 本机格式数据可能与终止符冲突,因为本机格式的数据文件是以 MicrosoftMicrosoftSQL ServerSQL Server 内部二进制数据格式存储的。Native format data can conflict with terminators because a native-format data file is stored in the MicrosoftMicrosoftSQL ServerSQL Server internal binary data format.

可用作终止符的字符Characters Supported As Terminators

bcp 命令、BULK INSERT 语句和 OPENROWSET 大容量行集提供程序支持多种字符作为字段终止符或行终止符,并始终查找每个终止符的第一个实例。The bcp command, BULK INSERT statement, and the OPENROWSET bulk rowset provider support a variety of characters as field or row terminators and always look for the first instance of each terminator. 下表列出了支持的终止符字符。The following table lists the supported characters for terminators.

终止字符Terminating character 表示方法Indicated by
选项卡Tab \t\t

这是默认的字段终止符。This is the default field terminator.
换行符Newline character \n\n

这是默认的行终止符。This is the default row terminator.
回车符/换行符Carriage return/line feed \r\r
反斜杠*Backslash* \|
空终止符(不可见终止符)**Null terminator (nonvisible terminator)** \0\0
任何可打印的字符(控制字符是不可打印的,除空值、制表符、换行符和回车之外)Any printable character (control characters are not printable, except null, tab, newline, and carriage return) (*、A、t、l 等)(*, A, t, l, and so on)
最长可达 10 个可打印字符的字符串,包括上面列出的部分或全部终止符String of up to 10 printable characters, including some or all of the terminators listed earlier (**\t**、end、!!!!!!!!!!、\t-\n 等)(**\t**, end, !!!!!!!!!!, \t-\n, and so on)

*只有 t、n、r、0 和 '\0' 字符与反斜杠转义字符一起使用才能生成控制符。*Only the t, n, r, 0 and '\0' characters work with the backslash escape character to produce a control character.

**即使在打印时空控制字符 (\0) 不可见,它在数据文件中仍然是一个独特的字符。**Even though the null control character (\0) is not visible when printed, it is a distinct character in the data file. 这表示使用空控制字符作为字段终止符或行终止符与根本没有字段终止符或行终止符是不同的。This means that using the null control character as a field or row terminator is different than having no field or row terminator at all.

重要

如果数据中出现终止符字符,则它被视为终止符(而非数据),并且该字符后的数据被视为属于下一个字段或记录。If a terminator character occurs within the data, it is interpreted as a terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. 因此,请仔细选择您的终止符,以确保它们从未出现在您的数据中。Therefore, choose your terminators carefully to make sure that they never appear in your data. 例如,如果数据中包含某个低代理项,则低代理项字段终止符并不是一个好的选择。For example, a low surrogate field terminator would not be a good choice for a field terminator if the data contains that low surrogate.

使用行终止符Using Row Terminators

行终止符可以与最后一个字段的终止符相同。The row terminator can be the same character as the terminator for the last field. 但是,通常来说,非重复的终止符是很有用的。Generally, however, a distinct row terminator is useful. 例如,若要生成表格格式的输出,可用换行符 (\n) 终止每行的最后一个字段,并用制表符 (\t) 终止所有其他字段。For example, to produce tabular output, terminate the last field in each row with the newline character (\n) and all other fields with the tab character (\t). 若要将每个数据记录放置在数据文件中的各自的行上,请指定 \r\n 组合作为行终止符。To place each data record on its own line in the data file, specify the combination \r\n as the row terminator.

备注

交互使用 bcp 并指定 \n(换行符)作为行终止符时, bcp 将自动以 \r(回车符)作为前缀,从而形成行终止符 \r\n。When you use bcp interactively and specify \n (newline) as the row terminator, bcp automatically prefixes it with a \r (carriage return) character, which results in a row terminator of \r\n.

指定大容量导出的终止符Specifying Terminators for Bulk Export

大容量导出 charnchar 数据,并且希望使用非默认终止符时,必须向 bcp 命令指定终止符。When you bulk export char or nchar data, and want to use a non-default terminator, you must specify the terminator to the bcp command. 可以用下列任一方式指定终止符:You can specify terminators in any of the following ways:

  • 使用格式化文件逐个字段指定终止符。With a format file that specifies the terminator on a field-by-field basis.

    备注

    有关如何使用格式化文件的详细信息,请参阅导入或导出数据的格式化文件 (SQL Server)For information about how to use format files, see Format Files for Importing or Exporting Data (SQL Server).

  • 不使用格式化文件,但有下列可选方式:Without a format file, the following alternatives exist:

    • 使用 -t 开关为行中的所有字段(除最后一个字段以外)指定字段终止符,并使用 -r 开关指定行终止符。Using the -t switch to specify the field terminator for all the fields except the last field in the row and using the -r switch to specify a row terminator.

    • 使用字符格式开关 -c-w(而不是 -t 开关)将字段终止符设置为制表符 \t。Using a character-format switch (-c or -w) without the -t switch, which sets the field terminator to the tab character, \t. 这与指定 -t\t 的作用相同。This is the same as specifying -t\t.

      备注

      如果指定了 -n (本机数据)或 -N (Unicode 本机)开关,则不会插入终止符。If you specify the -n (native data) or -N (Unicode native) switch, terminators are not inserted.

    • 如果交互 bcp 命令包含 inout 选项,而不包含格式化文件开关 (-f) 或数据格式开关(-n-c-w-N),并且选择不指定前缀长度和字段长度,则每个字段的字段终止符的命令提示符默认为无:If an interactive bcp command contains the in or out option without either the format file switch (-f) or a data-format switch (-n, -c, -w, or -N), and you have chosen not to specify prefix length and field length, the command prompts for the field terminator of each field, with a default of none:

      Enter field terminator [none]:

      通常,默认设置是适当的选择。Generally, the default is a suitable choice. 但是,有关 charnchar 数据字段,请参阅下一节“使用终止符指南”。However, for char or nchar data fields, see the following subsection, "Guidelines for Using Terminators." 有关在上下文中显示此提示符的示例,请参阅在使用 bcp 时指定数据格式以获得兼容性 (SQL Server)For an example that shows this prompt in context, see Specify Data Formats for Compatibility when Using bcp (SQL Server).

      备注

      在你以交互方式指定 bcp 命令中的所有字段后,该命令会提示你将自己对每个字段的响应保存到一个非 XML 格式化文件中。After you interactively specify all of the fields in a bcp command, the command prompts you save your responses for each field in a non-XML format file. 有关非 XML 格式文件的详细信息,请参阅非 XML 格式化文件 (SQL Server)For more information about non-XML format files, see Non-XML Format Files (SQL Server).

使用终止符指南Guidelines for Using Terminators

在某些情况下,终止符对 charnchar 数据字段是很有用的。In some situations, a terminator is useful for a char or nchar data field. 例如:For example:

  • 对于数据文件中包含 null 值的数据列,将不会被导入到不能理解前缀长度信息的程序中。For a data column that contains a null value in a data file that will be imported into a program that does not understand the prefix length information.

    包含 null 值的任何数据列都被认为是可变长度。Any data column that contains a null value is considered variable length. 如果缺少前缀长度,则需要一个终止符来确定 null 字段的结尾,同时确保正确地解释数据。In the absence of prefix lengths, a terminator is necessary to identify the end of a null field, making sure that the data is correctly interpreted.

  • 对于其空间只被许多行部分占用的固定长度的长列。For a long fixed-length column whose space is only partially used by many rows.

    在这种情况下,指定一个终止符可以最大限度地减少存储空间,同时可以将字段视为可变长度字段。In this situation, specifying a terminator can minimize storage space allowing the field to be treated as a variable-length field.

指定 \n 作为批量导出的行终止符Specifying \n as a Row Terminator for Bulk Export

当指定 \n 作为批量导出的行终止符,或隐式使用默认行终止符时,bcp 将输出回车符和换行符的组合 (CRLF),作为行终止符。When you specify \n as a row terminator for bulk export, or implicitly use the default row terminator, bcp outputs a carriage return-line feed combination (CRLF) as the row terminator. 如果只想输出换行符 (LF) 作为行终止符(在 Unix 和 Linux 计算机上很典型),请使用十六进制表示法来指定 LF 行终止符。If you want to output a line feed character only (LF) as the row terminator - as is typical on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. 例如:For example:

bcp -r '0x0A'

示例Examples

此示例使用字符格式将 AdventureWorks.HumanResources.Department 表中的数据批量导出至 Department-c-t.txt 数据文件,其中将逗号用作字段终止符,将换行符 (\n) 用作行终止符。This example bulk exports the data from the AdventureWorks.HumanResources.Department table to the Department-c-t.txt data file using character format, with a comma as a field terminator and the newline character (\n) as the row terminator.

bcp 命令包含以下开关。The bcp command contains the following switches.

开关Switch 描述Description
-c-c 指定将数据字段作为字符数据加载。Specifies that the data fields be loaded as character data.
-t ,-t , 指定逗号 (,) 作为字段终止符。Specifies a comma (,) as the field terminator.
-r \n-r \n 指定行终止符作为换行符。Specifies the row terminator as a newline character. 这是默认的行终止符,因此将其指定为可选。This is the default row terminator, so specifying it is optional.
-T-T 指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 如果未指定 -T ,则需要指定 -U-P 才能成功登录。If -T is not specified, you need to specify -U and -P to successfully log in.

有关详细信息,请参阅 bcp UtilityFor more information, see bcp Utility.

MicrosoftMicrosoft Windows 命令提示符下输入:At the MicrosoftMicrosoft Windows command prompt enter:

bcp AdventureWorks.HumanResources.Department out C:\myDepartment-c-t.txt -c -t, -r \n -T  

这创建了 Department-c-t.txt,其中包含 16 条记录,每条记录有四个字段。This creates Department-c-t.txt, which contains 16 records with four fields each. 这些字段由逗号分隔。The fields are separated by a comma.

为大容量导入指定终止符Specifying Terminators for Bulk Import

当大容量导入 charnchar 数据时,大容量导入命令必须识别数据文件中使用的终止符。When you bulk import char or nchar data, the bulk-import command must recognize the terminators that are used in the data file. 如何指定终止符依赖于大容量导入命令,如下所示:How terminators can be specified depends on the bulk-import command, as follows:

  • bcpbcp

    为导入操作指定终止符与为导出操作指定终止符的语法相同。Specifying terminators for an import operation uses the same syntax as for an export operation. 有关详细信息,请参阅本主题前面的“为大容量导出指定终止符”。For more information, see "Specifying Terminators for Bulk Export," earlier in this topic.

  • BULK INSERTBULK INSERT

    使用下表中列出的限定符可以为格式化文件中的各个字段或为整个数据文件指定终止符。Terminators can be specified for individual fields in a format file or for the whole data file by using the qualifiers shown in the following table.

    QualifierQualifier 描述Description
    FIELDTERMINATOR ='field_terminatorFIELDTERMINATOR ='field_terminator' 指定用于字符和 Unicode 字符数据文件的字段终止符。Specifies the field terminator to be used for character and Unicode character data files.

    默认的字段终止符是 \t(制表符)。The default is \t (tab character).
    ROWTERMINATOR ='row_terminatorROWTERMINATOR ='row_terminator' 指定用于字符和 Unicode 字符数据文件的行终止符。Specifies the row terminator to be used for character and Unicode character data files.

    默认的行终止符是 \n(换行符)。The default is \n (newline character).

    有关详细信息,请参阅 BULK INSERT (Transact SQL)For more information, see BULK INSERT (Transact-SQL).

  • INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...)

    对于 OPENROWSET 大容量行集提供程序,仅可以在格式化文件中指定终止符(这是必需的,除大型对象数据类型以外)。For the OPENROWSET bulk rowset provider, terminators can be specified only in the format file (which is required except for large-object data types). 如果字符数据文件使用非默认终止符,则必须在格式化文件中定义该非默认终止符。If a character data file uses a non-default terminator, it must be defined in the format file. 有关详细信息,请参阅创建格式化文件 (SQL Server)使用格式化文件批量导入数据 (SQL Server)For more information, see Create a Format File (SQL Server) and Use a Format File to Bulk Import Data (SQL Server).

    有关 OPENROWSET BULK 子句的详细信息,请参阅 OPENROWSET (Transact-SQL)For more information about the OPENROWSET BULK clause, see OPENROWSET (Transact-SQL).

指定 \n 作为批量导入的行终止符Specifying \n as a Row Terminator for Bulk Import

当指定 \n 作为批量导入的行终止符,或隐式使用默认行终止符时,bcp 和 BULK INSERT 语句将导出回车符和换行符的组合 (CRLF),作为行终止符。When you specify \n as a row terminator for bulk import, or implicitly use the default row terminator, bcp and the BULK INSERT statement expect a carriage return-line feed combination (CRLF) as the row terminator. 如果你的源文件仅使用换行符 (LF) 作为行终止符(在 Unix 和 Linux 计算机上生成的文件中很常见),请使用十六进制表示法来指定 LF 行终止符。If your source file uses a line feed character only (LF) as the row terminator - as is typical in files generated on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. 例如,在 BULK INSERT 语句中:For example, in a BULK INSERT statement:

    ROWTERMINATOR = '0x0A'

示例Examples

本部分的示例将字符数据从上述示例中创建的 Department-c-t.txt 数据文件大容量导入到 myDepartment 示例数据库的 AdventureWorks2012AdventureWorks2012 表中。The examples in this section bulk import character data form the Department-c-t.txt data file created in the preceding example into the myDepartment table in the AdventureWorks2012AdventureWorks2012 sample database. 必须先创建此表,才能运行这些示例。Before you can run the examples, you must create this table. 若要在 dbo 架构下创建此表,请在 SQL Server Management StudioSQL Server Management Studio 查询编辑器中执行以下代码:To create this table under the dbo schema, in SQL Server Management StudioSQL Server Management Studio Query Editor, execute the following code:

USE AdventureWorks;  
GO  
DROP TABLE myDepartment;  
CREATE TABLE myDepartment   
(DepartmentID smallint,  
Name nvarchar(50),  
GroupName nvarchar(50) NULL,  
ModifiedDate datetime not NULL CONSTRAINT DF_AddressType_ModifiedDate DEFAULT (GETDATE())  
);  
GO 

A.A. 使用 bcp 交互指定终止符Using bcp to interactively specify terminators

以下示例使用 Department-c-t.txt 命令大容量导入 bcp 数据文件。The following example bulk imports the Department-c-t.txt data file using a bcp command. 该命令与大容量导出命令使用相同的命令开关。This command uses the same command switches as the bulk export command. 有关详细信息,请参阅本主题前面的“为大容量导出指定终止符”。For more information, see "Specifying Terminators for Bulk Export," earlier in this topic.

在 Windows 命令提示符下输入:At the Windows command prompt enter:

bcp AdventureWorks..myDepartment in C:\myDepartment-c-t.txt -c -t , -r \n -T  

B.B. 使用 BULK INSERT 交互指定终止符Using BULK INSERT to interactively specify terminators

以下示例使用 Department-c-t.txt 语句大容量导入 BULK INSERT 数据文件,该语句使用了下表中所示的限定符。The following example bulk imports the Department-c-t.txt data file using a BULK INSERT statement that uses the qualifiers shown in the following table.

选项Option AttributeAttribute
DATAFILETYPE =' char 'DATAFILETYPE =' char ' 指定将数据字段作为字符数据加载。Specifies that the data fields be loaded as character data.
FIELDTERMINATOR =','FIELDTERMINATOR =',' 将逗号 (,) 指定为字段终止符。Specifies a comma (,) as the field terminator.
ROWTERMINATOR ='\n'ROWTERMINATOR ='\n' 指定行终止符作为换行符。Specifies the row terminator as a newline character.

SQL Server Management StudioSQL Server Management Studio 查询编辑器中,执行以下代码:In SQL Server Management StudioSQL Server Management Studio Query Editor, execute the following code:

USE AdventureWorks;  
GO  
BULK INSERT myDepartment FROM 'C:\myDepartment-c-t.txt'  
   WITH (  
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ',',  
      ROWTERMINATOR = '\n'  
);  
GO  

另请参阅See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
使用 bcp 指定字段长度 (SQL Server) Specify Field Length by Using bcp (SQL Server)
使用 bcp 指定数据文件中的前缀长度 (SQL Server) Specify Prefix Length in Data Files by Using bcp (SQL Server)
使用 bcp 指定文件存储类型 (SQL Server)Specify File Storage Type by Using bcp (SQL Server)