DTS to Excel, $0

I am exporting some data from a view to an excel file though a DTS. $0 amounts come as -0, what is the reason for that an dhow can I fix it?

Jim,

When I try a straight export with money data equalling zero

or '$0' as strings, either from SQL Server 2005 or 2000 (using

the Import/Export Data wizard in both cases), I get $0.00

or $0 in the Excel file.

Can you be more specific about what you are seeing? What is the

view definition? Is the problem column a table column declared

as money/smallmoney in SQL Server, an expression, or what? Are

you using a wizard or another method to do the export? When you

select from the view in SQL Server, do you see .0000, or something

else?

You haven't given us much to go on.

Steve Kass

Drew University

www.stevekass.com

JIM.H.@.discussions.microsoft.com wrote:

> I am exporting some data from a view to an excel file though a DTS. $0

> amounts come as -0, what is the reason for that an dhow can I fix it?

>

>

|||

DTS is using the view. When I use the view I do not see -0. But the exported data has -0. If I format the column in excel for 2 decimals, it becomes 0.00 so the negative drops. Maybe DTS considers a long decimal after the point and assumes it is negative.

|||

You haven't really answered some of the relevant questions:

Is the problem column a table column declared as money/smallmoney

in SQL Server, an expression, or what? Are you using a wizard

or another method to do the export?

SK

JIM.H.@.discussions.microsoft.com wrote:

> DTS is using the view. When I use the view I do not see -0. But the

> exported data has -0. If I format the column in excel for 2 decimals, it

> becomes 0.00 so the negative drops. Maybe DTS considers a long decimal

> after the point and assumes it is negative.

>

>

|||Thanks for your help. The column is a float in the table. I used the DTS wizard.|||

Apparently the number is a very small negative number. When such a number

is represented in Excel with only a few decimal places, Excel can't show

its absolute value differently from zero, but Excel could display its

sign correctly, yet doesn't. Excel displays the number as negative for

some formats and not for others. I would consider this a

bug, and you might wish to report it. (I don't know where, but if you

post this in an Excel newsgroup someone will probably tell you.)

I reproduced the fleeting - as follows:

In an Excel cell, type -0.00000001, the format the cell in these ways:

Number (2 decimal places). The result is 0.00 [not negative]

Currency (2 decimal places, with () for negatives. The result is $(0.00) [negative]

Currency (2 decimal places, with - for negative values. The result is $0.00 [not negative]

In particular, the currency marker shouldn't affect whether or not the number is shown as negative.

Steve Kass

Drew University

www.stevekass.com

JIM.H.@.discussions.microsoft.com wrote:

> Thanks for your help. The column is a float in the table. I used the DTS

> wizard.

>