SSRS csv export with comma in the column header names

Question

By default, csv takes the text box name as the csv header name for the columns. The text box does not allow a space.

How do I make a different header than the text box for the csv?
Is there a way to display comma in the header when exported to csv?

Note: The solution has to be only for one report; not global.

Tricotine

i know one very easy way .. tested ... replace space by : ALT+255 – Rigmarole

Answer 1

The column name comes from the DataElementName property, or if that is blank, the Name property. Unfortunatly, neither allow commas or quoting.

There is a blog post here:

http://www.behindthecode.net/Blog/post/2008/01/29/How-to-modify-Reporting-Services-CSV-export-Behavior.aspx

on how to change settings in the config file for csv exports. These are global changes. The list of properties available is here:

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

None of these allow the spaces you need, or the quoting of column names that I am currently trying to achieve. Sorry.

Lonnielonny

Answer 2

In short, No, it's not possible.

From SSRS 2008 Column issue when exporting to a CSV file:

For csv export, the header of a column is determined by the DataElementName property of the textbox which actually contains the data. If the DataElementName property is not explicitly set by the user, DataElementName automatically gets defaulted to the value of the textbox.Name property.

That being said, I believe you're getting this issue because RDL has a restriction that both a reportItem.Name and reportItem.DataElementName property must be CLS-compliant identifiers. The headings like "Phone #", "Other Name(s)", etc... are not CLS-Compliant.

You can verify this by opening up the report in BIDS or RB and trying to set the DataElementName/Name property of the textbox containing the data to those names.

You will get an error which states the following:

Property Value is not valid. Specify a valid name. The name cannot contain spaces, and it must begin with a letter followed by letters, numbers, or the underscore character (_).

There is a Microsoft Connect Issue about this, but unfortunately it looks like it won't be acted on.
See: Use of expressions for DataElementName property (for CSV Export)

Centroid

Answer 3

This solution works: https://mcmap.net/q/1482067/-report-builder-export-to-csv-with-colum-header-spaces

In summary:

1. Modify your query to have the first row in the data set be your desired column headers. E.g., select header names and union them to the data set.
2. Add a new CSV rendering extension to the report server that excludes headers.

This workaround will let you to put whatever values you'd like in the headers.

Katar

Answer 4

Append the following query parameters to your url:

• For # - [url]&rs:format=CSV&rc:FieldDelimiter=%23
• For ; - [url]&rs:format=CSV&rc:FieldDelimiter=";"

Not sure you can do it directly from Report Manager without altering config files somewhere

Update: After rereading this, are you returning the column headers from the procedure like this?

SELECT tbl.colname [my column name, some text],  
       tbl.col2    [another, col]

The square brackets will allow you to escape characters.

Ziegfeld

Thanks for your reply. It gives me a hope. In which file I have to make it? Any thoughts? – Tricotine

Answer 5

Column names for Csv, Dbf and Xml are picked up from <Name> field by default.

Solved this issue by adding appropriate names for each of this formats in <Tag> field in *.mrt file. E.g:

<Name>Txt_InternalName</Name> <Tag>CSV:"Name for Csv"; DBF: "Name for DBF"; XML: "Name for Xml"; Default: "Default Name for Csv Dbf Xml"</Tag>

Also it resolves issue with CLS non compliant identifiers in headers

Aid