Database Problem

kepler has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I'm converting a database (that I thought it was utf8) to a txt flat database; here are the results I'm getting:

guozi|5|41.102188|122.502444|CH|19||Guo?zi|
e|5|41.102188|122.502444|CH|19||¹ø?×Ó|
zaomugou|5|41.144313|122.489929|CH|19||Zaomugou|
|5|41.144313|122.489929|CH|19||ÔæÄ¾¹µ|
wangjia|5|41.417097|122.412761|CH|19||Wangjia|
c|5|41.417097|122.412761|CH|19||Íõ¼Ò|
shengli|5|41.393546|122.456116|CH|19||Shengli|
e|5|41.393546|122.456116|CH|19||Ê¤Àû|
minjitun|5|41.375362|122.471185|CH|19||Minjitun|
e|5|41.375362|122.471185|CH|19||Ãñ¼¯ÍÍ|
zhangjiagou|5|41.413368|122.487095|CH|19||Zhangjiagou|
|5|41.413368|122.487095|CH|19||ÕÅ¼Ò¹µ|
yahua|5|41.42516|122.473109|CH|19||Yahua|
[download]

As you can see, some of the lines have strange symbols (utf8?) - not many. I want to exclude them. What kind of match pattern can I use to exclude them? Kind regards, Kepler

Comment on Database Problem Download Code

Replies are listed 'Best First'.
Re: Database Problem by John M. Dlugosz (Monsignor) on May 20, 2011 at 13:31 UTC
Show the hex codes of the strings you are getting from the database. Then I might be able to identify the correct code page for you. Or you can take a guess, perhaps Windows-1252 or whatever is normal for your part of the world. Use the Encoding module to process the string from the database. Or maybe it is indeed UTF8 as you thought, but is coming through as individual bytes. The Encoding module will take care of that too: decode UTF8, rather than transferring the bytes directly to a Perl string. Just because it is UTF-8 data doesn't mean the string is "marked" properly by the database implementation; it could just be treating it as a string of individual bytes. Then Perl thinks it is a legacy 8-bit string and sees each byte of the multi-byte UTF-8 character as a separate character. —John	[reply]
Re: Database Problem by rovf (Priest) on May 20, 2011 at 14:00 UTC
This question was already posted at http://perlguru.com/gforum.cgi?post=56756. If you REALLY think that you need crossposting, always quote the link to the other postings of your problem! -- Ronald Fischer <ynnor@mm.st>	[reply] [d/l]
Re^2: Database Problem by kepler (Scribe) on May 20, 2011 at 14:33 UTC
Hi, Sorry - I didn't mean nothing wrong. Kind regards, Kepler	[reply]
Re: Database Problem by John M. Dlugosz (Monsignor) on May 20, 2011 at 19:55 UTC
I think there are two separate places in the code path where encoding was wrong or transcoding got performed wrong. Once where you read the original content and stuffed it into Perl strings, and again when transforming that to printable output which introduced some extra '?' but otherwise kept the bytes the same. You might try Code Page 936 or "GBK" for your original data, not UTF-8. Again, printing the raw data you got from the source database as hex, with no further processing, would help. You can also print the raw bytes to a file, and try loading that file into a text editor with different encodings specified and see which one makes sense. You need to read Chinese to know when it "makes sense" though. Does “脭忙脛戮鹿碌” look like the correct content? It's all valid simplified Chinese characters, but it doesn't appear to make any sense out of context.	[reply]