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 : http://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 : http://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 (7)

  1. Pere says:

    When I export to csv.

    Is possible change the delimeter.

    I’ve RS2005

    Thanks

  2. Selvar says:

    Reporting Service 2005 exposes the FieldDelimiter device info setting for CSV, which allows you to change the defalut delimiter (,) to anything.

    REF: http://msdn.microsoft.com/en-us/library/ms155365(SQL.90).aspx

    This can be done as follows.

    Replace the following line in your RsReportServer.config file,

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

    Microsoft.ReportingServices.CsvRendering"/>

    With

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

    Microsoft.ReportingServices.CsvRendering">

    <Configuration>

    <DeviceInfo>

    <FieldDelimiter>#</FieldDelimiter>

    </DeviceInfo>

    </Configuration>

    </Extension>

    I’ve changed the delimiter to #.

    Save the file and restart the Report Server Windows service from the configuration manager UI.

    Now export the file and open it in notepad and you’ll see the columns seperated by # instead of ,.

    (eg: ALFKI#Alfreds Futterkiste#Maria Anders)

    HTH!

    Selva.

  3. Nermin says:

    i am trying what u written but it is not workin.

    reporting service 2008 doesn’t export Tablix data into individual columns.

  4. Selvar says:

    Reporting Service 2008 does export it to individual columns. If there is some problem then You can try forcing it in the rsreportserver.config file as follows,

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

    Replace the entire line with the following,

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

                  <Configuration>

             <DeviceInfo>

               <Encoding>UTF-8</Encoding>

             </DeviceInfo>

           </Configuration>

         </Extension>

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

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

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

    HTH!

    Selva.

    (All the posts are AS-IS and doesn’t carry any warranty.)

  5. Braino says:

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

  6. SELVAR says:

    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]

  7. Siva says:

    Thanks, its really worked. 🙂

Skip to main content