Reporting Services troubleshooting: Speed CSV imports into Excel

Serdar Yegulalp, Contributor

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 (

    Requires Free Membership to View

    Login

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

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.

      Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

      Back to top