7

When exporting information from SQL Server Reporting Services in CSV format, it always appends two blank lines after the last row of data in the exported file.

Whilst I can edit the file after the export and remove the blank lines, is it possible to prevent SSRS from producing the two blank lines in the first place?

Pervez Choudhury
  • 2,892
  • 3
  • 27
  • 28

2 Answers2

14

See the following connect article (and vote for it to be fixed!

Bug 557655 SSRS2008 - Extra blank line in csv files created by csv renderer on export of report.

You need to alter the rendering extenstion setting for csv export in the RSReportServer.config file. Set the Device Info setting for CSV for ExcelMode to false like this:

    <Render>
        <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering"/>
        <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>
        <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
            <Configuration>
                <DeviceInfo>
                    <ExcelMode>False</ExcelMode>
                </DeviceInfo>
            </Configuration>                
        </Extension>

The default for the Excel mode setting is true. There is a known bug in the Excel Mode CSV renderer that adds an extra blank line. This workaround will allow you to get around the error. I had to implement this when I moved from SSRS 2005 to SSRS 2008.

William Salzman
  • 6,396
  • 2
  • 33
  • 49
  • 1
    I had the same problem and implemented the workaround (ExcelMode False), and it did indeed remove the extra blank line. However, it also changed all my datetime output fields to American format (mm/dd/yyyy hh:mm:ss) ignoring the formatting I had specified. Beware! – Edward Sep 11 '14 at 14:36
  • @Edward - Same thing happened to me, but I just changed the report's expression to specify the format I wanted using `=Format(Fields!Date.Value,"dd-MM-yyyy")` – Kevin Pope Nov 05 '15 at 22:09
  • It is not working . Can any one suggest other views regarding same. – Sameer Sameer Aug 05 '20 at 03:31
  • @SameerSameer what version of SSRS are you using, It is very likely this has changed since 2010 when I wrote this answer. – William Salzman Aug 18 '20 at 15:44
1

Newer versions of SSRS allow you to specify various settings in your report URL query string. To have bottom lines removed just add &rc:ExcelMode=false. More information here.

Martin
  • 145
  • 2
  • 3
  • I think this functionality has been in most of the versions I have worked with from at least 2005 on. My answer was to set it globally so it would work for all of my reports. – William Salzman Aug 18 '20 at 15:45