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