从Excel文件导入时出现文本被截断或一个或多个字符在目标代码页中无法匹配的问题

Question

我有一个包含四个文本列的Excel文件，其中一个名称为ShortDescription的列拥有最长的值。 我在SQL Server 2008数据库中创建了一张表，其中包含四列，而且将ShortDescription列的类型设置为NvarChar(Max)。

但是，当使用SSIS导入和导出对话框时，即使在将OnTruncation选项设置为忽略后仍然会遇到标题中提到的错误。

我尝试清空该列的数据，这样做成功了（因此我确定问题出在ShortDescription列上）。 我尝试将所有数据复制到另一个Excel工作簿中，但仍然没有成功。

有什么想法吗？

- Nour

Answer 1

我假设您正在尝试在SSIS对话框中使用Excel源导入数据？

如果是这样，问题可能是当创建Excel源时，SSIS会在电子表格开头抽取一定数量的行。如果在[ShortDescription]列中没有注意到任何太大的内容，它将默认为一个255个字符的文本列。

因此，要从包含大量数据行的列中导入数据而不截断，有两个选项：

1. 您必须确保至少一个示例行中的[ShortDescription]列包含长度超过255个字符的值。一种方法是使用REPT()函数，例如=REPT('z'，4000)，它将创建一个包含4000个字母“z”的字符串。
2. 您必须增加Jet Excel驱动程序抽取的行数以包括这样的行。可以通过增加HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel（如果系统是x64，则在HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Jet\4.0\Engines\Excel下）注册表键下TypeGuessRows的值来增加抽取的行数。

您可以在以下两个链接中获取更多信息：

• http://waxtadpole.wordpress.com/2008/04/28/hello-world/
• http://technet.microsoft.com/en-us/library/ms141683.aspx

更进一步解释，SSIS在向导背后创建3个对象：一个Excel数据源对象、一个SQL表目标对象和它们之间的数据流操作符。Excel源对象定义了源数据并独立于其他两个对象存在。因此，当它被创建时，所描述的抽样已经完成，并且确定了源列的大小。所以，在数据流操作符执行并尝试从Excel中提取数据放入您的表中时，它已经查看了一个被限制为255个字符的数据源。

- TetonSig

---

太对了！但是如果目标列设置为接受最大长度的数据，为什么会发生这种情况呢？那么源列的长度与此有什么关系吗？ - Nour

请注意，对于64位系统，相应的键为： HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel。但是显然该值只能达到16？看起来改进不大，但我还没有测试过。 - PeterX

对我来说，将最长文本的行排在顶部是有效的。@NourSabouny，我认为他是在说即使目标列设置为nvarchar(max)，中间的数据流运算符仍然出错。 - bendodge

Answer 2

我曾在将平面分隔文件导入 SQL Server 时遇到了这个问题。解决方法是更新出错列的“OutputColumnWidth”值（根据错误消息）。在导入向导的“选择数据源”表单中，我的源文件是平面文件。在最左侧的窗格中，选择“高级”。然后可以设置单个列的属性。在我的情况下，大多数列的“OutputColumnWidth”默认为“50”。我只需将其更新为不会截断平面文件中的值的较大值。

- Dean

---

与其对 DT_STR 猜测一个足够大的限制，你可以选择使用 DT_NTEXT SSIS 类型，它等同于 MSSQL 的 nvarchar(max) 或已过时的 ntext 类型。请注意，这段话已经被翻译为中文。 - eidgenossen

更新OutputColumnWidth并使用DataType DT_WSTR的组合对我很有用。在SQL Server 2014中，DT_WSTR的OutputColumnWidth可以达到4,000个Unicode字符宽度。这导致了与下面40-Love答案类似的结果。 - Jasper

你能一次性更改所有这些列吗？我有很多列，想知道是否可能一次性更改。 - Lostaunaum

这是我的问题。+1 - Lee

Answer 3

一种简单的方法是编辑要导入的文件并在第一行创建一个新行。这样它就会被始终采样。然后对于任何可能具有大于255个字符的列，只需向单元格添加255个字符即可使其正常工作。导入完成后，只需删除您添加的垃圾行即可。

- Kalel Wade

---

这对我来说是通往成功的最短路径。 - Prisoner ZERO

嗯，不确定这与解决方案有何不同。这个解决方案似乎更适合于StackExchange。~（： - dcary

Answer 4

当我尝试导入一个包含中文字符和一些无效（大型）字符串的大文件时，出现了这个错误。

该文本文件保存在UTF8格式下。

我的设置：

在常规选项上（没有更改任何内容）：

- Locale: English (United States) 
- Unicode: Unchecked
- Code Page: 65001 (UTF-8)

左侧有一个高级选项。

- DataType (for column): Unicode String [DT_WSTR] (changed)
- OutputColumnWidth: 4000 (that's the maximum) (changed)

关于数据类型映射的审核

- On Error: Ignore
- On Truncation: Ignore

我的目标列宽度为50。

使用这些设置没有出现任何错误。

- live-love

---

谢谢您发布这篇文章。在我的导入过程中，我也遇到了同样的错误信息，原因就是上面提到的问题：试图将带有外国字符的数据导入到不接受外国字符的字段中。我的解决方法是短期内从要导入的数据中删除外国字符。 - cjo30080

Answer 5

为解决这个问题，需要更改注册表组件的备用位置。

如果您无法在此处找到它，请：

Start–>RUN–>RegEdit–>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

然后查看

开始菜单->运行->RegEdit->HKEY_LOCAL_MACHINE->SOFTWARE->Wow6432Node->Microsoft->Jet->4.0->Engines->Excel

- Charles Mulwa

Answer 6

对我来说，这个链接非常有帮助: https://support.microsoft.com/en-us/kb/189897

1. 将具有超过255个字符的单元格值的行复制到Excel的开头，使该行成为Excel中的第一行
2. 更改上述链接中的注册表值。

- Jaydeep Shil

Answer 7

尝试这个 - 转到数据流任务>右键单击Excel数据源>单击显示高级编辑器>选择输入和输出属性>展开Excel源输出>展开外部列和输出列并检查错误列，然后单击那些列标题并相应地更新数据类型（大多数情况下应该是Unicode文本流[DT_NTEXT]，否则更改为该类型并尝试）。 希望这可以帮助。

- ratcoder