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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
