SSRS Report has 3 Special Characters

Question

SSRS Report has 3 Special Characters

Merlin802

SSC-Addicted

Points: 402
More actions
October 15, 2015 at 6:47 am

#312324

I have a SSRS report that is saved as a tab delimited text file and while it looks fine if you open the file with say notepad, it actually has 3 special characters at the beginning of the file (can be seen with a hex editor and causes a problem when our customer tries to import the file into their ERP system).
The 3 characters are ï»¿ and I do not see anything when building the report that corresponds to these characters. How can I stop them from saving in the tab delimited file?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Spiff SSCertifiable Points: 7766 More actions · Answer 1

Hi Awhicker,

It's known as a Byte Order Mark (BOM) and it indicates the text indicates the text is encoded as UTF-8. I've also found this issue when I import CSV exports into R.

You need to change the encoding for the CSV renderer in your rsreportserver.config.

From:

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

To:

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

<Configuration>

<DeviceInfo>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

Note the / is removed in the first row to avoid auto closing the tag. I have got to give the solution credit to SO though: http://stackoverflow.com/questions/691620/non-unicode-csv-export-from-sql-server-report-server. Note the Renderer library names change between versions. The above will work on 2012, it think the first answer in the SO thread is for 2008 so the library names are different.

Merlin802 SSC-Addicted Points: 402 More actions · Answer 2

If I am understanding correctly, it looks like the '/' is already not present. There is one at the end of the two lines above it though, does that need to be removed or do you see any other issues?

<Render>

<Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering"/>

<Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>

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

<Configuration>

<DeviceInfo>

<FieldDelimiter>|</FieldDelimiter>

<NoHeader>true</NoHeader>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

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

<OverrideNames>

<Name Language="en-US">TAB delimited</Name>

</OverrideNames>

<Configuration>

<DeviceInfo>

<FieldDelimiter> </FieldDelimiter>

<UseFormattedValues>False</UseFormattedValues>

<NoHeader>False</NoHeader>

<FileExtension>txt</FileExtension>

</DeviceInfo>

</Configuration>

</Extension>

<Extension Name="ATOM" Type="Microsoft.ReportingServices.Rendering.DataRenderer.AtomDataReport,Microsoft.ReportingServices.DataRendering" Visible="false"/>

<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering"/>

<Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

<Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"/>

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"/>

<Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false"/>

<Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering"/>

<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>

</Render>

Spiff SSCertifiable Points: 7766 More actions · Answer 3

Hang on, nope everything looks fine there. However just notice in your original post you said you were doing a Tab delimited export so you'll want to put the <Encoding>ASCII</Encoding> element under the <DeviceInfo> for that extension.

I was assuming a default config file, not sure why I didn't click there is no default Tab delimited option.

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

<OverrideNames>

<Name Language="en-US">TAB delimited</Name>

</OverrideNames>

<Configuration>

<DeviceInfo>

<FieldDelimiter></FieldDelimiter>

<UseFormattedValues>False</UseFormattedValues>

<NoHeader>False</NoHeader>

<FileExtension>txt</FileExtension>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

Spiff SSCertifiable Points: 7766 More actions · Answer 4

Spiff

SSCertifiable

Points: 7766

October 16, 2015 at 6:51 am

#1833782

.

Merlin802 SSC-Addicted Points: 402 More actions · Answer 5

Merlin802

SSC-Addicted

Points: 402

October 16, 2015 at 7:01 am

#1833793

Perfect, worked like a champ! Thanks!!!