Report Builder Export to CSV with colum header spaces

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?

Rusel

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

Scorpio

Thanks for this I have modified my SQL and have added the new extension to the .Config file. However this is not showing up in the export menu (web and client). Any idea why that may be? – Rusel

<B>Test 1</B>Usually you don't event need to start the report server to see the changes. Try restarting the server and see if it makes any difference. <B>Test 2</B>Another test you can do is change the name of existing Rendering option like PDF to ZZZ. Restart the server and goto report server page to see if you see zzz or pdf. if you are still seeing pdf that means you are not modifying the correct file. – Scorpio

Only downside is you have to pre-convert all your data to strings, but seems to be the best solution available. – Hem

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?

Kalevala

Not sure why it got down voted, I have specified the links where many people have said it is not possible and I don't think so their responses were down voted too. – Kalevala