Avatar of rascal
rascal
Flag for United States of America asked on

ASP: How to convert escaped Chinese chars into readable format?

In an ASP page, I need to read a database field that contains escaped Chinese chars in it (the database field is ntext) and create a CSV file with the resulting content so that it can be later opened in an Excel spreadsheet.

Here is an example of what the database field contains:
Bragg营养无盐有机氨基酸儿童宝宝酱油 6 oz/180ml

I wish to convert each of the escaped chars (such as 营) into their actual Chinese characters before writing them to the CSV file.

Is there a way to actually do this?

Many thanks experts!
ASPWeb Development

Avatar of undefined
Last Comment
Big Monty

8/22/2022 - Mon
Big Monty

try putting the following code at the top of your ASP, it'll set the page for UTF-8 encoding:

<%@Language=VBScript CodePage = 65001%>
<%
Session.CodePage = 65001
Response.charset ="utf-8"
Session.LCID     = 1033 'en-US
%>

Open in new window

rascal

ASKER
Thanks Big Monty, but that did not fix the problem. Keep in mind that I am not attempting to display these characters on the screen. I am writing them to a text file and need them to be converted from their escaped value, to their Chinese value before writing the Chinese char to the text file.

For your reference, below is the entire source file. We first call SetDownload() to set the headers, then we read the database and response.write the contents to the "screen" (csv file).

=========================================================================
<%@Language=VBScript CodePage = 65001%>
<%
Session.CodePage = 65001
Response.charset ="utf-8"
Session.LCID     = 1033 'en-US
%>
<!--#include file=dbconn.asp-->

<%
If request.cookies("Department")="" Then
	Response.Redirect "../../myaccount.asp"
else
	if request.Cookies("Department")<>"Management" Then
		'Response.Write "Session(Department): " & request.Cookies("Department")
		a=split(request.Cookies("Department"),";")
		if a(2)="True" then
		else
			Response.Redirect "../../myaccount.asp"
		end if
		
		set objConnmanage = Server.CreateObject("ADODB.Connection")
		strConnectionmanage = "Provider=SQLOLEDB;Data Source=" & DatabaseIP & ";User ID=" & user & ";Password='" & pass & "';Initial Catalog=" & MerchDBName
		objConnmanage.Open strConnectionmanage

		sql="SELECT managementmenu FROM Employees"
		sql=sql & " WHERE EmployeeID=" & Request.Cookies("EmployeeID")
		Set objRSmanage=objConnmanage.Execute(sql)
		options=split(objRSmanage("managementmenu"),";")

		Set objRSmanage=nothing
		objConnmanage.Close
		Set objConnmanage=nothing
	else
		options=array(1,1,1,1,1)
	end if
End if

