Import excel to MSSQL converts long numbers to +000

I am importing an excel file to MSSQL with the following script:

SELECT * INTO Z_StockImport
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=\\path\StockList.xls;Extended Properties=Excel 12.0')...[StockList$];
GO


The problem is that one of the columns has very long numbers, but they have to stay this way, so instead of the original value: 2185693631 it changes to: 2.18569e+009

It's some automatic mechanism, so I cannot do changes on the excel before importing it.. how can I specify during the import to keep the value as is OR how to update in the MSSQL afterwards that it will change back to the original number?

Thanks

Originally Posted by dflak

Sometimes, Excel will "help" you by converting long numbers to scientific notation simply because the column is too narrow to show the number. With almost any other kind of data, it will show #######, but if it is a string of numbers, it sometimes converts it to scientific notation. Try formatting the column with the number to text.

Thanks for the reply, but did you read my question?

Originally Posted by Zer0Cool

The answer to your question specifically is "you dont". dflak was trying to explain why to you.

Excel interprets numbers differently then most other systems. Most databases do not distinguish (in exports at least) between numbers and text (ie: "1" or 1). Therefor Excel interprets what it thinks the values should be (numeric or text).

What you are seeing is scientific notation, its just a shorter way of writing a large (or really small) number. Since its Excel deciding to represent it this way it doesnt matter what you do before that data is processed by Excel as it will still interpret the numeric value. Its possible you may get it to decide its text, in which case you have a new set of considerations/issues.

Another thing to consider is Excel can only handle a certain number of digits of significance, I think 15, after which point it truncates the rest placing 0's at the end (right most) of the number. So if these numbers are > 15 digits, nothing you do other than storing them as text will keep the value unchanged.

So in ending, you either export in a different format (possibly native export to Excel in which case the system exporting determines how to feed the values to the sheet, or different file format) or you format it after its imported into Excel.

I know what scientific notation is. That wasn't my question neither.
In the excel file it shows it properly (not as a scientific notation, but as a number) . When I am importing it to MSSQL through the script I wrote above IN THE MSSQL table it will convert it to scientific notion, while I want to keep it as a number.

Maybe if I would change it to TEXT and then import it, it would be ok, however it's an automatic mechanism that downloads an excel file from somewhere and then imports it daily. So I cannot do changes in the excel before importing it..

Hope it's clearer!

Thanks

Originally Posted by Zer0Cool

Ah ok, so your asking in an Excel specific forum how something works in MSSQL.

I really don't get your comment, it says it right in the title and in the first line of my post..

I am trying to import an excel file after all, so someone could know about this stuff here also.