SQL 文本被截断或一个或多个字符在目标代码页中不匹配，包括逆透视中的主键

Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

提问by l--''''''---------''''''''''''

I'm trying to import a flat file into an oledb target sql server database.

我正在尝试将平面文件导入到 oledb 目标 sql server 数据库中。

here's the field that's giving me trouble:

这是给我带来麻烦的领域：

here are the properties of that flat file connection, specifically the field:

以下是该平面文件连接的属性，特别是该字段：

here's the error message:

这是错误消息：

[Source - 18942979103_txt [424]] Error: Data conversion failed. The data conversion for column "recipient-name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[来源 - 18942979103_txt [424]] 错误：数据转换失败。列“收件人姓名”的数据转换返回状态值 4 和状态文本“文本被截断或一个或多个字符在目标代码页中不匹配。”。

What am I doing wrong?

我究竟做错了什么？

采纳答案by Chookoos

I know this is an old question. The way I solved it - after failing by increasing the length or even changing to data type text - was creating an XLSX file and importing. It accurately detected the data type instead of setting all columns as varchar(50). Turns out nvarchar(255)for that column would have done it too.

我知道这是一个老问题。我解决它的方法 - 在通过增加长度甚至更改为数据类型文本失败后 - 创建一个 XLSX 文件并导入。它准确地检测到数据类型，而不是将所有列都设置为varchar(50). 原来nvarchar(255)那个专栏也会这样做。

回答by ADH

Here is what fixed the problem for me. I did not have to convert to Excel. Just modified the DataType when choosing the data source to "text stream" (Figure 1). You can also check the "Edit Mappings" dialog to verify the change to the size (Figure 2).

这是为我解决问题的方法。我不必转换为 Excel。只是在选择数据源时将DataType 修改为“文本流”（图1）。您还可以检查“编辑映射”对话框以验证对大小的更改（图 2）。

Figure 1

图1

Figure 2

图2

回答by Eric Hrnicek

I solved this problem by ORDERING my source data (xls, csv, whatever) such that the longest text values on at the top of the file. Excel is great. use the LEN() function on your challenging column. Order by that length value with the longest value on top of your dataset. Save. Try the import again.

我通过 ORDERING 我的源数据（xls、csv 等）解决了这个问题，这样最长的文本值位于文件的顶部。Excel很棒。在具有挑战性的列上使用 LEN() 函数。按该长度值与数据集顶部的最长值排序。节省。再次尝试导入。

回答by user756366

SQL Server may be able to suggest the right data type for you (even when it does not choose the right type by default) - clicking the "Suggest Types" button (shown in your screenshot above) allows you to have SQL Server scan the source and suggest a data type for the field that's throwing an error. In my case, choosing to scan 20000 rows to generate the suggestions, and using the resulting suggested data type, fixed the issue.

SQL Server 可能会为您建议正确的数据类型（即使默认情况下它没有选择正确的类型） - 单击“建议类型”按钮（如上面的屏幕截图所示）允许您让 SQL Server 扫描源并为引发错误的字段建议数据类型。就我而言，选择扫描 20000 行以生成建议，并使用生成的建议数据类型，解决了该问题。

回答by Albert

While an approach proposed above (@chookoos, here in this q&aconvert to Excel workbook) and import resolves those kinds of issues, this solution this solution in another q&ais excellent because you can stay with your csv or tsv or txt file, and perfom the necessary fine tuning without creating a Microsoft product related solution

虽然上面提出的方法（@chookoos，在此问答中转换为 Excel 工作簿）并导入解决了这些类型的问题，但此解决方案在另一个问答中的解决方案非常好，因为您可以保留 csv 或 tsv 或 txt 文件，并执行无需创建 Microsoft 产品相关解决方案即可进行必要的微调

回答by Matt Evans

Not really a technical solution, but SQL Server 2017 flat file import is totally revamped, and imported my large-ish file with 5 clicks, handled encoding / field length issues without any input from me

不是真正的技术解决方案，但 SQL Server 2017 平面文件导入已完全改进，只需单击 5 次即可导入我的大型文件，无需我的任何输入即可处理编码/字段长度问题

回答by Pinch

SQl Management Studio data import looks at the first few rows to determine source data specs..

SQl Management Studio 数据导入查看前几行以确定源数据规格。

shift your records around so that the longest text is at top.

移动您的记录，以便最长的文本位于顶部。

回答by Anand Bhosekar

I've resolved it by checking the 'UNICODE'checkbox. Click on below Image link:

我通过选中“UNICODE”复选框解决了这个问题。点击下方图片链接：

---

---

回答by JossWs

I had similar problem against 2 different databases (DB2 and SQL), finally I solved it by using CASTin the source query from DB2. I also take advantage of using a query by adapting the source column to varcharand avoiding the useless blank spaces:

我对 2 个不同的数据库（DB2 和 SQL）有类似的问题，最后我通过CAST在 DB2 的源查询中使用来解决它。我还通过使源列适应varchar并避免无用的空格来利用查询：

CAST(RTRIM(LTRIM(COLUMN_NAME)) AS VARCHAR(60) CCSID UNICODE 
   FOR SBCS DATA)  COLUMN_NAME

The important issue here is the CCSID conversion.

这里的重要问题是 CCSID 转换。

回答by user11791703

It usually because in connection manager it may be still of 50 char , hence I have resolved the problem by going to Connection Manager--> Advanced and then change to 100 or may be 1000 if its big enough

这通常是因为在连接管理器中它可能仍然是 50 个字符，因此我通过转到连接管理器 - > 高级然后更改为 100 或可能是 1000 如果它足够大来解决问题