Re: How can I detect rows with non-ASCII values?

On 1/18/20 3:21 AM, Rocky Ji wrote:
> Hi,
>
> I am asked to highlight rows containing strange characters. All data were
> ingested by a proprietary crawler.
>
> By strange, I mean, question marks, boxes, little Christmas Trees,  solid
> arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII
> English letters.
>
> How do I approach this?
>
> Thanks.

The first thing that you are going to need to find to do this is how the 
data has actually been stored. The strange characters you are describing 
sound like somewhere some data obtained in one character encoding, but 
then interpreted as another.  This is a very old problem, without a 
solution in general except to always know the encoding of your data. It 
goes back to the development of the ASCII character set which is the 
base for most character sets in use today (another major branch is 
EBCDIC, but that has its own issues and you tend to know when you need 
to deal with that).

The A in ASCII is for American, the ASCII character set was designed for 
American (i.e. english) data, and when it when it was developed, memory 
and bandwidth were limited and expensive, so you didn't waste them. 
ASCII was a compact set, using only 7 bits per character, and was fine 
for english information, with the basic alphabet. It became the base 
standard because America was a core part in the initial computer 
development, and had a lot of influence in the initial standards.

While it worked well for American data, it didn't work so well for many 
other countries, so most other countries adopted their own character set 
for use in their country, normally based on ASCII as a base, but adding 
codes to extend the coding to an 8 bit code, keep (at least most of) 
ASCII as the first 128 values.

To exchange data between character machines, you needed to include what 
character set the data was in (or you see some funny words due to the 
mis-match).

Operating systems adopted the concept of Code Pages, which basically 
defined which of the many standard character sets was to be used, and 
some transfer formats actually included as part of the header 
information what character set the data that follows was in. One of 
these was the web pages that were on the Internet.

Later, to try and get out of this mess, a new character encoding was 
invented called Unicode, Unicode initially intended to provide a single 
universal encoding that would let any of the many standard encodings be 
converted to this universal encoding. It was first thought that this 
could be done with a 16 bit character set, but it later needed to be 
enlarged in size as they found out how many different characters there 
really were. While memory isn't quite as precious as it was when ASCII 
was designed, it isn't so abundant that we could just increase the size 
of out data by a factor, so a compact encoding was developed called 
UTF-8, which represents the ASCII characters exactly as the ASCII 
character set, and all the extra characters with multiple bytes.

Because it did make files with the extra characters longer, and it was 
somewhat complicated to work with, and most people only worked with 
documents that could all be encoded in a single character set, its 
adoption was slow, but it now is becoming a norm, but still many things 
are in the old legacy encodings.

If you try to interpret a file that is in one of the legacy encodings as 
Unicode UTF-8, then (if it uses extended characters) it almost certainly 
will create decoding errors (UTF-8 was intentionally designed with 
redundancy in the encoding to easy processing, so many 'random' 
sequences become invalid). If you interpret a file that is in UTF-8 and 
a legacy encoding, you will tend to get some strange out of place 
extended characters.

My first guess is that your proprietary crawler either didn't properly 
detect the page encoding and handle it, ideally it would have converted 
it to UTF-8, but in might also save the data in the original encoding 
and saved what that encoding was, or your read out program isn't 
detecting the character set the data was stored as and processing it 
right. I believe SQLite assumes that 'TEXT' data is UTF-8 encoded, but 
other encodings can be declared or data stored as BLOBs.

What likely should happen is someone (maybe you) needs to read out 
samples of the funny data as a blob, and figure out how the data is 
actually encoded, ideally comparing it to the original page crawled, and 
once you know what the problem was, you can perhaps work on fixing it 
and detecting the records with problems.

One possible issue is that some conversion routines take characters they 
don't know how to handle and replace them with the ASCII Question Mark, 
and if that is what has been stored in the database, it may be very hard 
to distinguish that from an actual question mark in the data.

-- 
Richard Damon

_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users