August 17, 2011 at 11:41 am
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
August 17, 2011 at 5:03 pm
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);August 17, 2011 at 5:44 pm
Awesome, that worked. I had already started down that route, but had left out the <DeviceInfo></DeviceInfo> tags in the config section.
August 18, 2011 at 4:47 am
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);May 21, 2012 at 2:22 am
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 🙂
May 21, 2012 at 11:50 am
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>
May 22, 2012 at 5:23 am
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply