Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Exporting strange characteres to Excel from SQL Server

Reply
Thread Tools

Exporting strange characteres to Excel from SQL Server

 
 
junk.rail junk.rail is offline
Junior Member
Join Date: Jun 2010
Posts: 4
 
      07-12-2010
Hi guys,

I got 2 days trying to figure out what is happening. I hope, that you can help me.

I got this table:

USE [Bicentenario]
GO
/****** Object: Table [bice].[indices] Script Date: 07/12/2010 14:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bice].[indices](
[id_i] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nombre] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fuente] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nota] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[id_t] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I am getting basiclly the columds "nombre", "fuente" and "nota" from the code behind from a id_i which I get via POST. Once I populate the strings. I build a StringBuilder and HTML is beign inserted on it.

Therefore I add the contentType like text/html and declare the charset like this: Response.ContentEncoding = Encoding.UTF8;

Because I need to use "" character and the acent ",,,," because the spanish language.

At the end I just display the Open/Save dialog adding a attachment header as content-disposition, like this.

Response.AddHeader("Content-Disposition", "attachment; filename=Indicador.xls");

Eveything is working fine, in fact get the data from the database on the Excel file exactyl as I want. The problem that I get is, because sometimes on the excel file, I got strange characteres because the ",,,," or "" sentences. Note: I have 2 buttons, one for displaying the data on HTML and other for exporting as xls file. The funny stuff is that as HTML always is beign displayed right (it means there should nt be any trouble with the table). The problem it's the excel file.

If I open the xls file, save it like CSV, edit the csv file on notepad and pulse "SaveAs". There, the codeification it's already UTF-8 (the one that I need) and then overwrite the file (leave the UTF-8 codification).

Afterwards I will bea able to open the file with the charset that I need, but of course I don't want the user to do this whole proccess. As I said, it happens only with some rows of the same table and the rest work perfectly.

I cannot figure out why with some rows it's saved right and sometimes it doesnt.

The code-behind cycle is always the same for every row on my table.

what it could be guys? Maybe the table definition?

Thank you in advance

On a few rows whereby I get the wrong data
 
Reply With Quote
 
 
 
 
junk.rail junk.rail is offline
Junior Member
Join Date: Jun 2010
Posts: 4
 
      07-13-2010
Quote:
Originally Posted by junk.rail
Hi guys,

I got 2 days trying to figure out what is happening. I hope, that you can help me.

I got this table:

USE [Bicentenario]
GO
/****** Object: Table [bice].[indices] Script Date: 07/12/2010 14:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [bice].[indices](
[id_i] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nombre] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fuente] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nota] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[id_t] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I am getting basiclly the columds "nombre", "fuente" and "nota" from the code behind from a id_i which I get via POST. Once I populate the strings. I build a StringBuilder and HTML is beign inserted on it.

Therefore I add the contentType like text/html and declare the charset like this: Response.ContentEncoding = Encoding.UTF8;

Because I need to use "" character and the acent ",,,," because the spanish language.

At the end I just display the Open/Save dialog adding a attachment header as content-disposition, like this.

Response.AddHeader("Content-Disposition", "attachment; filename=Indicador.xls");

Eveything is working fine, in fact get the data from the database on the Excel file exactyl as I want. The problem that I get is, because sometimes on the excel file, I got strange characteres because the ",,,," or "" sentences. Note: I have 2 buttons, one for displaying the data on HTML and other for exporting as xls file. The funny stuff is that as HTML always is beign displayed right (it means there should nt be any trouble with the table). The problem it's the excel file.

If I open the xls file, save it like CSV, edit the csv file on notepad and pulse "SaveAs". There, the codeification it's already UTF-8 (the one that I need) and then overwrite the file (leave the UTF-8 codification).

Afterwards I will bea able to open the file with the charset that I need, but of course I don't want the user to do this whole proccess. As I said, it happens only with some rows of the same table and the rest work perfectly.

I cannot figure out why with some rows it's saved right and sometimes it doesnt.

The code-behind cycle is always the same for every row on my table.

what it could be guys? Maybe the table definition?

Thank you in advance

On a few rows whereby I get the wrong data
I just solved it using a Response.Write(sb)

I realize that the content is corrupted when I modify some tag's like

sb.append("<td><center>" + value + "</center></td>");

to

sb.append("<td align='right'>" + value + "</td>");

What it could be.
 

Last edited by junk.rail; 07-13-2010 at 05:20 PM..
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting to Excel using new version of Excel Doogie ASP .Net 1 11-19-2008 09:10 PM
Path longer than 256 characteres? Alex D. ASP .Net 0 07-10-2006 06:09 PM
Exporting Excel Data To SQL 2000 through asp.net amitshinde02@gmail.com ASP .Net 2 02-04-2006 07:17 AM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM
Perl output displaying ??? characteres Jay Perl Misc 3 10-20-2004 11:09 PM



Advertisments