Semicolon as field delimiter in csv export SSRS report

For a new project I needed to use a csv rendered SSRS report, but the default settings were not sufficient (comma delimited).

I had to do some research, but managed to get what I want:

  • Export without headers
  • Semicolon as field delimiter
  • Dot as decimal separator


Let’s start with the last part of my needs, the decimal separator.
This was done by changing the language setting to en-US and the format to #,0.000

Semi001

Semi002

Next came the ‘difficult’ part.
We have to modify the rsreportserver.config file, which can be found (depending on the version) somewhere here: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

Make sure you make a copy of the original file, to have a backup.

Next we go to the <Render> element and creat a new extention.

<Extension Name="CSVpk" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">

</Extention>;

The new name is needed so we can select it in (for example) a subscription.
To give the extention a new description, we added: (sorry for the Dutch)

<OverrideNames>
     <Name Language="en-US">CSV-puntkomma gescheiden</Name>
</OverrideNames>

Next we need to get rid of the headers and change the field delimiter, this was done by adding some elements.

				<Configuration>
					<DeviceInfo>
						<FieldDelimiter>;</FieldDelimiter>
						<NoHeader>true</NoHeader>
					</DeviceInfo>
				</Configuration>

So in total we have:

			<Extension Name="CSVpk" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
				<OverrideNames>
        			<Name Language="en-US">CSV-puntkomma gescheiden</Name>
    			</OverrideNames>
				<Configuration>
					<DeviceInfo>
						<FieldDelimiter>;</FieldDelimiter>
						<NoHeader>true</NoHeader>
					</DeviceInfo>
				</Configuration>
			</Extension>

Semi003

The reportserver service needs to be restarted.

If we generate the report and click on the floppy disk, we now see the new extention.
Semi004

And, the result is like we did expect.

Semi005

More info on this topic:

http://msdn.microsoft.com/en-us/library/ms157273.aspx

13 comments:

  1. Nice

    thank you.

    The noheader seems to remove the column headers in the csv, while in the excel-export it removes the report header. Or did I configure something wrong?

    1. That is correct, I did put my column headers in the report header.
      I did not have the need to make further tests, as this works for me (my report did not have a ‘report header’, only column headers).

  2. Thx – it works really perfekt.
    I tried to add quotation mark (“) as text qualifier – but it dosn´t work.

    any hints?

    1. As far as I see, SSRS only puts qualifiers around a cell when the value contains the field delimiter or record delimiter.
      https://msdn.microsoft.com/en-us/library/ms155365.aspx

      The default is (“) so you don’t have to set it to that.

      It is consistent with my output, as I have set a comma as the field delimiter and all cells that contain a comma have “” around them.

      You have the option of setting the qualifier to (for example) a single quote (‘) and add the double quote in your dataset (like “+value+”)
      The first is needed because SSRS will repeat the qualifier if the value contains the qualifier (ending up with “”value””).
      Drawback of this method is that you loose the datatype (not needed in csv, but if you use the same report in f.e. Excel, you might need that).
      And you will need to convert to string if the field is any other datatype.

      So far I cannot see any other options, but let me know if this works for you!

      Peter

  3. Many thanks for the pointer, however, I created what I thought was going to be the semi-colon seperated values file but got html instead. My only amend to the text above was the removal of the element as Reporting Services service would not start with this in the code. Am using SQL 2014 standard, but have the handicap of little or no experience with it….. Any thoughts appreciated!

    1. Your welcome.
      I am not sure about 2014, as we run 2012.
      However, there should be information about this online.
      Usually when RS won’t start after a change in the config file, you made an XML error so the config file does not parse anymore.
      (like forgetting a closing tag etc.)

      What I would recommend, is copying an existing extension node (starting from Extension …… until the closing tag /Extension). that does work as already, outputting CSV file or whatever and change it step by step (checking after each step if it works).
      So first only change the name and display name. Try if it works. Change the delimiter and check again. etc. etc.

      That way you know where you made an error.

    2. Apologies, my lack of experience with this was showing, problem solved and all is now working! Please ignore the last comment from me.
      Much appreciate the shared code, really helpful, thank you

Leave a Reply

Your email address will not be published. Required fields are marked *