SSRS Hash tag delimited table for export

Question

Post reply

SSRS Hash tag delimited table for export

Gunther Van Loco

SSC Veteran

Points: 254
More actions
March 26, 2014 at 9:35 am

#285020

Hi,
I have created an export type as followed
<Extension Name="HASH" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<OverrideNames>
<Name Language="en-US">Hash Tag (# Delimited Text File)</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<FieldDelimiter xml:space="preserve">#</FieldDelimiter>
<RecordDelimiter>#</RecordDelimiter>
<UseFormattedValues>False</UseFormattedValues>
<NoHeader>True</NoHeader>
<SuppressLineBreaks>True</SuppressLineBreaks>
<FileExtension>TXT</FileExtension>
</DeviceInfo>
</Configuration>
</Extension>
Now the problem resides in
<RecordDelimiter>#</RecordDelimiter>
wich should allow to have a # char at the end of the record but this makes my report server crash when I try to export is as such.
As usual Microsoft is being very vague abouit what to put between
<RecordDelimiter>&</RecordDelimiter>
If I leave it out it works fine but that is not what I want.
My lines should look like xxx#yyyy#zzz# instead of xxx#yyyy#zzz
Any ideas ???
Greetz

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gunther Van Loco SSC Veteran Points: 254 More actions · Answer 1

Already found out that RecordDelimiter and FieldDelimiter cannot be the same ?

is this native or a bug ?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

gunther.vanloco (3/27/2014)
Already found out that RecordDelimiter and FieldDelimiter cannot be the same ?
is this native or a bug ?

It is normal that the fielddelimiter (aka column delimiter) cannot be the same as the row delimiter.

Otherwise how can you know if you are at the end of a column or of a record?

If you want a hash-tag at the end, you'll need to include an extra column, which is just empty.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gunther Van Loco SSC Veteran Points: 254 More actions · Answer 3

I tried adding it but the column is empty and is not included in the output apprantly so no hash tag at the end

I also tried a space char in it and no luck

:crying:

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

gunther.vanloco (3/27/2014)
I tried adding it but the column is empty and is not included in the output apprantly so no hash tag at the end
I also tried a space char in it and no luck
:crying:

With the empty column at the end, your row delimiter should be a regular carriage return.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gunther Van Loco SSC Veteran Points: 254 More actions · Answer 5

Ok I tried following

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

<OverrideNames>

<Name Language="en-US">Hash Tag (# Delimited Text File)</Name>

</OverrideNames>

<Configuration>

<DeviceInfo>

<FieldDelimiter xml:space="preserve">#</FieldDelimiter>

<UseFormattedValues>False</UseFormattedValues>

<NoHeader>True</NoHeader>

<SuppressLineBreaks>True</SuppressLineBreaks>

<FileExtension>TXT</FileExtension>

</DeviceInfo>

</Configuration>

</Extension>

The report looks like

27-03-2014 9-37-25.jpg attach

Export looks like

27-03-2014 9-39-10.jpg attach

What I'm I not seeing here :blink:

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Is there a space in the empty column?

Is the column marked to be exported?

(just guessing here)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gunther Van Loco SSC Veteran Points: 254 More actions · Answer 7

The column and field itself get exported, but the header is set on DataElementOutput : NoOutput

There is a space in the header and a space in the column itself.

I can not seem to select the empty char in the report, maybe there lays the problem ?

See screenshot

Gunther Van Loco SSC Veteran Points: 254 More actions · Answer 8

Ok after trying out some more I found out that adding the space as an extra column to my query and then adding it as an extra column to my report the output works fine.

I just added

SELECT *, ' ' As FinalDelimiter FROM XXX

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

gunther.vanloco (3/27/2014)
Ok after trying out some more I found out that adding the space as an extra column to my query and then adding it as an extra column to my report the output works fine.
I just added
SELECT *, ' ' As FinalDelimiter FROM XXX

Ah good, glad that you finally solved it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP