scientific notation to original numbers

[neetusingla7]

I have uploaded an excel file in sql server 2005 and that file contains some phone numbers in field phonenum nvarchar(50) some of which have been converted to scientific notation due to cell width and stored as scientific notation in sql server database.

how can i convert those numbers to original phone numbers using sql server.

Help in in this regard as soon as possible.

Thanks :
Neetu

[NeoPa]

I suggest a better question would be how to import the numbers properly in the first place.

Scientific notation is a floating point type and it is very likely that telephone numbers will lose digits in the conversion. This is fine for most values with a large number of digits, as the most significant digits are always maintained. Obviously all digits in a telephone number are equally significant so this should not be considered an acceptable approach.

Importing from Excel is always likely to cause problems as MS, in their wisdom, have designed things such that they know best how you want to import all the Excel data and provide minimal flexibility and control. This is a problem in the design, but not anything that's likely to change any time soon, so you need to find reliable ways around this. This generally means manipulating the data before it's (re)saved in the spreadsheet in the first place.

The most obvious way to do this I suppose, is to format the telephone numbers as they are normally formatted for display anyway. This ensures Excel sees them as string values, which ensures SQL Server will see them as string values.

[ck9663]

Or save the excel as txt file then import that text file instead ...

Good Luck!!!


~~ CK

[NeoPa]

Good thinking CK, but the reason I didn't include that as an option is that exporting from Excel to text suffers from the same problem. Some may find manipulating the text file easier than managing the worksheet to return the correct values, in which case that may be appropriate for them, but essentially the same job is being done in a text editor that you would otherwise need to do in Excel. One step seems the more straightforward, but take two if you're more comfortable doing the work outside of excel.

[ck9663]

True.

I think Microsoft is putting Excel some kind of super light version of SQL Server. Just speaking plainly of tables and database and how you can relate them. Enough to be able to process a lot of rows and tables, but not quite enough that you can do a lot. As if you can use Excel and do not need SQL Server, but not really....

It's their way of saying you still need to use all their products :)


~~ CK

[NeoPa]

There's truth in that CK, though Excel does act as more than simply a baby database app. Excel provides many pretty neat features that are outside the scope of databases completely, but there is certainly some limited capability in there for data handling. I doubt many serious database developers would want to design any such thing around a spreadsheet though. We have too many examples of recovering such projects designed by others I would guess ;-)