Improve company productivity with a Business Account.Sign Up

x
?
Solved

Imported characters from spreadsheet look strange on ASP page

Posted on 2016-07-30
6
Medium Priority
?
116 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:Aleks
  • 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:Aleks
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 85

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:Aleks
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:Aleks
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Custom Android App Development is the need of an hour for several businesses. If you go through the right firm for Android Application Development, it can make a huge difference to the ways in which customers interact with your business.
AngularJS web development a very simple procedure. So, to put it, in short, AngularJS’ stand out features are – Two-way data binding, MVC structure, directives, templates, dependency injections and testing.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This video teaches users how to migrate an existing Wordpress website to a new domain.

595 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