how to import data from excel of type float to var

问题:

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?

回答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:

In SQL, Format([COLUMN NAME], ‘@’) As [COLUMN NAME]

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

I cannot vouch for them, though ;)

回答2:

I changed the data type of the excel column to 'Text' when it was 'General' and the 32 bit SQL Server Import Tool picked it up as an nvarchar.