1

I have a problem regarding importing data from Excel to SQL. In the 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 changes to float with e+ digit. Anyone?

Improve this question
4
  • have you tried playing around with "Format cells" in excel? Commented Oct 11, 2011 at 5:52
  • haven't but i give it a try thanks. Commented Oct 11, 2011 at 5:54
  • 1
    If anyone is wondering why a data element BankAccountNumber is of type FLOAT, consider that Access (ACE, Jet, whatever) SQL for Excel has but one numeric data type ;) Commented Oct 11, 2011 at 7:22
  • thanks I follow Mi Mee's advice and realize to change format of that certain column in excel. Commented Oct 12, 2011 at 7:01

2 Answers 2

Reset to default
2

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 ;)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

i have try both formatted as general and text, but MSSQL still set it as float!!!
@Huei: But have you set IMEX=1, set the registry settings as appropriate, etc? The link to the article is broken but googling "IMEX=1 " and my handle (OneDayWhen) should reveal more details e.g. here: instantpages.ltd.uk/ADODB_WP.htm
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.

Improve this answer

1 Comment

didn't work..:(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.