非 XML 格式化文件 (SQL Server)Non-XML Format Files (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

SQL ServerSQL Server 中,大容量导出和导入支持两种类型的格式化文件:非 XML 格式化文件 和 XML 格式化文件 。In SQL ServerSQL Server, two types of format files are supported for bulk exporting and importing: non-XML format files and XML format files.

本主题内容:In this Topic:

非 XML 格式化文件的优点Benefits of Non-XML Format Files

  • 通过在 bcp 命令中指定 format 选项,可以自动创建非 XML 格式化文件。You can create a non-XML format file automatically by specifying the format option in a bcp command.

  • 当在 bcp 命令中指定某个现有的格式化文件时,该命令就使用记录在该格式化文件中的值,而不提示你输入文件存储类型、前缀长度、字段长度或字段终止符。When you specify an existing format file in a bcp command, the command uses the values that are recorded in the format file and does not prompt you for the file storage type, prefix length, field length, or field terminator.

  • 您可为特定的数据类型(例如字符数据或本机数据)创建格式化文件。You can create a format file for a particular data type such as character data or native data.

  • 还可以创建非 XML 格式化文件,这种文件包含为每个数据字段交互式指定的属性。You can create a non-XML format file that contains interactively specified attributes for each data field. 有关详细信息,请参阅在使用 bcp 时指定数据格式以获得兼容性 (SQL Server)For more information, see Specify Data Formats for Compatibility when Using bcp (SQL Server).

备注

XML 格式化文件与非 XML 格式化文件相比具有一些优点。XML format files offer several advantages over non-XML format files. 有关详细信息,请参阅 XML 格式化文件 (SQL Server)For more information, see XML Format Files (SQL Server).

非 XML 格式化文件的结构Structure of Non-XML Format Files

非 XML 格式化文件是具有特殊结构的文本文件。A non-XML format file is a text file that has a specific structure. 非 XML 格式化文件包含了有关每个表列的文件存储类型、前缀长度、字段长度和字段终止符的信息。The non-XML format file contains information about the file storage type, prefix length, field length, and field terminator of every table column.

下图显示了一个示例非 XML 格式化文件的格式化文件字段。The following illustration illustrates the format-file fields for a sample non-XML format file.

标识非 XML 格式文件的字段

“版本”和 “列数”字段仅出现一次。The Version and Number of columns fields occur one time only. 下表对其意义进行了说明。Their meanings are describes in the following table.

格式化文件字段Format-file field 描述Description
版本Version bcp 实用工具的版本号:Version number of the bcp utility:

9.0 = SQL Server 2005 (9.x)SQL Server 2005 (9.x)9.0 = SQL Server 2005 (9.x)SQL Server 2005 (9.x)

10.0 = SQL Server 2008SQL Server 200810.0 = SQL Server 2008SQL Server 2008

11.0 = SQL Server 2012 (11.x)SQL Server 2012 (11.x)11.0 = SQL Server 2012 (11.x)SQL Server 2012 (11.x)

12.0 = SQL Server 2014 (12.x)SQL Server 2014 (12.x)12.0 = SQL Server 2014 (12.x)SQL Server 2014 (12.x)

该版本号仅可由 bcp识别,而无法由 Transact-SQLTransact-SQL识别。The version number is recognized only by bcp, not by Transact-SQLTransact-SQL.



注意:读取格式化文件所用的 bcp 实用工具 (Bcp.exe) 的版本必须与创建格式化文件所用的版本相同或更高 。Note: The version of the bcp utility (Bcp.exe) used to read a format file must be the same as, or a later version than was used to create the format file. 例如, SQL Server 2012 (11.x)SQL Server 2012 (11.x)bcp 可以读取由 SQL Server 2008SQL Server 2008bcp生成的 10.0 版格式化文件,但 SQL Server 2008SQL Server 2008bcp 无法读取由 SQL Server 2014 (12.x)SQL Server 2014 (12.x)bcp生成的 12.0 版格式化文件。For example, SQL Server 2012 (11.x)SQL Server 2012 (11.x)bcp can read a version 10.0 format file, which is generated by SQL Server 2008SQL Server 2008bcp, but SQL Server 2008SQL Server 2008bcp cannot read a version 12.0 format file, which is generated by SQL Server 2014 (12.x)SQL Server 2014 (12.x)bcp.
列数Number of columns 数据文件中字段的数目。Number of fields in the data file. 该数目必须在所有行中都相同。This number must be the same in all rows.

其他格式化文件字段说明需要大容量导入或导出的数据字段。The other format-file fields describe the data fields that are to be bulk imported or exported. 每个数据字段都需在格式化文件中占单独一行。Each data field requires a separate row in the format file. 每个格式化文件行均包含下表中说明的格式化文件字段的值。Every format-file row contains values for the format-file fields that are described in the following table.

格式化文件字段Format-file field 描述Description
宿主文件字段顺序Host file field order 用以表示数据文件中每个字段的位置的数字。A number that indicates the position of each field in the data file. 行中的第一个字段为 1,依此类推。The first field in the row is 1, and so on.
宿主文件数据类型Host file data type 表示存储在数据文件的给定字段中的数据类型。Indicates the data type that is stored in a given field of the data file. 对于 ASCII 数据文件,使用 SQLCHAR;对于本机格式数据文件,使用默认的数据类型。With ASCII data files, use SQLCHAR; for native format data files, use default data types. 有关详细信息,请参阅 使用 bcp 指定文件存储类型 (SQL Server)For more information, see Specify File Storage Type by Using bcp (SQL Server).
前缀长度Prefix length 字段长度前缀字符的数目。Number of length prefix characters for the field. 有效前缀长度是 0、1、2、4 和 8。Valid prefix lengths are 0, 1, 2, 4, and 8. 若要避免指定长度前缀,将其设置为 0。To avoid specifying the length prefix, set this to 0. 如果字段包含 NULL 数据值,则必须指定长度前缀。A length prefix must be specified if the field contains NULL data values. 有关详细信息,请参阅 使用 bcp 指定数据文件中的前缀长度 (SQL Server)For more information, see Specify Prefix Length in Data Files by Using bcp (SQL Server).
宿主文件数据长度Host file data length 数据文件的特定字段中所存储的数据类型的最大长度(按字节计)。Maximum length, in bytes, of the data type stored in the particular field of the data file.

如果您正在为带分隔符的文本文件创建非 XML 格式化文件,则可以将每个数据字段的宿主文件数据长度指定为 0。If you are creating a non-XML format file for a delimited text file, you can specify 0 for the host file data length of every data field. 当带分隔符的文本文件的前缀长度为 0 并导入终止符时,可忽略字段长度值,因为字段所使用的存储空间等于数据加上终止符的长度。When a delimited text file having a prefix length of 0 and a terminator is imported, the field-length value is ignored, because the storage space used by the field equals the length of the data plus the terminator.

有关详细信息,请参阅 使用 bcp 指定字段长度 (SQL Server)For more information, see Specify Field Length by Using bcp (SQL Server).
终止符Terminator 用来分隔数据文件中各字段的分隔符。Delimiter to separate the fields in a data file. 常用的终止符为逗号 (,)、制表符 (\t) 和行结束符 (\r\n)。Common terminators are comma (,), tab (\t), and end of line (\r\n). 有关详细信息,请参阅 指定字段终止符和行终止符 (SQL Server)For more information, see Specify Field and Row Terminators (SQL Server).
服务器列顺序Server column order 列在 SQL ServerSQL Server 表中显示的顺序。Order in which columns appear in the SQL ServerSQL Server table. 例如,如果数据文件的第四个字段映射到 SQL ServerSQL Server 表中的第六列,则第四个字段的服务器列顺序为 6。For example, if the fourth field in the data file maps to the sixth column in a SQL ServerSQL Server table, the server column order for the fourth field is 6.

若要阻止表中的某个列接收数据文件中的任何数据,则可以将服务器列顺序值设置为 0。To prevent a column in the table from receiving any data from the data file, set the server column order value to 0.
服务器列名Server column name SQL ServerSQL Server 表中复制的列名。Name of the column copied from the SQL ServerSQL Server table. 无需使用字段的实际名称,但格式化文件中的字段不得为空。The actual name of the field is not required, but the field in the format file must not be blank.
列排序规则Column collation 排序规则用于在数据文件中存储字符和 Unicode 数据。The collation used to store character and Unicode data in the data file.

备注

您可以修改格式化文件,以便从字段编号或顺序与表列的编号或顺序不同的数据文件进行大容量导入。You can modify a format file to let you bulk import from a data file in which the number or order of the fields are different from the number or order of table columns. 有关详细信息,请参阅本主题后面的 相关任务 列表。For more information, see the Related Tasks list, later in this topic.

非 XML 格式化文件的示例Example of a Non-XML Format File

下面的示例显示了一个以前创建的非 XML 格式化文件 (myDepartmentIdentical-f-c.fmt)。The following example shows a previously created non-XML format file (myDepartmentIdentical-f-c.fmt). 该文件描述了 HumanResources.Department 示例数据库中的 AdventureWorks2012 表中每一列的字符数据字段。This file describes a character-data field for every column in the HumanResources.Department table in the AdventureWorks2012 sample database.

生成的格式化文件 myDepartmentIdentical-f-c.fmt包含以下信息:The generated format file, myDepartmentIdentical-f-c.fmt, contains the following information:

12.0  
4  
1       SQLCHAR       0       7       "\t"     1     DepartmentID     ""  
2       SQLCHAR       0       100     "\t"     2     Name             SQL_Latin1_General_CP1_CI_AS  
3       SQLCHAR       0       100     "\t"     3     GroupName        SQL_Latin1_General_CP1_CI_AS  
4       SQLCHAR       0       24      "\r\n"   4     ModifiedDate     ""  

备注

有关显示与该非 XML 格式化文件示例相关的格式化文件字段的说明,请参阅本主题前面的 非 XML 格式化文件的结构For an illustration that shows the format-file fields in relation to this sample non-XML format file, see Structure of Non-XML Format Files, earlier in this topic.

相关任务Related Tasks

另请参阅See Also

bcp 实用工具 bcp Utility
创建格式化文件 (SQL Server) Create a Format File (SQL Server)
XML 格式化文件 (SQL Server) XML Format Files (SQL Server)
用来导入或导出数据的格式化文件 (SQL Server)Format Files for Importing or Exporting Data (SQL Server)