Export to CSV and oepn in Excel

Is there a way to export to CSV and open the file directly in Excel without
saving it first? And, to have Excel parse the columns correctly when opening?
I can save the file and then import it in another step into Excel without
problems. But, when I Open in Excel instead of saving it first it has all the
exported csv columns in a single Excel column, i.e. Excel did not parse it
into a spreadsheet format.
Thanks,
SteveOn Jan 15, 4:46 pm, Steve <MyNoS...@.NoSpam.org> wrote:
> Is there a way to export to CSV and open the file directly in Excel without
> saving it first? And, to have Excel parse the columns correctly when opening?
> I can save the file and then import it in another step into Excel without
> problems. But, when I Open in Excel instead of saving it first it has all the
> exported csv columns in a single Excel column, i.e. Excel did not parse it
> into a spreadsheet format.
> Thanks,
> Steve
You might try setting the delimiter programmatically and use Microsoft
Automation to open the file using Excel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The problem here is that the default for CSV export is Unicode which Excel
puts all into one column (I don't know about the latest version of excel but
Excel 2003 and earlier does).
RS 2005 you can set this in config file to default to ASCII instead of
Unicode. Then it will do exactly as you want.
RS 2000 I added a link to my report and used Jump To URL to open up in ASCII
CSV format.
RS 2005 solution:
RSReportServer.Config file make a backup before changing.
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Note how I commented out the original and then added the new configuration.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
> Is there a way to export to CSV and open the file directly in Excel
> without
> saving it first? And, to have Excel parse the columns correctly when
> opening?
> I can save the file and then import it in another step into Excel without
> problems. But, when I Open in Excel instead of saving it first it has all
> the
> exported csv columns in a single Excel column, i.e. Excel did not parse it
> into a spreadsheet format.
> Thanks,
> Steve
>|||Bruce,
We are still using RS 2000 and Excel 2003, so I tried your Jump To URL
suggestion and it works great.
I also tried modifying the config as suggested and restarting the report
server, but that didn't work for us. So, I guess unless I typed something
incorrectly that is strictly a RS 2005 solution?
Thanks much!
Regards,
Steve
"Bruce L-C [MVP]" wrote:
> The problem here is that the default for CSV export is Unicode which Excel
> puts all into one column (I don't know about the latest version of excel but
> Excel 2003 and earlier does).
> RS 2005 you can set this in config file to default to ASCII instead of
> Unicode. Then it will do exactly as you want.
> RS 2000 I added a link to my report and used Jump To URL to open up in ASCII
> CSV format.
> RS 2005 solution:
> RSReportServer.Config file make a backup before changing.
> <!--
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
> -->
> <Extension Name="CSV"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <Configuration>
> <DeviceInfo>
> <Encoding>ASCII</Encoding>
> </DeviceInfo>
> </Configuration>
> </Extension>
> Note how I commented out the original and then added the new configuration.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
> news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
> > Is there a way to export to CSV and open the file directly in Excel
> > without
> > saving it first? And, to have Excel parse the columns correctly when
> > opening?
> >
> > I can save the file and then import it in another step into Excel without
> > problems. But, when I Open in Excel instead of saving it first it has all
> > the
> > exported csv columns in a single Excel column, i.e. Excel did not parse it
> > into a spreadsheet format.
> >
> > Thanks,
> > Steve
> >
>
>|||Yes, it is a RS 2005 only solution.
As you have seen in RS 2000, exporting to CSV to get data into Exel is much
faster. Excel rendering is a good bit better in RS 2005 and I expect it will
be even better in RS 2008. Although I have CSV export configured as ASCII in
RS 2005 my users very seldom need to do that anymore. Pretty much they can
export to Excel with good enough performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:F9F9DFB8-52CF-49C0-8E88-023B45C7CBDD@.microsoft.com...
> Bruce,
> We are still using RS 2000 and Excel 2003, so I tried your Jump To URL
> suggestion and it works great.
> I also tried modifying the config as suggested and restarting the report
> server, but that didn't work for us. So, I guess unless I typed something
> incorrectly that is strictly a RS 2005 solution?
> Thanks much!
> Regards,
> Steve
> "Bruce L-C [MVP]" wrote:
>> The problem here is that the default for CSV export is Unicode which
>> Excel
>> puts all into one column (I don't know about the latest version of excel
>> but
>> Excel 2003 and earlier does).
>> RS 2005 you can set this in config file to default to ASCII instead of
>> Unicode. Then it will do exactly as you want.
>> RS 2000 I added a link to my report and used Jump To URL to open up in
>> ASCII
>> CSV format.
>> RS 2005 solution:
>> RSReportServer.Config file make a backup before changing.
>> <!--
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
>> -->
>> <Extension Name="CSV"
>> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
>> <Configuration>
>> <DeviceInfo>
>> <Encoding>ASCII</Encoding>
>> </DeviceInfo>
>> </Configuration>
>> </Extension>
>> Note how I commented out the original and then added the new
>> configuration.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
>> news:65404A6E-931E-482F-B1B5-A4C7374E9FA8@.microsoft.com...
>> > Is there a way to export to CSV and open the file directly in Excel
>> > without
>> > saving it first? And, to have Excel parse the columns correctly when
>> > opening?
>> >
>> > I can save the file and then import it in another step into Excel
>> > without
>> > problems. But, when I Open in Excel instead of saving it first it has
>> > all
>> > the
>> > exported csv columns in a single Excel column, i.e. Excel did not parse
>> > it
>> > into a spreadsheet format.
>> >
>> > Thanks,
>> > Steve
>> >
>>