从 Excel 导入到 SQL Server 2008 时转换数据类型时出错 [英] Error converting data types when importing from Excel to SQL Server 2008

问题描述

每次尝试将 Excel 文件导入 SQL Server 时，都会遇到特定错误.当我尝试编辑映射时，所有数字字段的默认值都是浮点数.我的表中的所有字段都没有小数，它们也不是货币数据类型.它们只有 8 位数字.但是，由于我不希望主键是 int 时将其存储为浮点数，我该如何解决这个问题?它给了我某种截断错误，如果需要，我会发布屏幕截图.这是一个常见问题吗?

Every time that I try to import an Excel file into SQL Server I'm getting a particular error. When I try to edit the mappings the default value for all numerical fields is float. None of the fields in my table have decimals in them and they aren't a money data type. They're only 8 digit numbers. However, since I don't want my primary key stored as a float when it's an int, how can I fix this? It gives me a truncation error of some sort, I'll post a screen cap if needed. Is this a common problem?

应该注意的是，我无法导入 Excel 2007 文件(我想我已经找到了解决方法)，但是即使我尝试导入 .xls 文件，每个包含数字的值都会自动导入为浮点数，当我尝试更改它，但出现错误.

It should be noted that I cannot import Excel 2007 files (I think I've found the remedy to this), but even when I try to import .xls files every value that contains numerals is automatically imported as a float and when I try to change it I get an error.

http://imgur.com/4204g

推荐答案

SSIS 不会隐式转换数据类型，因此您需要显式转换.Excel 连接管理器只能处理几种数据类型，它会尝试根据文件的前几行做出最佳猜测.这在 SSIS 文档中完全记录.

SSIS doesn't implicitly convert data types, so you need to do it explicitly. The Excel connection manager can only handle a few data types and it tries to make a best guess based on the first few rows of the file. This is fully documented in the SSIS documentation.

您有多种选择:

• 将目标数据类型更改为浮动
• 使用导入向导加载到数据类型为浮点的暂存"表，然后使用 CAST 或 CONVERTINSERT 到实际目标表> 转换数据
• 创建一个 SSIS 包并使用数据转换转换来转换数据

• Change your destination data type to float
• Load to a 'staging' table with data type float using the Import Wizard and then INSERT into the real destination table using CAST or CONVERT to convert the data
• Create an SSIS package and use the Data Conversion transformation to convert the data

您可能还想注意导入向导文档中关于数据类型映射的注释.

You might also want to note the comments in the Import Wizard documentation about data type mappings.

这篇关于从 Excel 导入到 SQL Server 2008 时转换数据类型时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！