Exporting tab-delimited files in SSRS 2005

Question

In this MSDN article, MS explains how to specify other delimiters besides commas for csv-type exports from SSRS 2005:

http://msdn.microsoft.com/en-us/library/ms155365%28SQL.90%29.aspx

...however, literal tab characters are stripped by the config file parser, and it doesn't appear that MS has provided a workaround. This entry on Microsoft Connect seems to confirm this:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=357527

...has anyone developed a way to export tab-delimited files from SSRS 2005? Or perhaps developed an open-source custom renderer to get the job done?

Note: I've heard of manually appending &rc:FieldDelimiter=%09 via URL access, but that's not an acceptable workaround for my users and doesn't appear to work anyways.

Answer 1

I used a select query to format the data and BCP to extract the data out into a file. In my case I encapsulated it all in a stored procedure and scheduled it using the SQL Agent to drop files at certain times. The basic coding is similar to:

use tempdb
go
create view vw_bcpMasterSysobjects
as
   select
      name = '"' + name + '"' ,
      crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
      crtime = '"' + convert(varchar(8), crdate, 108) + '"'
   from master..sysobjects
go
declare @sql varchar(8000)
select @sql = 'bcp "select * from tempdb..vw_bcpMasterSysobjects
                     order by crdate desc, crtime desc"
               queryout c:\bcp\sysobjects.txt -c -t, -T -S'
                                             + @@servername
exec master..xp_cmdshell @sql

Please have a look at the excellent post creating-csv-files-using-bcp-and-stored-procedures.

Answer 2

Call me Mr Silly but wouldn't it be simpler to have XML returned from a stored proc or a SQL statement? An XSLT transformation to CSV is trivial.

Or you could write an equally trivial ASP.NET page that obtains the data using ADO.NET, clears the output stream, sets the mime type to text/csv and writes CSV to it.

Oops, I see you want a delimiter other than comma. But both of the above solutions can still be applied. If you go the ASP way you could have a parameter page that lets them pick the delimiter of their choice.

Answer 3

My current workaround is to add a custom CSV extension as such:

<Extension Name="Tabs" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
    <OverrideNames>
        <Name Language="en-US">Tab-delimited (requires patch)</Name>
    </OverrideNames>
    <Configuration>
        <DeviceInfo>
            <Encoding>ASCII</Encoding>
            <FieldDelimiter>REPLACE_WITH_TAB</FieldDelimiter>
            <Extension>txt</Extension>
        </DeviceInfo>
    </Configuration>
</Extension>

...you can see I'm using the text "REPLACE_WITH_TAB" as my field delimiter, and then I use a simple platform-independent Perl script to perform a sed-like fix:

# all .txt files in the working directory
@files = <*.txt>;

foreach $file (@files) {
    $old = $file;
    $new = "$file.temp";

    open OLD, "<", $old or die $!;
    open NEW, ">", $new or die $!;

    while (my $line = <OLD>) {

        # SSRS 2005 SP2 can't output tab-delimited files
        $line =~ s/REPLACE_WITH_TAB/\t/g;

        print NEW $line;
    }

    close OLD or die $!;
    close NEW or die $!;

    rename($old, "$old.orig");
    rename($new, $old);
}

This is definitely a hack, but it gets the job done in a fairly non-invasive manner. It only requires:

• Perl installed on the user's machine
• User's ability to drag the .pl script to the directory of .txt files
• User's ability to double-click the .pl script

Answer 4

In case anyone needs it this is working very well for me.

  <Extension Name="Tabs" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
   <OverrideNames>
    <Name Language="en-US">Tab-delimited</Name>
   </OverrideNames>
   <Configuration>
    <DeviceInfo>
     <OutputFormat>TXT</OutputFormat>
     <Encoding>ASCII</Encoding>
     <FieldDelimiter>&#9;</FieldDelimiter>
     <!-- or as this -->
     <!-- <FieldDelimiter xml:space="preserve">[TAB]</FieldDelimiter> -->
     <FileExtension>txt</FileExtension>
    </DeviceInfo>
   </Configuration>
  </Extension>