主页 / dba / 问题 / 303337
Accepted
Martin Smith
Asked: 2021-12-02 05:56:45 +0800 CST

是否可以使用 OPENROWSET 导入固定宽度的 UTF8 编码文件？

  • 772

我有一个包含以下内容并使用 UTF8 编码保存的示例数据文件。

oab~opqr
öab~öpqr
öab~öpqr

该文件的格式为固定宽度，第 1 到第 3 列各分配 1 个字符，第 4 列保留 5 个字符。

我创建了一个 XML 格式文件，如下所示

<?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharFixed" ID="Col1" LENGTH="1"/>       
      <FIELD xsi:type="CharFixed" ID="Col2" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col3" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col4" LENGTH="5"/> 
      <FIELD xsi:type="CharTerm" ID="LINE_BREAK" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="Col1" NAME="Col1" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col2" NAME="Col2" xsi:type="SQLNVARCHAR"/> 
      <COLUMN SOURCE="Col3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col4" NAME="Col4" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

令人失望地运行以下 SQL...

SELECT *
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X

产生以下结果

Col1 Col2 Col3 Col4
---- ---- ---- -----
o    a    b    ~opqr
�    �    a    b~öp
�    �    a    b~öp

我从中得出的结论LENGTH是计算字节而不是字符。

有什么方法可以让这个使用 UTF8 编码的固定字符宽度正常工作吗？

（目标环境是从 Blob 存储读取的 Azure SQL 数据库）

COLLATION="LATIN1_GENERAL_100_CI_AS_SC_UTF8"注意：评论中建议添加FIELD元素可能会有所帮助，但结果保持不变。

sql-server azure-sql-database

3 个回答

  1. 一种解决方法是仅更改格式文件以批量导入整行，并在 TSQL 中执行子字符串化

    带格式文件

    <?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharTerm" ID="WholeLine" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="WholeLine" NAME="WholeLine" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

    以下确实返回了所需的结果

    SELECT SUBSTRING(WholeLine, 1,1) AS Col1,
       SUBSTRING(WholeLine, 2,1) AS Col2,
       SUBSTRING(WholeLine, 3,1) AS Col3,
       SUBSTRING(WholeLine, 4,5) AS Col4
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X
    • 9
  2. Best Answer

    我从中得出的结论LENGTH是计算字节而不是字符。

    这是正确的，没有办法改为字符。

    这种情况类似于char( n )、varchar( n )、nchar( n ) 和 nvarchar( n ) 中的n，其中“n”表示字节数，而不是字符数。请参阅文档：

    一个常见的误解是认为 CHAR(n) 和 VARCHAR(n) 中的 n 定义了字符数。但在 CHAR(n) 和 VARCHAR(n) 中，n 定义了以字节为单位的字符串长度 (0-8,000)。n 从不定义可以存储的字符数。这类似于 NCHAR(n) 和 NVARCHAR(n) 的定义。产生这种误解是因为在使用单字节编码时，CHAR 和 VARCHAR 的存储大小是 n 字节，字符数也是 n。但是，对于 UTF-8 等多字节编码，更高的 Unicode 范围 (128-1,114,111) 会导致一个字符使用两个或更多字节。

    这让许多人感到困惑，尤其是在引入UTF-8 支持之后。以前可以使用 n(var)char 和补充字符，但我会说相对很少遇到。

    如果 SQL Server将来在多个领域（包括 OPENROWSET）扩展其对字符而不是字节的支持，那就太好了。

    同时，您的解决方法也是我可能会使用的解决方法。

    • 5

  3. 只是把它作为另一种选择，特别是对于那些无法在 SQL Server 中修改进程的人：

    您还可以将文件编码从 UTF-8 转换为 UTF-16 LE（Little Endian；在许多 Microsoft 产品中通常简称为“Unicode”）。然后，您将更改以下内容（根据问题中发布的内容）：

    在格式文件中：

    1. xsi:type从更改CharFixed为NCharFixed
    2. 将每个值加倍LENGTH（例如 2 -> 4、5 -> 10）
    3. 对于<FIELD ID="LINE_BREAK" ...>：
      1. xsi:type从更改CharTerm为NCharFixed
      2. 消除TERMINATOR="\r\n
      3. 添加LENGTH="4"（使用 "4" 表示 "\r\n" 或 2 表示 "\n"）

    在对 OPENROWSET() 的调用中：

    1. , CODEPAGE = '65001'使用 "NChar*" 时忽略删除代码页xsi:type。

     
    笔记

    1. 需要将值加倍LENGTH是更多（和更悲伤）的证据，LENGTH它始终是字节。遗憾的是他们没有将其设为代码单元，因此LENGTH“1”中的 a 将获得任何 BMP 字符，就像您在 T-SQL 中NCHAR(1)所期望的一样。NVARCHAR(1)

    2. 为什么切换CharTerm到NCharFixed而不是NCharTerm？因为我不能NCharTerm上班。我的测试数据是 100% 正确的，但使用NCharTerm只会导入第一行。可能是一个错误。

    3. 虽然这确实处理 UTF-8 中为 2 或 3 个字节的字符，但此选项和 ​​UTF-8 都不能处理组合字符。意思是，ö示例数据中的 可以是单个字符（就像问题中的示例数据一样），也可以是非重音o符号加上变音符号的组合（2 个字符，但现在 UTF-8 中的 3 个字节因为变音符号本身是 2 个字节，在 UTF-16 中是 4 个字节）。例如，我使用以下方法创建了新角色：

      SELECT N'o'+NCHAR(0x0308); -- ö

      然后将第 3 行复制/粘贴到新的第 4 行，用我刚刚创建的替换第一个字符，并将“ab”更改为“cd”，以便能够清楚地区分输入行。这样做会导致以下错误：

      消息 4832，级别 16，状态 1，行 XXXXX
      批量加载：在数据文件中遇到意外的文件结尾。

      我希望 UTF-8 文件在给定相同字符的情况下产生相同的错误。

      公平地说，这种情况也会打破将每一行完全拉入并SUBSTRING用于将其分解的方法，如果不是通过产生错误，那么至少通过破坏SUBSTRING仍然会看到的数据o和̈ 两个单独的字符（因为他们是）。

    • 2

相关问题