Hidden characters added to beginning of csv when exporting report

Question

Hidden characters added to beginning of csv when exporting report

Micah Ritchie

SSC-Addicted

Points: 410
More actions
August 17, 2011 at 11:41 am

#242307

When exporting a report to a csv I am getting three control characters appended to the beginning of the file.
the characters are:
ï»¿
Now here is where it gets stranger. If you open the file in a text editor everything looks fine, however, when it is opened in Excel you see the characters in the first field. It also causes the Access import utility to choke.
The intended use of the file is for our Accounting department to have something to upload into a Hyperion session for our corporate office, and I would like to make it as easy as possible for them.
We are running SQL 2008 (not R2)
Thanks
I attached a sample of what is output
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Those extra characters are the "Byte Order Mark" or "BOM" which Windows helpfully adds to a UTF-8 file even though it is not required and loads of applications can't handle it.

See this Wikipedia page for more info: http://en.wikipedia.org/wiki/UTF-8

There are a couple of workarounds for this with SSRS, detailed here http://www.developerbarn.com/sql-server-reporting-services-help/445-csv-file-export-tip.html but I cannot attest to their validity, so please don't try it on a live server without testing it somewhere else first.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Micah Ritchie SSC-Addicted Points: 410 More actions · Answer 2

Awesome, that worked. I had already started down that route, but had left out the <DeviceInfo></DeviceInfo> tags in the config section.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Micah Ritchie (8/17/2011)
Awesome, that worked. I had already started down that route, but had left out the <DeviceInfo></DeviceInfo> tags in the config section.

Excellent, and good to hear confirmation that the method works, but don't you just wish it was a paramater at the report level!?!?!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 4

Sorry to bump an old thread but I found this linked to via the technet forums, sadly the developer barn link doesn't seem to work, can anyone give me some pointers on what to change? I've had a search on Google and it seems to be more related to UTF-8 vs ASCII rather than removing the BOM.

Any help much appreciated

Micah Ritchie SSC-Addicted Points: 410 More actions · Answer 5

Hi Rob.

seems to be more related to UTF-8 vs ASCII rather than removing the BOM.

Both statements are correct. Making it encode as ASCII removes the BOM.

You have to edit the rsreportserver.config file, and add a new extention to the <Render> section. This is what I found finally worked:

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

<OverrideNames>

<Name Language="en-US">CSV (Plain Text)</Name>

</OverrideNames>

<Configuration>

<DeviceInfo>

<FileExtension>txt</FileExtension>

<Encoding>ASCII</Encoding>

<ExcelMode>False</ExcelMode>

</DeviceInfo>

</Configuration>

</Extension>

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 6

Thanks Micah, that's very helpful. I think for my application ASCII may be a little limiting, someone on the Microsoft SSRS forum said I could use UTF-7 instead of 8.

As UTF-7 isn't standard I've let the recipient know that they've got a choice of this or sticking with the BOM, either way we should be covered

Thanks for replying to an old thread!

Hidden characters added to beginning of csv when exporting report

Cookies on SQLServerCentral