Report Builder Export to CSV with colum header spa

Question

This is a strange request as we all know that database headers should never contain spaces. However a system that I am using requires spaces in its headers to import. I have created a Report Builder report that builds the data into a table and works when I run it.

 '01/08/2015' "Active date",
 '31/07/2016' "Expiry date",

However when this is exported to a CSV file the are replaced with "Active_date" which causes an error on import. Does anyone know a way I can stop it from replacing spaces with _ or is this something that cannot be altered?

Answer 1

Yes It is possible to export to CSV with the spaces in column header. Here's how I achieved it.

Step 1
Update your report dataset to include the headers on row 1.

SELECT * FROM
(
 SELECT Field1, Field2, 2 as rowOrder
  FROM Tables
  Where Conditions
 UNION ALL
 SELECT 'Activity Date' AS Field1, 'Expiry Date' AS Field2, 1 as rowOrder
) ORDER BY rowOrder

Step 2:
Modify the RSReportServer.config file on Report server to customize CSV export to exclude header.

2012 config file Location: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

2008 File Location: \Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer

Imp: Make a backup of the RSReportServer.config in case you need to rollback your changes.

Add another entry in <render> section below CSV extension.

<Extension name="CSVNoHeader" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
        <Name Language="en-US">CSV No Header</Name>
    </OverrideNames>
    <Configuration>
        <DeviceInfo>
            <NoHeader>true</NoHeader>
        </DeviceInfo>
     </Configuration>
</Extension>

Save it. Now you have another drop down export option CSV No Header along with CSV, PDF, XML. Users can use this option to extract the data in CSV with the spaces in the header.

MSDN Link to customize Rendering extension

Answer 2

No its not possible, see below links for more details -

1. SSRS csv export with comma in the column header names
2. Is it possible to export to CSV and have the header contain spaces?