Reporting Services troubleshooting: Speed CSV imports into Excel

Reporting Services exports CSV files in a format not easily recognized by Excel, slowing the import process. Contributor Serdar Yegulalp explains how to fix this problem.

The following is the first tip in a series on how to troubleshoot common SQL Server 2005 Reporting Services dilemmas.

SQL Server Reporting Services 2005 can export to a variety of formats including PDF and the ubiquitously-used, comma-separated value (CSV). If you're exporting to Excel a great deal and need a good format for speed – and don't need pretty formatting – CSV is usually the best way to go.

But there's one small problem: Reporting Services exports CSV files as Unicode by default, not ASCII. This creates problems for Excel because Excel's CSV import module has difficulty automatically recognizing CSV files written in Unicode. If you attempt to import into Excel, the import process can take an agonizingly long time, and it will only get exponentially worse for bigger files. This may manifest to the end user as what seems to be a sluggish download process, when in fact the problem is Excel's file conversion.

To get around this dilemma, you can edit Internet Information Server's (IIS) rsreportserver.config file to export to ASCII .CSV by default:

    1. Open the rsreportserver.config file with a text editor and look for this block:

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

    2. Comment out that section with <!-- and --> marks.

    3. Directly below that comment section, add the following:

    <Extension Name="CSV"
    Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,
    Microsoft.ReportingServices.CsvRendering">
        <Configuration>
         <DeviceInfo>
          <Encoding>ASCII</Encoding>
         </DeviceInfo>
        </Configuration>
       </Extension>
    

4. Run IISRESTART to reload the configuration file.

The reason you'll want to comment out the original block rather than delete it is so that you can revert back to the original settings in the unlikely event the new ones don't work correctly.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!


More information from SearchSQLServer.com

  • Chapter: Installing and configuring Reporting Services
  • Article: SQL Server's reporting tool goes across the board
  • Ask the Experts: Ask Eric Brown your BI questions


  • This was first published in January 2006
    This Content Component encountered an error

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close