Non-Unicode CSV Export from SQL Server Report Server

Question

I am using the SQL Server Report Server from Microsoft SQL Server 2005.

In the report server report viewer control, there is a CSV download option. The CSV download option is currently downloading a Unicode CSV file, which does not load into Microsoft Excel with the correct column formatting.

If I save the Unicode CSV file and convert it to a ASCII file it works fine in Excel.

How can I can I set a report or the SSRS Report Viewer to export CSV as ASCII instead of Unicode?

Thanks in advance for any help with this.

Solution 1

Go to the RSReportserver.config file (should be in the root of your reportserver virtual directory e.g. "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer")

Find the CSV Extension - should look something like below. Change the Encoding node to read ASCII instead of Unicode or whatevr you have in there.

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

Solution 2

More information at http://msdn.microsoft.com/en-us/library/ms156281.aspx. You can create multiple extensions, i.e. in addition to the default "CSV" you can add "CSV - ASCII" or "CSV - Pipe" etc.

Solution 3

Just for completeness here's how you go about adding a "CSV-ANSI" export option.You'll need to add an "OverrideNames" tag so that you don't get duplicate "CSV" options. As I'm in Australia I needed to add a "en-AU" language option, you can remove it if you don't need it (or replace with your own language).

<Extension Name="CSV-UTF8" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
        <Name Language="en-US">CSV (UTF8)</Name>
        <Name Language="en-AU">CSV (UTF8)</Name>
    </OverrideNames>                
</Extension>

<Extension Name="CSV-ANSI" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
        <Name Language="en-US">CSV (ANSI)</Name>
        <Name Language="en-AU">CSV (ANSI)</Name>
    </OverrideNames>                
    <Configuration>     
        <DeviceInfo>
            <Encoding>ASCII</Encoding>
        </DeviceInfo>
    </Configuration>
</Extension>

Comments

[Davos]

+1 Much better than overriding the CSV setting across your whole ssrs site!

[Rocklan]

Thanks @Davos, been using this solution for a while now and it's working well.

[Doug_Ivison]

Locating config file: (Note 1) Your subfolder under Microsoft SQL Server may begin with MSRS, like ours: MSRS10_50.MSSQLSERVERBO. (Note 2) You may have to actually NAVIGATE into the Reporting Services folder, at least once, before its config files will show up on file searches: double-clicking folder Reporting Services, I got: "You don't currently have permission to access this folder. Click Continue to permanently get access to this folder." BEFORE clicking Continue, files under Reporting Services were NOT listed on file searches; AFTER Continue, searches found them!

[Nick Meldrum]

Ahh the joys of MSRS, nice tips!

[Praveen Prasannan]

<Encoding>Unicode</Encoding> didn't work for me. But <Encoding>UTF-8</Encoding> did. worth a read : msdn.microsoft.com/en-us/library/ms155365.aspx