Export to Excel Spreadsheet

I am going to export a numeric(14,2) field that containing
the Balance to a CSV file. It will be opened by the
Finance Staff with Excel.
They would like to get the Balance Field opened as
Currency Field (With $ sign). Is it possible for us to
convert the Balance Field or do some manuipulation so that
it means her need ?
ThanksYou might try just prefixing it with a $ e.g. Instead of
SELECT numeric_column
Use
SELECT '$' + RTRIM(numeric_column)
Not sure if Excel will automatically recognize it as currency as a string,
but that's probably as close as you're going to get--SQL Server doesn't have
any clue what Excel is, never mind what Excel commands you would embed to
force the confusion. Besides, since you're exporting to CSV, there's
nothing you can embed anyway... It won't process any Excel commands you
include in a plain text CSV file...
On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0$a601280a@.phx.gbl,
"Paul" <anonymous@.discussions.microsoft.com> wrote:

> I am going to export a numeric(14,2) field that containing
> the Balance to a CSV file. It will be opened by the
> Finance Staff with Excel.
> They would like to get the Balance Field opened as
> Currency Field (With $ sign). Is it possible for us to
> convert the Balance Field or do some manuipulation so that
> it means her need ?
> Thanks|||Thank you for your advice and it seems work properly.
However, I would like to know why we have to use RTRIM. I
have attempted not to use RTRIM, it gives me an error
message.

>--Original Message--
>You might try just prefixing it with a $ e.g. Instead of
>SELECT numeric_column
>Use
>SELECT '$' + RTRIM(numeric_column)
>Not sure if Excel will automatically recognize it as
currency as a string,
>but that's probably as close as you're going to get--SQL
Server doesn't have
>any clue what Excel is, never mind what Excel commands
you would embed to
>force the confusion. Besides, since you're exporting to
CSV, there's
>nothing you can embed anyway... It won't process any
Excel commands you
>include in a plain text CSV file...
>
>On 3/13/05 7:12 PM, in article 7c4301c5282a$88d2c8e0
$a601280a@.phx.gbl,
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
>
containing[vbcol=seagreen]
that[vbcol=seagreen]
>.
>|||SQL needs to convert your numeric_column to a string/char/varchar before it
can perform string manipulations on it. RTRIM does the job, as will CAST(
... AS VARCHAR(xx)).
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:7c5f01c52833$8a494d30$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you for your advice and it seems work properly.
> However, I would like to know why we have to use RTRIM. I
> have attempted not to use RTRIM, it gives me an error
> message.
>
> currency as a string,
> Server doesn't have
> you would embed to
> CSV, there's
> Excel commands you
> $a601280a@.phx.gbl,
> containing
> that|||> However, I would like to know why we have to use RTRIM.
RTRIM implicitly casts the numeric value as a string. There are several
other functions you can use to do this, such as LTRIM, or you can cast it
explicitly using CAST or CONVERT (I just find those more cumbersome).
If you don't change the numeric value to a varchar, SQL Server will cock up
an eyebrow and look at you funny, asking how you expect it to add '$' to
4.75... They are incompatible types for the addition and/or string
concatenation operator.
A