I'm using the reporting services web service to export reports to csv files.
When I export to a csv, a quote mark is appended at the beginning and end of
each line. I'm using the default device info settings. This is not the
Qualifier, because I tried changing that from ["] to other characters in the
devinfo settings, and the ["] is still inserted.
How can I prevent the ["] from being inserted? It causes problems when I
open the file in Excel, because Excel thinks it means the whole row is one
field, and it gets truncated after 256 characters.
Thanks!
BillTwo different solutions depending on RS 2000 or RS 2005. Note, either you
need to be concerned with a merged cells problem when exporting CSV.
Solution 1:
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Solution 2:
Do the above design change to avoid the merged cell problem with sorting in
Excel. Then modify rsreportserver.config. Reboot after the change. The below
shows commenting out the existing entry and putting in the needed change to
have CSV export as ASCII
. <!--
<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>
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>|||I just realized that the quotes are being added when I save the csv, and
aren't there when it is generated by the reporting service.
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>
