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 ?
Thanks
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:

> 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,[vbcol=seagreen]
>"Paul" <anonymous@.discussions.microsoft.com> wrote:
containing[vbcol=seagreen]
that
>.
>
|||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

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:
>> 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
>.
>|||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...
> 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:
>> 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
>>.|||> 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

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

export to cvs/excel with comments attached

i'm building a web page to help our support staff run and export their sql queries faster and more easily than going through the sql server manager. i found an article at codeproject [1] that i was able to customize to be more flexible, but i'm not sure how to get it to export the results of a query along with the comments that preceed the query.

for example, i have the following sql file:

-- this query does a certain thing, and the comment can be pretty long at times, depending on how complicated the query isselect *from table where columnid > 100

would anyone be able to tell me how to edit Kolluri's export function to allow for a comment section? alternatively if his way of doing an export is out of date, i'd be interested in a newer way. most importantly, though, i need to be able to export the comments along with query results.

my ultimate goal is to have a drop down list of databases on the server, a list of query files (from a certain consolodated folder) for them to run on the chosen database, a "test query" function, and an export function that exports the comments and results.

[1] http://www.codeproject.com/aspnet/ExportClassLibrary.asp

if i'm going about this all wrong, i don't mind changing directions. :)

what i'm really trying to do is allow our support staff to speed up their query running. currently they have a folder of txt and sql files that they need to run on a certain database (which is different for different support staff). right now they open one of these txt/sql files, open sql studio, copy the query from the txt/sql, paste it into sql studio, run the query, export the results, open the results, and paste the query into the exported results file. rinse and repeat for every txt/sql file in the folder, which is time consuming and ridiculous.

this seems very repetitive and i was hoping to help them out by allowing them to go to a web page, select one or more queries from a list, pick which database they need to connect to, and i'd loop through all those queries and run them with the support staff having to only click one button to start that process. i've got almost every part of this plan working except being able to put the query in comments at the top of the exported file, but if i need to change my entire game plan to allow that, i'm willing. having the query at the top of the exported results is very important, and if i can't get this working, my whole web page is a bust.