数据转换失败的SQL [英] Data Conversion Failed SQL

问题描述

我正在使用导入和导出向导并导入了一个大型csv文件。我收到以下错误。

I am using the import and export wizard and imported a large csv file. I get the following error.

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data 
conversion for column "firms" returned status value 2 and status text "The 
value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

导入后，我使用高级选项卡进行所有调整。至于有问题的字段，我设置它是数字（8,0）。我已经多次经历这个过程并尝试了7,8,9,10和11无济于事。我将csv导入excel并查看相应的列，公司。它显示没有超过5个字符的条目。我想过把它变成DT_String但是最终需要通过对它进行平均来操纵该列。此外，搜索空格或奇怪的字符，但没有找到。

Upon importing, I use the advanced tab and make all of the adjustments. As for the field in question, I set it is numeric (8,0). I have since went through this process multiple times and tried 7,8,9,10,and 11 to no avail. I import the csv into excel and look at the respective column, firms. It shows no entry with more than 5 characters. I thought about making it DT_String but will need to manipulate that column eventually by averaging it. Also, have searched for spaces or strange characters and found none.

还有其他想法吗？

推荐答案

1）尝试在源表和目标表中将数字精度更改为数字（30,20）。

1) Try changing the Numeric precision to numeric(30,20) both in source and destination table.

2）将数据类型更改为str / wstr并在导入时调整输出列宽。它运行正常。在加载大约5 GB的大型CSV文件时，它也发生在我身上。加载后，使用 Try_convert 函数将其转换回数字并检查转换时变为null的值，然后您将找到根本原因。

2) Change the data type to str/wstr and adjust the output column width while importing. It will run fine. It happened with me as well while loading large CSV file of approx 5 GB. After load, use Try_convert function to convert it back to numeric and check the values which went null while conversion, you will find the root cause then.

这篇关于数据转换失败的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！