Solved

Imported characters from spreadsheet look strange on ASP page

Posted on 2016-07-30
6
89 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 250 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 250 total points
ID: 41735955
In your database the accent is correct or not ?
if yes response.write toUTF8(result of SQL)
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 81

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 250 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will learn how to dynamically set the form action using jQuery.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

751 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