Currency Format with Comma Delimination

Is there a way to convert a field to Currency format with
a comma as a seperator.
ie
50000 would be $50,000
1234567 would be $1,234,567
etc.
I am just looking to do this via the SELECT statement.
Any suggestions would be helpful.
Use CONVERT function to do this.
Try something as:
SELECT '$'+ CONVERT(VARCHAR(50), money_column, 1) AS column_name
FROM <table>
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||That works good to put the $ sign in. But is there also a
way to put the commas in?

>--Original Message--
>Use CONVERT function to do this.
>Try something as:
>SELECT '$'+ CONVERT(VARCHAR(50), money_column, 1) AS
column_name
>FROM <table>
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>.
>
|||yes,
-- if column has datatype money then you can use query i've mentioned
earlier.
-- or else you will explicitly have to convert numeric value to money.
-- see 2nd query below.
create table t(money_column money)
insert into t values (123456)
SELECT '$' + CONVERT(VARCHAR(50), money_column, 1) AS column_name
FROM t
-- query if datatype is not meney
create table t(money_column int)
insert into t values (123456)
SELECT '$' + CONVERT(VARCHAR(50), cast(money_column as money), 1) AS
column_name
FROM t
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||

Quote:

Originally posted by Vishal Parkar
Use CONVERT function to do this.
Try something as:
SELECT '$'+ CONVERT(VARCHAR(50), money_column, 1) AS column_name
FROM <table>
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

This code works, but when I insert the markup that I need (*1.40) I get the dollar signs and 4 leading 0's at the end. IE, $4.500000
How do I get these 0's to drop? Here is the code
SELECT Product, [BU/ST], Pack, '$' + CONVERT(VARCHAR(50), UnitPrice * 1.40, 1) AS UnitPrice
FROM dbo.Regional
ORDER BY Flower

csv import which runs stored procedure

Are there any tools which allow you to take a comma delimted file and run a stored procedure for each line in the file, passing the values in the file as parameters? i.e.

CREATE PROCEDURE add_user
@.name varchar(20),
@.first_name varchar(20),
@.last_name varchar(20)
AS
/* do some lookups */
/* validate input */
insert into users (name, first_name, last_name) values (@.name, @.first_name, @.last_name)

Then a comma delimited file such as
bpeikes,Benjamin,Peikes
rbobly,Robert,Bobly
trichard,Tom,Richards

I need a tool which can execute the stored procedure once for each line in the file. I could write one myself, but I'd like to know if any of the tools which come with SQL Server will handle it.

My preferred method would be to first load the csv file data into a 'staging' table, and then have a procedure that validates, cleanses, massages, and transfers the data to the working table(s).

And that can easily be accomplished using a SQL Agent Job, scheduled to run 'on schedule', or available to run 'as needed'.

|||

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

|||

You could add a trigger to your target table that calls your stored procedure, bcp the data into the target table and it should trigger storedproc calls for each insert.

Hope that helps,

John

|||

bpeikes wrote:

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

Maybe you should use the sql server integration services SSIS

instead

CSV from SQL

Hi guys,
I need to get a CSV(comma seperated file) generated for a table on MS SQL Server 2000 database.
Can anyone help me! How should I solve this issue!
Thanks a lot in advance
AsthaIn sql query analyzer after getting the result, File -> save and change the following options
Save as type ".CSV"
Column delimiter as "Comma Separated (CSV)"

Hope it solves your problem

Originally posted by astha_raj
Hi guys,

I need to get a CSV(comma seperated file) generated for a table on MS SQL Server 2000 database.

Can anyone help me! How should I solve this issue!

Thanks a lot in advance
Astha|||Originally posted by abidulla
In sql query analyzer after getting the result, File -> save and change the following options
Save as type ".CSV"
Column delimiter as "Comma Separated (CSV)"

Hope it solves your problem

Thanks for your help - But what I need to actually do is to generate the CSV file for the query result dynamically - Something by which I can create a file to run as a job to do the process automatically at regular intervals - as the table values changes frequently

Can it be done programitacally some how - may be in Cold Fusion or something?

Please guide me if you have any idea.

Thanks anyways
Astha|||I know that you can use the BULK INSERT command to get CSV into a SQL Server table.

I believe that it is just an argument in order to change from an import to an export.

