次の方法で共有

Reports exported in CSV from Reporting Service 2008, when opened in EXCEL shows each comma seperated values in new column

  • [アーティクル]

Have you ever noted down the difference when you open a CSV file in Excel which is exported from REPORTING SERVICE 2005 and REPORTING SERVICE 2008?

 

The one which is exported from REPORTING SERVICE 2005 shows all the values for each row in a single column separated by comma. Whereas the one which is exported from REPORTING SERVICE 2008 shows each row values which are separated by comma in an individual columns.

 

The reason here is,

 

The default encoding for CSV has changed from REPORTING SERVICE 2005 to REPORTING SERVICE 2008.

 

REPORTING SERVICE 2005 : https://msdn.microsoft.com/en-us/library/ms155365(SQL.90).aspx  (Encoding - One of the character encoding schemas: ASCII, UTF-7, UTF-8, or Unicode. The default value is Unicode.)

 

REPORTING SERVICE 2008 : https://msdn.microsoft.com/en-us/library/ms155365.aspx  (Encoding - One of the character encoding schemas: ASCII, UTF-7, UTF-8, or Unicode. The default value is UTF-8.)

 

Whenever a file with encoding UTF-8 is opened in Excel, it automatically converts the comma separated values in to individual columns. 

 

So if you want your REPORTING SERVICE 2008 exported CSV to shows all the values for each row in a single column separated by comma (Just as REPORTING SERVICE 2005), you need to change the encoding to Unicode as follows.

 

1. Open the Reporting ServiceReportServer.config file (Located @ C:\Program Files\Microsoft SQL Server\MSREPORTING SERVICE10.SSREPORTING SERVICE2008\Reporting Services\ReportServer).

 

2. Locate the element, <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"/>

 

3. Replace the entire line with the following,

< Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">

               <Configuration>

          <DeviceInfo>

            <Encoding>Unicode</Encoding>

          </DeviceInfo>

        </Configuration>

      </Extension>

 

4. Save the file and Restart the Report server windows service (From Reporting Service configuration manager).

 

5. Now open the report manager, run the report and export the same to CSV.

 

6. Then open it in Excel and you’ll see the file getting opened in the way you expected (Values listed in one column with comma separated).

 

On the other hand if you want your REPORTING SERVICE 2005 exported CSV to show each row values which are separated by comma in an individual columns (Just as REPORTING SERVICE 2008), you need to change the encoding to UTF-8 as follows.

 

1. Open the Reporting ServiceReportServer.config file (Located @ C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer).

 

2. Locate the element, <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>

 

3. Replace the entire line with the following,

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">

               <Configuration>

          <DeviceInfo>

            <Encoding>UTF-8</Encoding>

          </DeviceInfo>

        </Configuration>

</Extension>

 

4. Save the file and Restart the Report server windows service (From Reporting Service configuration manager).

 

5. Now open the report manager, run the report and export the same to CSV.

 

6. Then open it in Excel and you’ll see the same behavior that you see in REPORTING SERVICE 2008.

 

HTH!

Selva.

Comments

  • Anonymous
    December 01, 2009
    When I export to csv. Is possible change the delimeter. I've RS2005 Thanks

  • Anonymous
    December 01, 2009
    The comment has been removed

  • Anonymous
    January 20, 2010
    i am trying what u written but it is not workin. reporting service 2008 doesn't export Tablix data into individual columns.

  • Anonymous
    January 21, 2010
    The comment has been removed

  • Anonymous
    February 08, 2010
    Any Chance that SSRS 2005 can support Tab-delimited format?  I have tried several changes similar to what you listed above, but it does not seem to work.   Your insight is appreciated.  :)

  • Anonymous
    February 22, 2010
    Hi Braino,          My appologize for the delayed response as i was travelling. With reporting service 2005 there is no easy way of bringing the TAB delimiter in to CSV through the Device info setting in the config file. That flexibility is not there with RS 2005 due to product limitation. However it can be achieved in to RS 2008 as described here, http://blogs.msdn.com/robertbruckner/archive/2009/01/20/take-another-look-at-csv-rendering-in-rs-2008.aspx But having said that, there is a way to work around the same in RS 2005. That is through the URL rendering. Here is an example of how to do the same. http://machinename/ReportServer2K5/Pages/ReportViewer.aspx?%2fSimpleSelect%2fReport1&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09 HTH! Selva. [bove post is AS-IS with no warranty]

  • Anonymous
    January 07, 2013
    Thanks, its really worked. :)