?
Solved

Imported characters from spreadsheet look strange on ASP page

Posted on 2016-07-30
6
Medium Priority
?
105 Views
Last Modified: 2016-08-01
I imported data from a spreadsheet into an sql db table.
Some of the values have accents, etc. but on the ASP which uses UTF-8 encoding the accent shows with strange characters

This is an example.

table name: users
field : MiddleNm
Database value:   Cléa
How it shows on the ASP webpage: Cléa

I was able to run a query to replace the above accent with:

UPDATE dbo.Users
Set    MiddleNm = replace(MiddleNm, 'é', 'é')

The problem is that there may be other characters that need to be replaced, I was wondering if there may be a query out there that replaces all the accents and other strange characters with the UTF-8 unicode.

Source:  http://www.fileformat.info/info/charset/UTF-8/list.htm
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
Manuel Marienne-Duchêne earned 1000 total points
ID: 41735947
I use this function to encode decode

Function ToUTF8(s)
	    sUTF8 = ""
	    For i=1 To Len(s)
		     unicode = AscW(Mid(s, i, 1))
		     If unicode <= &H007F Then
			     sUTF8 = sUTF8 & Chr(unicode)
		     Else
			     If unicode <= &H07FF Then
				     sUTF8 = sUTF8 & Chr(&HC0 + unicode \ &H40)
				     sUTF8 = sUTF8 & Chr(&H80 + unicode Mod &H40)
			     Else
				     sUTF8 = sUTF8 & Chr(&HE0 + unicode \ &H1000)
				     sUTF8 = sUTF8 & Chr(&H80 + (unicode Mod &H1000) \ &H40)
				     sUTF8 = sUTF8 & Chr(&H80 + (unicode Mod &H1000) Mod &H40)
			     End if
		     End If
	    Next
	    ToUTF8 = sUTF8
    End Function

    Function FromUTF8(sIn) 
            Dim oIn
            Set oIn = CreateObject("ADODB.Stream") 
            oIn.Open 
            oIn.CharSet = "WIndows-1252" 
            oIn.WriteText sIn 
            oIn.Position = 0 
            oIn.CharSet = "UTF-8" 
            FromUTF8 = oIn.ReadText 
            oIn.Close 
    End Function 

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 41735953
I assume that is a function that is added to the Database. The data is already in the database, how can I run a query that would encode those characters with accents, etc  ?
0
 
LVL 6

Assisted Solution

by:Manuel Marienne-Duchêne
Manuel Marienne-Duchêne earned 1000 total points
ID: 41735955
In your database the accent is correct or not ?
if yes response.write toUTF8(result of SQL)
0
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
LVL 83

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 1000 total points
ID: 41736167
what is the database collation? What is your webpage language settings? (in the header)
0
 

Author Comment

by:amucinobluedot
ID: 41736180
If we read the initial question you can see the character in the database is what the actual character should show on the webpage.

I already have a query to replace one. But I want to see if someone already has a query that includes all the characters in the link I included at the bottom. Otherwise I'll have to create it myself.
0
 

Author Comment

by:amucinobluedot
ID: 41738301
I am spreading the points. my DB doesn't support non-Latin characters, so I am just removing the accents. thanks!
0

Featured Post

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question