rascal
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营养Ą 80;盐 ;有& #26426; 7688;á 22;酸 ;儿& #31461; 3453;ê 53;酱 ;油 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!
Here is an example of what the database field contains:
Bragg营养Ą
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!
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).
========================== ========== ========== ========== ========== =======
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> <% 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> <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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
Response.AddHeader "Content-Type", "text/html;charset=UTF-8"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
Open in new window