时间戳

首页 > 解决方案 > 如何在 SQL Server 2017 中使用批量插入和 XML 格式文件加载 UTF-8 CSV 文件

sql-server - 如何在 SQL Server 2017 中使用批量插入和 XML 格式文件加载 UTF-8 CSV 文件

问题描述

经过多次尝试，我发现自（2016 年？）以来，通过使用选项和，SQL server 2017可以通过 Bulk Insert 加载 UTF-8 编码的 CSV 文件，正如其他一些问题中所解释的那样。CODEPAGE = 65001DATAFILETYPE = 'Char'

使用 XML 格式文件时似乎不起作用的方法是相同的。我通过仍然使用 CODEPAGE 和 DATAFILETYPE 选项进行了尝试，并且省略了这些选项。我已经用最简单的数据集试过了。一行一列，包含一些带有 UTF-8 字符的文本。

这是我正在使用的 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 ID="STREET" xsi:type="NCharTerm" TERMINATOR="\r\n" MAX_LENGTH="1000" COLLATION="Latin1_General_CS_AS_WS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="STREET" NAME="STREET" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

即使源数据只包含一些带有 1 个特殊字符的文本，最终结果看起来像这样：慊潫ⵢ瑓晥慦⵮瑓慲鿃⁳㐱 当使用xsi:type="CharTerm"而不是xsi:type="NCharTerm"结果看起来像这样： ... -Straßs...

我做错了什么，还是没有为 XML 格式文件正确实现 UTF-8 支持？

标签： sql-servercsvbulkinsertsql-server-2017

解决方案

在玩了这个之后，我找到了解决方案。

笔记

  • 这适用于有或没有 BOM 标题。这是无关紧要的。
  • 罪魁祸首是在 XML 文件中使用了 COLLATION 参数。省略它解决了编码问题。我对为什么会这样有一个直观的感觉，但也许有更多洞察力的人可以在评论中解释......
  • 该DATAFILETYPE = 'char'选项似乎没有必要。
  • 在 XML 文件中，xsi:typefor 字段需要是 CharTerm，而不是NCharTerm。
  • 这适用于 \r\n、\n 或 \r。只要您正确设置了 TERMINATOR，它就可以工作。不需要 \n\0 变体（这甚至会破坏功能，因为这不是 UTF-16 或 UCS-2）。

您可以在下面找到一个易于重复使用的概念验证...

数据.txt 

ß
ß
ß

桌子

CREATE TABLE [dbo].[TEST](
    TEST [nvarchar](500) NULL
)

格式文件.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 ID="1" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="TEST" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

批量插入

    bulk insert TEST..TEST
    from 'data.txt'
    with (formatfile = 'formatfile.xml', CODEPAGE = 65001)

推荐阅读