如何从SQL Server 2008中的float类型的Excel数据导入varchar [英] how to import data from excel of type float to varchar in sql server 2008

问题描述

我在将数据从EXCEL导入到SQL时遇到问题.在SQL的导入向导中，字段的数据类型为float(因为数据仅包含数字，IDNumber，BiometricNumber和BankAccountNumber)，现在在SQL中此字段的数据类型为varchar，并且无论何时我查询或将数据传输到其他字段表以e +位数表示的值更改ito float的格式.有人吗?

I have a problem regarding importing data from EXCEL to SQL. In import wizard in SQL, the datatype of the field/s is in float (because data contain purely numbers, IDNumber, BiometricNumber and BankAccountNumber) now the datatype of this fields in SQL is varchar and when ever i query or transfer the data to other table the format of value change ito float with e+ digit. Anyone?

推荐答案

首先，确保在连接字符串中使用 IMEX = 1 ，并且已正确设置了关联的注册表项值.有关详细信息，请参见本文.

First, ensure you are using IMEX=1 in the connection string and that the associated registry key values are set appropriately. For full details, see this article.

就我个人而言，我从来没有遇到过科学记数法的问题，但是一些人在评论中报告了类似的问题，因此值得一游.这是我发现的几对:

Personally, I've never had a problem with scientific notation but a few people have reported similar problems in comments so it might be worth trawling through them. Here's a couple I spotted:

在SQL中， Format([列名称]，"@")为[列名称]

在Excel中，列的格式(单元格格式")必须设置为常规"(不是格式为文本").

In Excel, column has to be formatted (Format cells) as "general" (not formatted as "text").

尽管我不能为他们担保；)

I cannot vouch for them, though ;)

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