?
Solved

Excel ODBC Query - Symbols - encoding issue?

Posted on 2011-05-12
2
Medium Priority
?
809 Views
Last Modified: 2012-06-22
I use an ODBC connection to pull in data into Excel 2010 from a database. There are a lot of "copyright" symbols in the text, like women’s and Rider™

I need these converted back to read womens and Rider in excel in in order for someone else to import into another system. I believe it has something to do with encoding (needs to be UTF-8??), but I'm not very familiar with the topic.
0
Comment
Question by:Boardshop
  • 2
2 Comments
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 35752474
i guess the easiest solution for your problem is to use the macro as proposed at

http://www.world-gazetteer.com/wg.php?x=1129163518&men=stdl&lng=en&gln=xx&dat=32&srt=npan&col=aohdq

it searches and replaces the symbols. The problem indeed is introduced because of Excel being not able to understand the encoding.
Because the ampersand codes used in your example I am not sure if this macro will replace all the symbols you are using, but feel free to add new symbols as you see fit.


Sub utf8()
'
' utf8 Makro
' Convert UTF-8 characters to Excel format
'
' key combination: Strg+w
'

' a acute big
    Cells.Replace What:="Á", Replacement:="Á", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a acute small
    Cells.Replace What:="á", Replacement:="á", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a brevis small
    Cells.Replace What:="ă", Replacement:=ChrW(259), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a cedilla big
    Cells.Replace What:="Ä„", Replacement:=ChrW(260), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a cedilla small
    Cells.Replace What:="Ä…", Replacement:=ChrW(261), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a circumflex big
    Cells.Replace What:="Â", Replacement:="Â", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a circumflex small
    Cells.Replace What:="â", Replacement:="â", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a grave small
    Cells.Replace What:="à", Replacement:="à", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a macron big
    Cells.Replace What:="Ä€", Replacement:=ChrW(256), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a macron small
    Cells.Replace What:="ā", Replacement:=ChrW(257), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a ring big
    Cells.Replace What:="Ã…", Replacement:="Å", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a ring small
    Cells.Replace What:="Ã¥", Replacement:="å", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a tilde small
    Cells.Replace What:="ã", Replacement:="ã", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a umlaut big
    Cells.Replace What:="Ä", Replacement:="Ä", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a umlaut small
    Cells.Replace What:="ä", Replacement:="ä", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' ae ligature small
    Cells.Replace What:="æ", Replacement:="æ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' ae ligature big
    Cells.Replace What:="Æ", Replacement:="Æ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c acute big
    Cells.Replace What:="Ć", Replacement:=ChrW(262), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c acute small
    Cells.Replace What:="ć", Replacement:=ChrW(263), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c caron big
    Cells.Replace What:="Č", Replacement:=ChrW(268), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c caron small
    Cells.Replace What:="č", Replacement:=ChrW(269), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c cedilla big
    Cells.Replace What:="Ç", Replacement:="Ç", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c cedilla small
    Cells.Replace What:="ç", Replacement:="ç", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' d dash big
    Cells.Replace What:="Đ", Replacement:=ChrW(272), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' d dash small
    Cells.Replace What:="Ä‘", Replacement:=ChrW(273), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' dh small small
    Cells.Replace What:="ð", Replacement:="ð", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e acute big
    Cells.Replace What:="É", Replacement:="É", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e acute small
    Cells.Replace What:="é", Replacement:="é", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e caron small
    Cells.Replace What:="Ä›", Replacement:=ChrW(283), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e cedilla small
    Cells.Replace What:="Ä™", Replacement:=ChrW(281), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e circumflex small
    Cells.Replace What:="ê", Replacement:="ê", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e grave big
    Cells.Replace What:="È", Replacement:="È", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e grave small
    Cells.Replace What:="è", Replacement:="è", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e macron big
    Cells.Replace What:="Ä’", Replacement:=ChrW(274), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e macron small
    Cells.Replace What:="Ä“", Replacement:=ChrW(275), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e point small
    Cells.Replace What:="Ä—", Replacement:=ChrW(279), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e turned big
    Cells.Replace What:="Ə", Replacement:=ChrW(399), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e turned small
    Cells.Replace What:="É™", Replacement:=ChrW(601), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
    Cells.Replace What:="ǝ", Replacement:=ChrW(601), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e umlaut small
    Cells.Replace What:="ë", Replacement:="ë", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' g brevis small
    Cells.Replace What:="ÄŸ", Replacement:=ChrW(287), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' g point big
    Cells.Replace What:="Ä ", Replacement:=ChrW(288), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' g point small
    Cells.Replace What:="Ä¡", Replacement:=ChrW(289), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' h macron big
    Cells.Replace What:="Ħ", Replacement:=ChrW(294), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' h macron small
    Cells.Replace What:="ħ", Replacement:=ChrW(295), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i acute big
    Cells.Replace What:="Í", Replacement:="Í", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i acute small
    Cells.Replace What:="í", Replacement:="í", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i brevis small
    Cells.Replace What:="Ä­", Replacement:=ChrW(301), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i circumflex big
    Cells.Replace What:="ÃŽ", Replacement:="Î", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i circumflex small
    Cells.Replace What:="î", Replacement:="î", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i grave small
    Cells.Replace What:="ì", Replacement:="ì", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i macron big
    Cells.Replace What:="Ī", Replacement:=ChrW(298), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i macron small
    Cells.Replace What:="Ä«", Replacement:=ChrW(299), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i pointed big
    Cells.Replace What:="Ä°", Replacement:=ChrW(304), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i pointless small
    Cells.Replace What:="ı", Replacement:=ChrW(305), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i umlaut small
    Cells.Replace What:="ï", Replacement:="ï", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' k cedilla big
    Cells.Replace What:="Ķ", Replacement:=ChrW(310), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' k cedilla small
    Cells.Replace What:="Ä·", Replacement:=ChrW(311), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' l cedilla small
    Cells.Replace What:="ļ", Replacement:=ChrW(316), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' l slash big
    Cells.Replace What:="Ł", Replacement:=ChrW(321), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' l slash small
    Cells.Replace What:="Å‚", Replacement:=ChrW(322), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n acute small
    Cells.Replace What:="Å„", Replacement:=ChrW(324), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n caron small
    Cells.Replace What:="ň", Replacement:=ChrW(328), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n cedilla small
    Cells.Replace What:="ņ", Replacement:=ChrW(326), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n tilde big
    Cells.Replace What:="Ñ", Replacement:="Ñ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n tilde small
    Cells.Replace What:="ñ", Replacement:="ñ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o acute big
    Cells.Replace What:="Ó", Replacement:="Ó", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o acute small
    Cells.Replace What:="ó", Replacement:="ó", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o brevis big
    Cells.Replace What:="ÅŽ", Replacement:=ChrW(334), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o brevis small
    Cells.Replace What:="ŏ", Replacement:=ChrW(335), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o circumflex small
    Cells.Replace What:="ô", Replacement:="ô", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o double acute big
    Cells.Replace What:="Ö", Replacement:=ChrW(336), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o double acute small
    Cells.Replace What:="Å‘", Replacement:=ChrW(337), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o grave small
    Cells.Replace What:="ò", Replacement:="ò", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o macron big
    Cells.Replace What:="Ō", Replacement:=ChrW(332), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o macron small
    Cells.Replace What:="ō", Replacement:=ChrW(333), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o slash big
    Cells.Replace What:="Ø", Replacement:="Ø", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o slash small
    Cells.Replace What:="ø", Replacement:="ø", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o tilde small
    Cells.Replace What:="õ", Replacement:="õ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o umlaut small
    Cells.Replace What:="ö", Replacement:="ö", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' r caron big
    Cells.Replace What:="Ř", Replacement:=ChrW(344), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' r caron small
    Cells.Replace What:="Å™", Replacement:=ChrW(345), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' r cedilla small
    Cells.Replace What:="Å—", Replacement:=ChrW(343), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s acute big
    Cells.Replace What:="Åš", Replacement:=ChrW(346), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s acute small
    Cells.Replace What:="Å›", Replacement:=ChrW(347), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s caron big
    Cells.Replace What:="Å ", Replacement:=ChrW(352), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s caron small
    Cells.Replace What:="Å¡", Replacement:=ChrW(353), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s cedilla big
    Cells.Replace What:="Åž", Replacement:=ChrW(350), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s cedilla small
    Cells.Replace What:="ÅŸ", Replacement:=ChrW(351), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' sz ligature small
    Cells.Replace What:="ß", Replacement:="ß", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' t cedilla big
    Cells.Replace What:="Å¢", Replacement:=ChrW(354), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' t cedilla small
    Cells.Replace What:="Å£", Replacement:=ChrW(355), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' thorn big
    Cells.Replace What:="Þ", Replacement:="Þ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u acute big
    Cells.Replace What:="Ú", Replacement:="Ú", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u acute small
    Cells.Replace What:="ú", Replacement:="ú", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u brevis big
    Cells.Replace What:="Ŭ", Replacement:=ChrW(364), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u brevis small
    Cells.Replace What:="Å­", Replacement:=ChrW(365), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u cedilla small
    Cells.Replace What:="ų", Replacement:=ChrW(371), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u circumflex small
    Cells.Replace What:="û", Replacement:="û", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u double acute small
    Cells.Replace What:="ű", Replacement:=ChrW(369), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u grave small
    Cells.Replace What:="ù", Replacement:="ù", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u macron big
    Cells.Replace What:="Ū", Replacement:=ChrW(362), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u macron small
    Cells.Replace What:="Å«", Replacement:=ChrW(363), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u ring small
    Cells.Replace What:="ů", Replacement:=ChrW(367), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u umlaut big
    Cells.Replace What:="Ãœ", Replacement:="Ü", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u umlaut small
    Cells.Replace What:="ü", Replacement:="ü", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' y acute small
    Cells.Replace What:="ý", Replacement:="ý", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z acute small
    Cells.Replace What:="ź", Replacement:=ChrW(378), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z caron big
    Cells.Replace What:="Ž", Replacement:=ChrW(381), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z caron small
    Cells.Replace What:="ž", Replacement:=ChrW(382), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z point big
    Cells.Replace What:="Å»", Replacement:=ChrW(379), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z point small
    Cells.Replace What:="ż", Replacement:=ChrW(380), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' cedilla
    Cells.Replace What:="̨", Replacement:=ChrW(808), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' diaresis
    Cells.Replace What:="̈", Replacement:=ChrW(776), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' halfcircle, right open
    Cells.Replace What:="´", Replacement:=ChrW(703), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' macron
    Cells.Replace What:="Ì„", Replacement:=ChrW(772), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' macron below
    Cells.Replace What:="̱", Replacement:=ChrW(817), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' apostroph
    Cells.Replace What:="ʼ", Replacement:=ChrW(700), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True




End Sub

Open in new window


0
 
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 2000 total points
ID: 35752495
a list of unicode characters and their ampersand codes can be found at

http://natural-innovations.com/wa/doc-charset.html

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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