This BULK INSERT command is basically the same thing as BCP, if i understand it correctly..

if this doesnt work, then you can just use BCP throgh the CLI and xp_cmdshell

cheers and good luck|||Use OSQL utility with -s option, refer to BOL for more information.|||Originally posted by Satya
Use OSQL utility with -s option, refer to BOL for more information.

Thank you very much - I was able to do my stuff with bcp utility.

Thanks again

astha|||I have used OSQL many times before to do so. Ofcourse BCP also does the thing.

CSV Export problem

Hi
I wish to export a reportas CSV format, however when I open it in Excel it is assuming it is fixed width seperated, when it clearly is comma seperated! Is there any way I can get it to do it right?
I am using Excel 2000 to view the csv output, and it is being triggered via a URL on a ASP.NET application.
Thanks for any help.
Jan
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=0388cedae91e417186f2d4a5cdfc8406
*****************************************I have answered my own question, by applying the parameter
rc:Encoding=ASCII
to the URL!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5264
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6357bd9e0da54182b1994c9908ce943b
*****************************************

CSV Export delimited not fixed

Since RS exports csv files using a comma delimiter, Excel doesn't by
default, open the file correctly. Is there a way to export to csv using a
format which excel will handle correctly using its default settings?
Currently the user must open the file from within Excel and specify
"Delimited" not "Fixed Width" and go through the Text Import Wizard.
Thanks in advance,
Ryan OpferIs there a problem with Excel format?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> Since RS exports csv files using a comma delimiter, Excel doesn't by
> default, open the file correctly. Is there a way to export to csv using a
> format which excel will handle correctly using its default settings?
> Currently the user must open the file from within Excel and specify
> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> Thanks in advance,
> Ryan Opfer
>|||Thanks Bruce for the quick reply.
The Excel format is fine provided you go through the Text Import Wizard of
Excel and specify that the file is a comma delimited csv file not Fixed
Width. I would like to figure out how to Export from RS using a format in
which Excel will natively open the file as a comma delimited file and
thereby bypass having to have the user go through the Text Import Wizard.
There was a post back in November (Jan Bodey) that said she fixed this by
specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
could have, I wouldn't expect the user to put this in the link anyway.
Does that make more sense?
Thanks,
Ryan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Is there a problem with Excel format?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv using
>> a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>|||I just noticed how I worded the subject title. By delimited not fixed, I
ment: default to "Delimited" not "Fixed Width".
Sorry for the confusion.
Ryan
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> Since RS exports csv files using a comma delimiter, Excel doesn't by
> default, open the file correctly. Is there a way to export to csv using a
> format which excel will handle correctly using its default settings?
> Currently the user must open the file from within Excel and specify
> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> Thanks in advance,
> Ryan Opfer
>|||You can render your report as an Excel file. No reason to use the CSV format
in the first place.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Thanks Bruce for the quick reply.
> The Excel format is fine provided you go through the Text Import Wizard of
> Excel and specify that the file is a comma delimited csv file not Fixed
> Width. I would like to figure out how to Export from RS using a format in
> which Excel will natively open the file as a comma delimited file and
> thereby bypass having to have the user go through the Text Import Wizard.
> There was a post back in November (Jan Bodey) that said she fixed this by
> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
> could have, I wouldn't expect the user to put this in the link anyway.
> Does that make more sense?
> Thanks,
> Ryan
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is there a problem with Excel format?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv using
>> a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>>
>|||An Excel format is what I tried first but I couldn't get around how Excel
sees the document (it's too smart). Excel sees the document map and puts it
on sheet 1 then on sheet 2 (which has the main table data) it shows drill
down. Ultimately this file will need to be imported into an MS SQL table so
it needs to be simple.
Excel would work fine if I can remove the document map (obviously I can do
this by just removing the document map from the report...and maybe that's
something the user will have to live with) and drill down capabilities (the
user can't live without this though), but by then I'd pretty much be looking
at a plain csv file anyway.
The key here is the user will be doing all this and just sending us back a
basic csv or Excel file for the sql import so thereby has to be a simple
process.
From your reply's, I take it that RS can't defign how it exports to csv or
for that matter Excel.
Thanks for the help,
Ryan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
> You can render your report as an Excel file. No reason to use the CSV
> format in the first place.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> Thanks Bruce for the quick reply.
>> The Excel format is fine provided you go through the Text Import Wizard
>> of Excel and specify that the file is a comma delimited csv file not
>> Fixed Width. I would like to figure out how to Export from RS using a
>> format in which Excel will natively open the file as a comma delimited
>> file and thereby bypass having to have the user go through the Text
>> Import Wizard.
>> There was a post back in November (Jan Bodey) that said she fixed this by
>> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
>> could have, I wouldn't expect the user to put this in the link anyway.
>> Does that make more sense?
>> Thanks,
>> Ryan
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is there a problem with Excel format?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv
>> using a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>>
>>
>|||Here is an idea for you. Have an additional report that is very plain and
exports to Excel well. Have it be the same as the other on as far as the
data is concerned and the report parameters. Then add a link that uses jump
to URL and have it render the second shadow report as Excel passing it the
parameters from the current report. I call it a shadow report because you
can set it to not be visible in list view so the user never sees it. The
only way it gets run is when they click on the link.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
> An Excel format is what I tried first but I couldn't get around how Excel
> sees the document (it's too smart). Excel sees the document map and puts
it
> on sheet 1 then on sheet 2 (which has the main table data) it shows drill
> down. Ultimately this file will need to be imported into an MS SQL table
so
> it needs to be simple.
> Excel would work fine if I can remove the document map (obviously I can do
> this by just removing the document map from the report...and maybe that's
> something the user will have to live with) and drill down capabilities
(the
> user can't live without this though), but by then I'd pretty much be
looking
> at a plain csv file anyway.
> The key here is the user will be doing all this and just sending us back a
> basic csv or Excel file for the sql import so thereby has to be a simple
> process.
> From your reply's, I take it that RS can't defign how it exports to csv or
> for that matter Excel.
> Thanks for the help,
> Ryan
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
> > You can render your report as an Excel file. No reason to use the CSV
> > format in the first place.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> >> Thanks Bruce for the quick reply.
> >>
> >> The Excel format is fine provided you go through the Text Import Wizard
> >> of Excel and specify that the file is a comma delimited csv file not
> >> Fixed Width. I would like to figure out how to Export from RS using a
> >> format in which Excel will natively open the file as a comma delimited
> >> file and thereby bypass having to have the user go through the Text
> >> Import Wizard.
> >>
> >> There was a post back in November (Jan Bodey) that said she fixed this
by
> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
> >> could have, I wouldn't expect the user to put this in the link anyway.
> >>
> >> Does that make more sense?
> >>
> >> Thanks,
> >>
> >> Ryan
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
> >> Is there a problem with Excel format?
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> >> Since RS exports csv files using a comma delimiter, Excel doesn't by
> >> default, open the file correctly. Is there a way to export to csv
> >> using a
> >> format which excel will handle correctly using its default settings?
> >> Currently the user must open the file from within Excel and specify
> >> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> >>
> >> Thanks in advance,
> >>
> >> Ryan Opfer
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks Bruce for all the help, I do appreciate it. This sounds like an
adequate solution that I think will work.
It does seems strange though (although seemingly common for MS) to have RS
export to csv using a format that Excel doesn't natively understand by
default. I would have hoped that whatever MS decides to set as a default
format for csv would be the same between MS applications. I guess that's
just something we should all be use to by now anyway.
Ryan Opfer
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1VflVAIFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Here is an idea for you. Have an additional report that is very plain and
> exports to Excel well. Have it be the same as the other on as far as the
> data is concerned and the report parameters. Then add a link that uses
> jump
> to URL and have it render the second shadow report as Excel passing it the
> parameters from the current report. I call it a shadow report because you
> can set it to not be visible in list view so the user never sees it. The
> only way it gets run is when they click on the link.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
>> An Excel format is what I tried first but I couldn't get around how Excel
>> sees the document (it's too smart). Excel sees the document map and puts
> it
>> on sheet 1 then on sheet 2 (which has the main table data) it shows drill
>> down. Ultimately this file will need to be imported into an MS SQL table
> so
>> it needs to be simple.
>> Excel would work fine if I can remove the document map (obviously I can
>> do
>> this by just removing the document map from the report...and maybe that's
>> something the user will have to live with) and drill down capabilities
> (the
>> user can't live without this though), but by then I'd pretty much be
> looking
>> at a plain csv file anyway.
>> The key here is the user will be doing all this and just sending us back
>> a
>> basic csv or Excel file for the sql import so thereby has to be a simple
>> process.
>> From your reply's, I take it that RS can't defign how it exports to csv
>> or
>> for that matter Excel.
>> Thanks for the help,
>> Ryan
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
>> > You can render your report as an Excel file. No reason to use the CSV
>> > format in the first place.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> >> Thanks Bruce for the quick reply.
>> >>
>> >> The Excel format is fine provided you go through the Text Import
>> >> Wizard
>> >> of Excel and specify that the file is a comma delimited csv file not
>> >> Fixed Width. I would like to figure out how to Export from RS using a
>> >> format in which Excel will natively open the file as a comma delimited
>> >> file and thereby bypass having to have the user go through the Text
>> >> Import Wizard.
>> >>
>> >> There was a post back in November (Jan Bodey) that said she fixed this
> by
>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if
>> >> I
>> >> could have, I wouldn't expect the user to put this in the link anyway.
>> >>
>> >> Does that make more sense?
>> >>
>> >> Thanks,
>> >>
>> >> Ryan
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> Is there a problem with Excel format?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> >> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> >> default, open the file correctly. Is there a way to export to csv
>> >> using a
>> >> format which excel will handle correctly using its default settings?
>> >> Currently the user must open the file from within Excel and specify
>> >> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Ryan Opfer
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||For some reason, Excel doesn't understand Unicode CSV files by default. If
we did an ANSI CSV file (which you can specify in the DeviceInfo) then we
would lose data in certain scenarios. We could look at all of the characters
in the file and see whether we could use ANSI without data loss but this is
a lot of work.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:%23ETGwjAIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks Bruce for all the help, I do appreciate it. This sounds like an
> adequate solution that I think will work.
>
> It does seems strange though (although seemingly common for MS) to have RS
> export to csv using a format that Excel doesn't natively understand by
> default. I would have hoped that whatever MS decides to set as a default
> format for csv would be the same between MS applications. I guess that's
> just something we should all be use to by now anyway.
>
> Ryan Opfer
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u1VflVAIFHA.3536@.TK2MSFTNGP14.phx.gbl...
>> Here is an idea for you. Have an additional report that is very plain and
>> exports to Excel well. Have it be the same as the other on as far as the
>> data is concerned and the report parameters. Then add a link that uses
>> jump
>> to URL and have it render the second shadow report as Excel passing it
>> the
>> parameters from the current report. I call it a shadow report because you
>> can set it to not be visible in list view so the user never sees it. The
>> only way it gets run is when they click on the link.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
>> An Excel format is what I tried first but I couldn't get around how
>> Excel
>> sees the document (it's too smart). Excel sees the document map and puts
>> it
>> on sheet 1 then on sheet 2 (which has the main table data) it shows
>> drill
>> down. Ultimately this file will need to be imported into an MS SQL table
>> so
>> it needs to be simple.
>> Excel would work fine if I can remove the document map (obviously I can
>> do
>> this by just removing the document map from the report...and maybe
>> that's
>> something the user will have to live with) and drill down capabilities
>> (the
>> user can't live without this though), but by then I'd pretty much be
>> looking
>> at a plain csv file anyway.
>> The key here is the user will be doing all this and just sending us back
>> a
>> basic csv or Excel file for the sql import so thereby has to be a simple
>> process.
>> From your reply's, I take it that RS can't defign how it exports to csv
>> or
>> for that matter Excel.
>> Thanks for the help,
>> Ryan
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
>> > You can render your report as an Excel file. No reason to use the CSV
>> > format in the first place.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> >> Thanks Bruce for the quick reply.
>> >>
>> >> The Excel format is fine provided you go through the Text Import
>> >> Wizard
>> >> of Excel and specify that the file is a comma delimited csv file not
>> >> Fixed Width. I would like to figure out how to Export from RS using
>> >> a
>> >> format in which Excel will natively open the file as a comma
>> >> delimited
>> >> file and thereby bypass having to have the user go through the Text
>> >> Import Wizard.
>> >>
>> >> There was a post back in November (Jan Bodey) that said she fixed
>> >> this
>> by
>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if
>> >> I
>> >> could have, I wouldn't expect the user to put this in the link
>> >> anyway.
>> >>
>> >> Does that make more sense?
>> >>
>> >> Thanks,
>> >>
>> >> Ryan
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> Is there a problem with Excel format?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> >> Since RS exports csv files using a comma delimiter, Excel doesn't
>> >> by
>> >> default, open the file correctly. Is there a way to export to csv
>> >> using a
>> >> format which excel will handle correctly using its default
>> >> settings?
>> >> Currently the user must open the file from within Excel and specify
>> >> "Delimited" not "Fixed Width" and go through the Text Import
>> >> Wizard.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Ryan Opfer
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>

CSV Export delimited not fixed

Since RS exports csv files using a comma delimiter, Excel doesn't by
default, open the file correctly. Is there a way to export to csv using a
format which excel will handle correctly using its default settings?
Currently the user must open the file from within Excel and specify
"Delimited" not "Fixed Width" and go through the Text Import Wizard.
Thanks in advance,
Ryan OpferIs there a problem with Excel format?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> Since RS exports csv files using a comma delimiter, Excel doesn't by
> default, open the file correctly. Is there a way to export to csv using a
> format which excel will handle correctly using its default settings?
> Currently the user must open the file from within Excel and specify
> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> Thanks in advance,
> Ryan Opfer
>|||Thanks Bruce for the quick reply.
The Excel format is fine provided you go through the Text Import Wizard of
Excel and specify that the file is a comma delimited csv file not Fixed
Width. I would like to figure out how to Export from RS using a format in
which Excel will natively open the file as a comma delimited file and
thereby bypass having to have the user go through the Text Import Wizard.
There was a post back in November (Jan Bodey) that said she fixed this by
specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
could have, I wouldn't expect the user to put this in the link anyway.
Does that make more sense?
Thanks,
Ryan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Is there a problem with Excel format?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv using
>> a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>|||I just noticed how I worded the subject title. By delimited not fixed, I
ment: default to "Delimited" not "Fixed Width".
Sorry for the confusion.
Ryan
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> Since RS exports csv files using a comma delimiter, Excel doesn't by
> default, open the file correctly. Is there a way to export to csv using a
> format which excel will handle correctly using its default settings?
> Currently the user must open the file from within Excel and specify
> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> Thanks in advance,
> Ryan Opfer
>|||You can render your report as an Excel file. No reason to use the CSV format
in the first place.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Thanks Bruce for the quick reply.
> The Excel format is fine provided you go through the Text Import Wizard of
> Excel and specify that the file is a comma delimited csv file not Fixed
> Width. I would like to figure out how to Export from RS using a format in
> which Excel will natively open the file as a comma delimited file and
> thereby bypass having to have the user go through the Text Import Wizard.
> There was a post back in November (Jan Bodey) that said she fixed this by
> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
> could have, I wouldn't expect the user to put this in the link anyway.
> Does that make more sense?
> Thanks,
> Ryan
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is there a problem with Excel format?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv using
>> a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>>
>|||An Excel format is what I tried first but I couldn't get around how Excel
sees the document (it's too smart). Excel sees the document map and puts it
on sheet 1 then on sheet 2 (which has the main table data) it shows drill
down. Ultimately this file will need to be imported into an MS SQL table so
it needs to be simple.
Excel would work fine if I can remove the document map (obviously I can do
this by just removing the document map from the report...and maybe that's
something the user will have to live with) and drill down capabilities (the
user can't live without this though), but by then I'd pretty much be looking
at a plain csv file anyway.
The key here is the user will be doing all this and just sending us back a
basic csv or Excel file for the sql import so thereby has to be a simple
process.
From your reply's, I take it that RS can't defign how it exports to csv or
for that matter Excel.
Thanks for the help,
Ryan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
> You can render your report as an Excel file. No reason to use the CSV
> format in the first place.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> Thanks Bruce for the quick reply.
>> The Excel format is fine provided you go through the Text Import Wizard
>> of Excel and specify that the file is a comma delimited csv file not
>> Fixed Width. I would like to figure out how to Export from RS using a
>> format in which Excel will natively open the file as a comma delimited
>> file and thereby bypass having to have the user go through the Text
>> Import Wizard.
>> There was a post back in November (Jan Bodey) that said she fixed this by
>> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
>> could have, I wouldn't expect the user to put this in the link anyway.
>> Does that make more sense?
>> Thanks,
>> Ryan
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is there a problem with Excel format?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv
>> using a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>>
>>
>|||Here is an idea for you. Have an additional report that is very plain and
exports to Excel well. Have it be the same as the other on as far as the
data is concerned and the report parameters. Then add a link that uses jump
to URL and have it render the second shadow report as Excel passing it the
parameters from the current report. I call it a shadow report because you
can set it to not be visible in list view so the user never sees it. The
only way it gets run is when they click on the link.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
> An Excel format is what I tried first but I couldn't get around how Excel
> sees the document (it's too smart). Excel sees the document map and puts
it
> on sheet 1 then on sheet 2 (which has the main table data) it shows drill
> down. Ultimately this file will need to be imported into an MS SQL table
so
> it needs to be simple.
> Excel would work fine if I can remove the document map (obviously I can do
> this by just removing the document map from the report...and maybe that's
> something the user will have to live with) and drill down capabilities
(the
> user can't live without this though), but by then I'd pretty much be
looking
> at a plain csv file anyway.
> The key here is the user will be doing all this and just sending us back a
> basic csv or Excel file for the sql import so thereby has to be a simple
> process.
> From your reply's, I take it that RS can't defign how it exports to csv or
> for that matter Excel.
> Thanks for the help,
> Ryan
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
> > You can render your report as an Excel file. No reason to use the CSV
> > format in the first place.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> >> Thanks Bruce for the quick reply.
> >>
> >> The Excel format is fine provided you go through the Text Import Wizard
> >> of Excel and specify that the file is a comma delimited csv file not
> >> Fixed Width. I would like to figure out how to Export from RS using a
> >> format in which Excel will natively open the file as a comma delimited
> >> file and thereby bypass having to have the user go through the Text
> >> Import Wizard.
> >>
> >> There was a post back in November (Jan Bodey) that said she fixed this
by
> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
> >> could have, I wouldn't expect the user to put this in the link anyway.
> >>
> >> Does that make more sense?
> >>
> >> Thanks,
> >>
> >> Ryan
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
> >> Is there a problem with Excel format?
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> >> Since RS exports csv files using a comma delimiter, Excel doesn't by
> >> default, open the file correctly. Is there a way to export to csv
> >> using a
> >> format which excel will handle correctly using its default settings?
> >> Currently the user must open the file from within Excel and specify
> >> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> >>
> >> Thanks in advance,
> >>
> >> Ryan Opfer
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks Bruce for all the help, I do appreciate it. This sounds like an
adequate solution that I think will work.
It does seems strange though (although seemingly common for MS) to have RS
export to csv using a format that Excel doesn't natively understand by
default. I would have hoped that whatever MS decides to set as a default
format for csv would be the same between MS applications. I guess that's
just something we should all be use to by now anyway.
Ryan Opfer
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1VflVAIFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Here is an idea for you. Have an additional report that is very plain and
> exports to Excel well. Have it be the same as the other on as far as the
> data is concerned and the report parameters. Then add a link that uses
> jump
> to URL and have it render the second shadow report as Excel passing it the
> parameters from the current report. I call it a shadow report because you
> can set it to not be visible in list view so the user never sees it. The
> only way it gets run is when they click on the link.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
>> An Excel format is what I tried first but I couldn't get around how Excel
>> sees the document (it's too smart). Excel sees the document map and puts
> it
>> on sheet 1 then on sheet 2 (which has the main table data) it shows drill
>> down. Ultimately this file will need to be imported into an MS SQL table
> so
>> it needs to be simple.
>> Excel would work fine if I can remove the document map (obviously I can
>> do
>> this by just removing the document map from the report...and maybe that's
>> something the user will have to live with) and drill down capabilities
> (the
>> user can't live without this though), but by then I'd pretty much be
> looking
>> at a plain csv file anyway.
>> The key here is the user will be doing all this and just sending us back
>> a
>> basic csv or Excel file for the sql import so thereby has to be a simple
>> process.
>> From your reply's, I take it that RS can't defign how it exports to csv
>> or
>> for that matter Excel.
>> Thanks for the help,
>> Ryan
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
>> > You can render your report as an Excel file. No reason to use the CSV
>> > format in the first place.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> >> Thanks Bruce for the quick reply.
>> >>
>> >> The Excel format is fine provided you go through the Text Import
>> >> Wizard
>> >> of Excel and specify that the file is a comma delimited csv file not
>> >> Fixed Width. I would like to figure out how to Export from RS using a
>> >> format in which Excel will natively open the file as a comma delimited
>> >> file and thereby bypass having to have the user go through the Text
>> >> Import Wizard.
>> >>
>> >> There was a post back in November (Jan Bodey) that said she fixed this
> by
>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if
>> >> I
>> >> could have, I wouldn't expect the user to put this in the link anyway.
>> >>
>> >> Does that make more sense?
>> >>
>> >> Thanks,
>> >>
>> >> Ryan
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> Is there a problem with Excel format?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> >> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> >> default, open the file correctly. Is there a way to export to csv
>> >> using a
>> >> format which excel will handle correctly using its default settings?
>> >> Currently the user must open the file from within Excel and specify
>> >> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Ryan Opfer
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||For some reason, Excel doesn't understand Unicode CSV files by default. If
we did an ANSI CSV file (which you can specify in the DeviceInfo) then we
would lose data in certain scenarios. We could look at all of the characters
in the file and see whether we could use ANSI without data loss but this is
a lot of work.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:%23ETGwjAIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks Bruce for all the help, I do appreciate it. This sounds like an
> adequate solution that I think will work.
>
> It does seems strange though (although seemingly common for MS) to have RS
> export to csv using a format that Excel doesn't natively understand by
> default. I would have hoped that whatever MS decides to set as a default
> format for csv would be the same between MS applications. I guess that's
> just something we should all be use to by now anyway.
>
> Ryan Opfer
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u1VflVAIFHA.3536@.TK2MSFTNGP14.phx.gbl...
>> Here is an idea for you. Have an additional report that is very plain and
>> exports to Excel well. Have it be the same as the other on as far as the
>> data is concerned and the report parameters. Then add a link that uses
>> jump
>> to URL and have it render the second shadow report as Excel passing it
>> the
>> parameters from the current report. I call it a shadow report because you
>> can set it to not be visible in list view so the user never sees it. The
>> only way it gets run is when they click on the link.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
>> An Excel format is what I tried first but I couldn't get around how
>> Excel
>> sees the document (it's too smart). Excel sees the document map and puts
>> it
>> on sheet 1 then on sheet 2 (which has the main table data) it shows
>> drill
>> down. Ultimately this file will need to be imported into an MS SQL table
>> so
>> it needs to be simple.
>> Excel would work fine if I can remove the document map (obviously I can
>> do
>> this by just removing the document map from the report...and maybe
>> that's
>> something the user will have to live with) and drill down capabilities
>> (the
>> user can't live without this though), but by then I'd pretty much be
>> looking
>> at a plain csv file anyway.
>> The key here is the user will be doing all this and just sending us back
>> a
>> basic csv or Excel file for the sql import so thereby has to be a simple
>> process.
>> From your reply's, I take it that RS can't defign how it exports to csv
>> or
>> for that matter Excel.
>> Thanks for the help,
>> Ryan
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
>> > You can render your report as an Excel file. No reason to use the CSV
>> > format in the first place.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> >> Thanks Bruce for the quick reply.
>> >>
>> >> The Excel format is fine provided you go through the Text Import
>> >> Wizard
>> >> of Excel and specify that the file is a comma delimited csv file not
>> >> Fixed Width. I would like to figure out how to Export from RS using
>> >> a
>> >> format in which Excel will natively open the file as a comma
>> >> delimited
>> >> file and thereby bypass having to have the user go through the Text
>> >> Import Wizard.
>> >>
>> >> There was a post back in November (Jan Bodey) that said she fixed
>> >> this
>> by
>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if
>> >> I
>> >> could have, I wouldn't expect the user to put this in the link
>> >> anyway.
>> >>
>> >> Does that make more sense?
>> >>
>> >> Thanks,
>> >>
>> >> Ryan
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> Is there a problem with Excel format?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> >> Since RS exports csv files using a comma delimiter, Excel doesn't
>> >> by
>> >> default, open the file correctly. Is there a way to export to csv
>> >> using a
>> >> format which excel will handle correctly using its default
>> >> settings?
>> >> Currently the user must open the file from within Excel and specify
>> >> "Delimited" not "Fixed Width" and go through the Text Import
>> >> Wizard.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Ryan Opfer
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>