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

  • Article

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.