Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maxdanger
Starting Member
3 Posts |
 Posted - 2010-04-05 : 13:52:26
|
| I am exporting a large amount of data from SQL Server 2005 to Excel. Most of the data appears as I want it to- without the exponential notation. Any values smaller than 0.0001 (i.e. four zeros to the right of the decimal) are displayed as, for example, 7E-05 rather than 0.00007.I know that I could use a fixed format, however the values in this field have a broad range of precisions (e.g. 1250, 1.3, 0.00345, 0.000001) and I would like to maintain the original number formats.I also realize that Excel can convert exponential notation to non-exponential, however I am including a '<' sign with many values. That being said, I am concatenating a string (with '<') with a numeric field into a new string field before exporting. Unfortunately when Server concatenates this fields it does so with the exponential format so that my values appear as <1E-05.I know that with Oracleyou can use SQLPLUS to set column formats for queries. Is there anything like that which can be used in MS SQL Server?Thanks in advance...gabriel |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-04-05 : 19:01:13
|
| If you are including a > in your resultset, then it seems that you are returning varchar data instead of numeric data. Is there some way to return truly numeric data and then have the application perform any formatting?=======================================There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980) |
 |
|
|
maxdanger
Starting Member
3 Posts |
Posted - 2010-04-06 : 08:23:25
|
| You are right, I am returning varchar data.I found a really useful procedure that someone wrote that creates a cross table. I could potentially create two cross tables, one of all the values and one of all occurrences of the '<' sign, and then use a formula in Excel to combine them. We used to store all of our data in Excel, however we have moved to Server so that we could ultimately save time. I want to make this as easy for people as possible. It just seems like one of those things that shouldn't be an issue, or you should be able to set at what point to the left/right of the decimal point scientific notation is used... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-04-06 : 12:13:11
|
| just expand the column width in excel |
|
|
|
maxdanger
Starting Member
3 Posts |
Posted - 2010-04-07 : 11:14:51
|
| it's not an excel issue, because it is coming in as text, i.e. "<1E-05". Excel will not understand this as a number and therefore cannot convert it without the user separating the the < from the string. It is a SQL Server issue. |
|
|
|
|
|
|
|
|
Remove Exponential Notation in Server 2005