Thread: CSV File Export Tip...

If you ever export your data to CSV using Reporting Services, you might notice that the file does not open correctly in Excel (which should usually recognize CSV format). You might also note that programs expecting a standard CSV file will not be able to use CSV files exported from Reporting Services. The reason for this is that the encoding is set to Unicode by default instead of ASCII.

Here is a tip to solve that problem. For SQL Reporting Services 2005, change the config file on the report server as follows:

1. Open the rsreportserver.config file with a text editor and look for this block:
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRen derer.CsvReport,Microsoft. ReportingServices.CsvRendering"/>

2. Comment out that section with <!-- and --> marks.

3. Directly below that comment section, add the following:

<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRen derer.CsvReport,
Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>

4. Run IISRESTART to reload the configuration file.

If you do not have SQL Reporting Services 2005, you can edit the URL. If you'll notice, the URL strings have parameters in them telling the report how to render. You can force the report to render as CSV and change the encoding to ASCII by placing these in the URL string.

1. Replace this block in the URL:

Code:

rs%3aCommand=Render&rs%3aFormat=HTML4.0

with

Code:

rs%3aCommand=Render&rs%3aFormat=csv

2. Then add the following bit:

Code:

rc:Encoding=ASCII

3. Make sure to append using the ampersand &

4. It should prompt you to download the report. Just open or save it, and your encoding will be correct.

Hi There!

Which process are you using? Were you able to change the Report Server configuration, or did you have to resort to changing the URL? Does this happen every time you export the file as CSV?

P.S. I just re-tested adding the rc:Encoding=ASCII in the url just in case that was your method, and it does work but... you have to remember to add an ampersand before the rc:Encoding=ASCII (and after it if it is not at the end of your URL).

Hello Again,

I also have used the URL option, and so far find that it works without changing the extension to csv.txt. So... I am wondering if you are doing this somehow differently. Did you also change the rendering format tag? In other words change:

Code:

rs%3aCommand=Render&rs%3aFormat=HTML4.0

to

Code:

rs%3aCommand=Render&rs%3aFormat=csv

That should ensure that you have the correct format.

The only thing I can think of is that the machine in question might associate CSV with a TXT file instead of Excel CSV. You might check the file associations, or if you do not have Excel (or your users do not), that might be the cause.

As for the larger question of whether you can force the extension, I do not believe that you can do so. I know if you set up a suscription you can force the name of the file, but again... I am not sure that the extension would change.

You can try the URL change. I posted steps for that as well just in case anyone had trouble with changing the configuration file.

What version of Reporting Services are you using? That will make all the difference, because if you are using the version that comes with VS 2003, the configuration change will not work.

Hi Harish,

The dialog will still show up if you change the URL... that is true. You will be prompted to open or save. Once you have saved the file try opening it, and you should see that the formatting is correct.

If you want to make this change for reports on a subscription, you will have to make the change in the configuration file. Make sure that you are changing the file on the server (and if you have more than one installed, you'll have to change it on all of them).

Double check your config changes. Make sure that you have commented out this section:

Code:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRen derer.CsvReport,Microsoft. ReportingServices.CsvRendering"/>

Futhermore make sure that you have added this code:

Code:

<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,
Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>

Finally, make sure that IIS was restarted for the changes to take effect.

Good luck, and let me know if this works for you.