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
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>
The reportserver service needs to be restarted.
If we generate the report and click on the floppy disk, we now see the new extention.
And, the result is like we did expect.
More info on this topic:
http://msdn.microsoft.com/en-us/library/ms157273.aspx
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?
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).
Great! You made my day!
You’re welcome!
Glad to be able to help 🙂
Thx – it works really perfekt.
I tried to add quotation mark (“) as text qualifier – but it dosn´t work.
”
any hints?
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
Just what I needed, clearly explained. Thanks for sharing.
You’re welcome
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!
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.
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
Thank you.
It works great for me and I am using SSRS 2016.
Good to hear. Thanks!
Perfect! Thank you! 🙂
I tried the semicolon with PowerBI Report Server, but to no avail. When I click the 3 dots in a visual and choose “Export data” it still exports it as being comma separated instead of semicolon separated. Besides this it ignores the country settings which has the semicolon as field separator for csv files.
Any thoughts how to get this working in PBI Report Server (with the latest January release)?
I’m sorry, we don’t use PBI, instead we use SSRS.
So I can’t help you with that.
I am using SSRS 2016 and I cannot find the config file rsreportserver.config
Is the path different?
So far we have not upgraded, so I can’t help you with that!
Helpful write-up, thank you.
Any idea what the XML tag is to change the new render format name?
Had to change the < to [ because of WordPress. [OverrideNames] [Name Language="en-US"]CSV-puntkomma gescheiden[/Name] [/OverrideNames]
How to user pipe delimited text file as a data source in SSRS.
I’m sorry for the late reply. Apparently the notifications don’t get through.
I’m not sure how to do this, since we only use a SQL database as a source.