'-------------------------------
' ReplaceSpecChars
'-------------------------------
function ReplaceSpecChars(ByVal sString)
on error resume next

	' replace any values within the string that we are using for our delimiter
	'sString = replace(sString,sColDelimiter,"")
	
	' Replace any double-quotes within the string passed
	'sString = replace(sString,"""","""""")
	sString = replace(sString,"""","'")
	
	' wrap value in quotes for use in Excel so that we don't have to convert commas
	sString = """" & sString & """" 	
	
	ReplaceSpecChars = sString 
end function
'************************************************
' SetDownload
'************************************************
sub SetDownload()
	on error resume next
	dim sAgent,s_pretty
	
	sAgent = lcase(trim(request.ServerVariables("HTTP_USER_AGENT")))
	s_pretty="PopularProducts_download.csv"
	
	Response.Clear
		
	response.addHeader "pragma","public"
	response.expires = 0
	response.addHeader "cache-control","must-revalidate, post-check=0, pre-check=0"
	response.addHeader "cache-control","public"

	Response.ContentType = "application/force-download"
	
	if InStr(1,sAgent,"msie",vbTextCompare)>0 AND Instr(1,sAgent,"win",vbTextCompare)>0 then 
		Response.AddHeader "content-disposition","inline; filename=" & s_pretty
	else
		Response.AddHeader "content-disposition","attachment; filename=" & s_pretty
	end if

	response.addHeader "Content-Transfer-Encoding","binary"
	'response.addHeader "Content-Length",Len(s_rows)

end sub

'************************************************
' DownloadResults
'************************************************
sub DownloadResults()
	on error resume next
	
	dim sColDelimiter,s_rows
	sColDelimiter = ","

	' init output excel row header
'	s_rows = """PID""" & sColDelimiter & """Name""" & sColDelimiter & """Qty""" & sColDelimiter & """R""" & sColDelimiter & """Code1""" & sColDelimiter &_
'			"""Memo""" & vbCrlf
	s_rows = """PID""" & sColDelimiter &_
	         """UPC""" & sColDelimiter &_
	         """Brand""" & sColDelimiter &_
	         """Name""" & sColDelimiter &_
	         """Description""" & sColDelimiter &_
			 """Qty""" & sColDelimiter &_
			 """R""" & sColDelimiter &_
			 """Expires""" & sColDelimiter &_
			 """Location""" & sColDelimiter &_
			 """Chinese""" & sColDelimiter &_
			 """Memo""" & vbCrlf

	SetDownload() ' turn on the force download
	' write the first row
	response.write(s_rows)
	s_rows=""

	while objRS.EOF=false and err.number=0 
	
		s_rows = ReplaceSpecChars(objRS("ProductId")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("UPC")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("Brand")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("ProductName")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("ProductTitle")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("Quantity")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("OnReserve")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("ExpirationDate")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("Location")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("Chinese")) & sColDelimiter & _
				 ReplaceSpecChars(objRS("ProductMemo")) & vbcrlf
		
		if s_rows<>"" then 
			response.write(s_rows)
			s_rows=""
		end if 
		
		objRS.movenext
		
	wend
	
	objRS.close
	set objRS = nothing
	objConn.close
	set objConn=nothing
	
	' done.
	response.end 

end sub
%>


<%
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB;Data Source=" & DatabaseIP & ";User ID=" & user & ";Password='" & pass & "';Initial Catalog=" & MerchDBName
objConn.Open strConnection

'If Request.Querystring("action")="Add" and (request.Cookies("EmployeeID")="132" or Request.cookies("EmployeeID")="158" or Request.cookies("EmployeeID")="123" or 'request.Cookies("Department")="Management") Then
If Request.Querystring("action")="Add" then
	sql="INSERT INTO PopularProducts (ProductID) VALUES ('" & Request.QueryString("PID") & "')"
	objConn.Execute(sql)
	Response.Redirect "popular.asp"
Elseif Request.QueryString("action")="delete" and (request.Cookies("EmployeeID")="132" or Request.cookies("EmployeeID")="158" or Request.cookies("EmployeeID")="123" or request.Cookies("Department")="Management") Then
	sql="DELETE FROM PopularProducts WHERE ProductID='" & Request.QueryString("PID") & "'"
	objConn.Execute(sql)
	Response.Redirect "popular.asp"
Elseif Request.QueryString("action")="update" and (request.Cookies("Department")="Management" or options(1) = "1") then
	sMemo = Replace(Request.Form("memo_" & Request.QueryString("PID")),"'","''")
	sql="UPDATE Products SET productmemo = N'" & sMemo & "' WHERE ProductID='" & Request.QueryString("PID") & "'"
	objConn.Execute(sql)
	Response.Redirect "popular.asp"
Elseif Request.QueryString("action")="updateall" and (request.Cookies("Department")="Management" or options(1) = "1") then
	sPIDLIST=request.form("pidlist")
	response.write(sPIDLIST)
	if sPIDLIST <> "" then
		sPIDArray = split(sPIDList,",")
		for each s in sPIDArray
			sOrigMemo = Replace(Request.Form("origmemo_" & s),"'","''")
			sNewMemo = Replace(Request.Form("memo_" & s),"'","''")
			if sOrigMemo <> sNewMemo then
				sql="UPDATE Products SET productmemo = N'" & sNewMemo & "' WHERE ProductID='" & s & "'"
				objConn.Execute(sql)
			end if
		next
		Response.Redirect "popular.asp"
	end if
Elseif Request.QueryString("action")="download" then
	Set objRS = Server.CreateObject("ADODB.Recordset")
	sql = "SELECT * FROM PopularProductsView ORDER BY PRODUCTID"
	objRS.Open sql, objConn
	DownloadResults()
else 'show form
Set objRS = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM PopularProductsView ORDER BY PRODUCTID"
objRS.Open sql, objConn
%>

<html>
<head>
<title>.// DYNAMIC BRIDGE : Popular Products</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../system.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/javascript">
//*******************
// Submit()
//*******************
function SubmitUpdate(sPID)
{
	
	document.updateform.btnSubmit.disabled=true;
	document.updateform.action += "&pid=" + sPID;
	setTimeout('document.updateform.submit()',10);
	return true;
}
//*******************
// SubmitUpdateAll()
//*******************
function SubmitUpdateAll()
{
	
	document.updateform.btnSubmit2.disabled=true;
	document.updateform.btnSubmit3.disabled=true;
	document.updateform.action += "all";
	setTimeout('document.updateform.submit()',10);
	return true;
}
</script>
</head>

<body>
<!--#include file="../header.asp"-->
<table width="760" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
  <tr>
    <td width="150" valign="top" bgcolor="#EEEEEE"><!--include file="../purchasing_menu.asp" --></td>
    <td valign=top> <br>
	<form ACTION="popular.asp?action=update" name="updateform" method="post">
      <table width="98%" border="1" cellspacing="0" cellpadding="3" align="center" bordercolordark="#ffffff" bordercolorlight="#B4B4B4">
        <tr> 
          <td colspan="9" bgcolor="#CCCCFF" class="largeB">Popular Products</td>
        </tr>
        <TR><TD colspan="9" bgcolor=#CCCCFF" class="mediumB"><Font color=blue>Blue</FONT> font indicates quantity is greater than reserve by 30<BR>
        <FONT COLOR=red>Red</FONT> font indicates quantity less than reserve</TD></TR>
		<tr><td colspan="9"><a href="popular.asp?action=download"><strong><u>DOWNLOAD</u></strong></a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<% if request.Cookies("Department")="Management" or options(1) = "1" then %><input name="btnSubmit3" type="button" class="formButton" value="Update All" onClick="SubmitUpdateAll()"><%end if%>
</td></tr>
        <tr bgcolor="#DBDBDB" class="mediumB"> 
          <td > PID</td>
          <td > UPC</TD>
          <td > Brand</TD>
          <td > Name</TD>
          <td > Description</TD>
          <td > Qty</td>
          <TD > R</td>
          <TD > Expires</TD>
          <TD > Location</TD>
          <TD > Chinese</TD>
          <TD > Memo </TD>
          <TD > <img src=../images/delete.gif></TD>
        </tr>
        <%
        i=1
		sPIDLIST=""
        While NOT objRS.EOF
			if int(objRS("Quantity"))>int(objRS("OnReserve"))+30 then
				color="blue"
				font="<B>"
			elseif int(objRS("quantity"))<int(objRS("OnReserve")) Then
				color="red"
				font="<B>"
			else
				color="black"
				font=""
			end if
			sPID = objRS("ProductID")
			if sPIDLIST<>"" then sPIDLISt = sPIDLIST & ","
			sPIDList = sPIDLISt & sPID 
		%>
        <tr> 
          <td bgcolor="#F6F6F6"><FONT COLOR=<%=color%>><%=font%> <%=objRS("ProductID")%></font></td>
          <td bgcolor="#FBFBFF"><FONT COLOR=<%=color%>><%=font%> <%=objRS("UPC")%></font></td>
          <td bgcolor="#F6F6F6"><FONT COLOR=<%=color%>><%=font%> <%=objRS("Brand")%></font></td>
          <td bgcolor="#FBFBFF"><FONT COLOR=<%=color%>><%=font%> <%=objRS("ProductName")%></font></td>
          <td bgcolor="#F6F6F6"><FONT COLOR=<%=color%>><%=font%> <%=objRS("ProductTitle")%></font></td>
          <td bgcolor="#FBFBFF"><FONT COLOR=<%=color%>><%=font%> <%=objRS("Quantity")%></font></td>
          <td bgcolor="#F6F6F6"><FONT COLOR=<%=color%>><%=font%> <%=objRS("Onreserve")%></font></td>
          <td bgcolor="#FBFBFF"><FONT COLOR=<%=color%>><%=font%> <%=objRS("ExpirationDate")%></font></td>
          <td bgcolor="#F6F6F6"><FONT COLOR=<%=color%>><%=font%> <%=objRS("Location")%></font></td>
          <td bgcolor="#FBFBFF"><FONT COLOR=<%=color%>><%=font%> <%=objRS("Chinese")%></font></td>
          <td bgcolor="#F6F6F6"><FONT COLOR=<%=color%>><%=font%><% if request.Cookies("Department")="Management" or options(1) = "1" then %><textarea name="memo_<%=sPID%>" cols="" rows="1" style="width:250px;font-size:11px;line-height:1.2;"><%=Server.HTMLEncode(objRS("ProductMemo"))%></textarea><%else%> <%=objRS("ProductMemo")%><%end if%></font></td>
          <td bgcolor="#FBFBFF"><%If request.Cookies("EmployeeID")="132" or request.Cookies("EmployeeID")="158" or request.Cookies("Department")="Management" Then%>
          <FONT COLOR=<%=color%>><%=font%> <a href="popular.asp?PID=<%=objRS("ProductID")%>&action=delete"><img src=../images/delete.gif border=0></a></font>
          <%end if%>
		  <% if request.Cookies("Department")="Management" or options(1) = "1" then %><br><br><input name="btnSubmit" type="button" class="formButton" value="Upd" onClick="SubmitUpdate(<%=sPID%>)"><%end if%>
		  <input type="hidden" name="origmemo_<%=sPID%>" value='<%=Server.HTMLEncode(objRS("ProductMemo"))%>'>
		  </td>
        </tr>
        <%
          objRS.MoveNext
        Wend
        %>
      </table><input type="hidden" name="pidlist" value="<%=sPIDLIST%>">
		<% if request.Cookies("Department")="Management" or options(1) = "1" then %><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input name="btnSubmit2" type="button" class="formButton" value="Update All" onClick="SubmitUpdateAll()"><%end if%>
	  </form>
	  
      <%'If request.Cookies("EmployeeID")="132" or request.Cookies("EmployeeID")="158" or Request.cookies("EmployeeID")="123" or request.Cookies("Department")="Management" Then%>
      <FORM ACTION=popular.asp?action=add Method=get>
        <table width="98%" border="1" align="center" cellpadding="3" cellspacing="0" bordercolorlight="#B4B4B4" bordercolordark="#ffffff">
          <tr bgcolor="#666666"> 
            <td height="15" bgcolor="#CCCCFF" class="largeB"> Enter ProductID to add to list</td>
          </tr>
          <tr> 
            <td height="2" class="mediumB"> <div align="center">ProductID: 
                <input name="PID" type="text" class="form"><BR>
                <input name="action" type="submit" class="formButton" value="Add">
              </div></td>
          </tr>
        </table>
      </FORM>
      <%'end if%>
	  
    </td>
  </tr>
</table>
<!--#include file="../footer.htm"-->
</body>
</html>
<%
objRS.Close
Set objRS=nothing
end if

'Objconn.Close
'Set objConn=nothing
%>

<script LANGUAGE="JavaScript">
<!--

function verify() {
  c = confirm('Are you sure you want to delete this item?');
  return c;
}

// -->
</script>

Open in new window

ASKER CERTIFIED SOLUTION
Big Monty

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Big Monty

I had the same problem awhile ago, streaming multi byte chars to a file and when I used the code below, it worked for me:

Response.AddHeader "Content-Type", "text/html;charset=UTF-8"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck