pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SPAM]-D] How to find broken UTF-8 characters ?
6+ messages / 4 participants
[nested] [flat]
* Re: [SPAM]-D] How to find broken UTF-8 characters ?
@ 2010-04-26 10:12 silly sad <sad@bankir.ru>
2010-04-26 12:41 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: silly sad @ 2010-04-26 10:12 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On 04/26/10 04:12, Andreas wrote:
> Excel files
> pgAdmin
> Access.
looks like a complete offtopic
> How can I find those broken UTF-8 characters?
> How can I get rid of them?
iconv -c
BUT
u should not have those characters at all
if one is occured it most probably an error
AND
u should get rid of this error itself --
not of its consequences.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SPAM]-D] How to find broken UTF-8 characters ?
2010-04-26 10:12 Re: [SPAM]-D] How to find broken UTF-8 characters ? silly sad <sad@bankir.ru>
@ 2010-04-26 12:41 ` Andreas <maps.on@gmx.net>
2010-04-29 02:34 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas @ 2010-04-26 12:41 UTC (permalink / raw)
To: silly sad <sad@bankir.ru>; +Cc: pgsql-sql
Am 26.04.2010 12:12, schrieb silly sad:
> On 04/26/10 04:12, Andreas wrote:
>
> looks like a complete offtopic
Not anymore. The bad signs are in the DB now.
I'd need some command that filters somehow for inconvertible
(Unicode-->local charset) data.
How can I find those Unicode characters that allready sneaked in?
Actually there shouldn't be anything within the tables that NEED to be
coded in Unicode.
something like
SELECT * FROM tab_1 WHERE field_x <> ConvertToLocal(field_x)
might be a good start.
>> How can I get rid of them?
> iconv -c
AFAIK iconv would translate on file system level but I would think that
messed up a allready messed up Excel workmap even further.
I'd be glad to handle csv, too.
> BUT
> u should not have those characters at all
> if one is occured it most probably an error
Sure, but those files hit me over a chain of people who consider it ok
to convert data over numerus file formats, cut, edit, save as X, send
per mail .... then hit me and I am the one to clean up.
> AND
> u should get rid of this error itself -- not of its consequences.
Like quitting the job and grow flowers instead?
I'll consider this. ;)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SPAM]-D] How to find broken UTF-8 characters ?
2010-04-26 10:12 Re: [SPAM]-D] How to find broken UTF-8 characters ? silly sad <sad@bankir.ru>
2010-04-26 12:41 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
@ 2010-04-29 02:34 ` Andreas <maps.on@gmx.net>
2010-04-29 13:05 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Justin Graf <justin@magwerks.com>
2010-04-30 10:21 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 6+ messages in thread
From: Andreas @ 2010-04-29 02:34 UTC (permalink / raw)
To: Justin Graf <justin@magwerks.com>; +Cc: pgsql-sql
Hi,
while writing the reply below I found it sounds like beeing OT but it's
actually not.
I just need a way to check if a collumn contains values that CAN NOT be
converted from Utf8 to Latin1.
I tried:
Select convert_to (my_column::text, 'LATIN1') from my_table;
It raises an error that says translated:
ERROR: character 0xe28093 in encoding »UTF8« has no equivalent in »LATIN1«
I'd like to select all those records.
When I know which record has faulty content I can correct it.
If this is really OT on the SQL list then please tell me where to ask.
Am 28.04.2010 15:18, schrieb Justin Graf:
> On 4/26/2010 8:41 AM, Andreas wrote:
>>>> How can I get rid of them?
>>> iconv -c
>> AFAIK iconv would translate on file system level but I would think
>> that messed up a allready messed up Excel workmap even further.
>> I'd be glad to handle csv, too.
>
> I would look at a macro/script to have excel dump the data out in CSV
> then move data to into Postgres
It's like this.
I load the spreadsheet into an Access-DB and let a VBA skript stuff the
data into PG via ADODB/ODBC.
Often I have to clean up more obvious things than obscure characters or
amend the info out of other sources before I can upload it to PG.
>
> Now these are not illegal UTF chars. If those values where wacky
> Postgresql would not have allowed you insert the record.
> Ô = utf code 212, Ç = utf code 199, Ä = utf code 196
Those are even in Latin1.
They were only 1 example. I suppose where I find them the 3 codes form a
multibyte code that can't be displayd or don't get displayd as a usual
letter but some symbol or asian-looking thing which definately doesn't
belong there.
I saw occasionally that such a wacky symbol replaced some other signes
that are language specific like ä, ö, ü. Then the next sign is missing
too, so something is mixing up the encoding and combines 2 chars into 1
utf8-code.
> To force a string into a specific encoding we have the Covert,
> Convert_From and Cover_to see section 9.5 in the help files
The problem is, that pgAdmin complains those signes aren't convertible
and drops the whole record out of the result of the select that I'd like
to dump into a csv.
> Select covert('MyUtf8', 'UTF8', 'LATIN')
> or
> Select covert_to('MyUtf8', 'LATIN')
I found them before but didn't understand their output.
e.g.
Select convert('1aäßx', 'utf8', 'LATIN1') ;
Result = "1a\344\337x"
so it translated ä = 344 and ß = 337. The other 3 are just as they
were before.
How can this be valid in a single byte charset like Latin1?
Especially as ä, ß are E4 and DF.
Why do they come out as escaped codes when they are in Latin1 aswell as
1, a and x?
> What ever pg client library used to move Excel data to PG my have
> incorrectly converted some of the data or moved formatting information
> into the database. I have seen Access and Excel do mightily odd
> things when connecting to DB's I don't know about current versions
> but 2000 and 2003 Excels did really stupid things when trying to write
> to DB's including MSSQL.
Cute ... we use Access 2000 and 2003 :(
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SPAM]-D] How to find broken UTF-8 characters ?
2010-04-26 10:12 Re: [SPAM]-D] How to find broken UTF-8 characters ? silly sad <sad@bankir.ru>
2010-04-26 12:41 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
2010-04-29 02:34 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
@ 2010-04-29 13:05 ` Justin Graf <justin@magwerks.com>
2010-04-30 10:34 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Jasen Betts <jasen@xnet.co.nz>
1 sibling, 1 reply; 6+ messages in thread
From: Justin Graf @ 2010-04-29 13:05 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
On 4/28/2010 10:34 PM, Andreas wrote:
> Hi,
>
> while writing the reply below I found it sounds like beeing OT but
> it's actually not.
> I just need a way to check if a collumn contains values that CAN NOT
> be converted from Utf8 to Latin1.
> I tried:
> Select convert_to (my_column::text, 'LATIN1') from my_table;
>
> It raises an error that says translated:
> ERROR: character 0xe28093 in encoding »UTF8« has no equivalent in
> »LATIN1«
>
> I'd like to select all those records.
> When I know which record has faulty content I can correct it.
>
> If this is really OT on the SQL list then please tell me where to ask.
That's easy enough you need to write an Update statement using regular
expression to replace all non legal Latin/ASCII char
http://www.postgresql.org/docs/8.4/interactive/functions-string.html
<http://www.postgresql.org/docs/8.4/interactive/functions-string.html;
the command is regexp_replace('MyBadString',
'SearchForallNoneAsccIIChars', 'ReplaceWithBlankString ')
I'm pretty sure this is the regualr expression to find all non ASCII
chars.. [^\x00-\xFF]
To test is try to Select regexp_replace( MyColumn, '[^\x00-\xFF]', ' ')
from screweduptable
If the regular expression does not work, I'm dry well, when it comes to
regular expressions. Dd i say i hate regular expression. It dam near
impossible to write.
Once you get the expression right and working
the Update is straight forward.
Update mytable set mybadcolumn = regexp_replace( mybadcolumn,
'[^\x00-\xFF]', ' ')
>> Select covert('MyUtf8', 'UTF8', 'LATIN')
>> or
>> Select covert_to('MyUtf8', 'LATIN')
>
> I found them before but didn't understand their output.
> e.g.
> Select convert('1aäßx', 'utf8', 'LATIN1') ;
> Result = "1a\344\337x"
> so it translated ä = 344 and ß = 337. The other 3 are just as they
> were before.
> How can this be valid in a single byte charset like Latin1?
> Especially as ä, ß are E4 and DF.
> Why do they come out as escaped codes when they are in Latin1 aswell
> as 1, a and x?
Someone with more knowledge how convert() works is going to have to
explain why they have been escaped. PgAdmin may have escaped them. But
those characters are valid Latin1 characters
http://en.wikipedia.org/wiki/%C3%84
http://en.wikipedia.org/wiki/%C3%9F
ß = latin Beta
It seems Access and Excel are putting in extra bits of data into the
field. In the past i had to change inserts/updates from Access so it
would send data in a specific char encoding. I had problems where
Access was using a Windows Encoding, the ODBC converted it to Latin1,
and the MsSQL Database put it in UTF8. It was no fun cleaning it up.
>
>> What ever pg client library used to move Excel data to PG my have
>> incorrectly converted some of the data or moved formatting
>> information into the database. I have seen Access and Excel do
>> mightily odd things when connecting to DB's I don't know about
>> current versions but 2000 and 2003 Excels did really stupid things
>> when trying to write to DB's including MSSQL.
>
> Cute ... we use Access 2000 and 2003 :(
Been more Screwed by Excel and Access flakiness, and Access Programmers
thinking they are DBA's.
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SPAM]-D] How to find broken UTF-8 characters ?
2010-04-26 10:12 Re: [SPAM]-D] How to find broken UTF-8 characters ? silly sad <sad@bankir.ru>
2010-04-26 12:41 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
2010-04-29 02:34 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
2010-04-29 13:05 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Justin Graf <justin@magwerks.com>
@ 2010-04-30 10:34 ` Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 6+ messages in thread
From: Jasen Betts @ 2010-04-30 10:34 UTC (permalink / raw)
To: pgsql-sql
On 2010-04-29, Justin Graf <justin@magwerks.com> wrote:
> I'm pretty sure this is the regualr expression to find all non ASCII=20
> chars.. [^\x00-\xFF]
Not in postgres.
\x00 does not work well in strings, and \xFF is invalid utf-8.
this is why I used char()
(also ASCII is undefined past at \x7F ... but the original request
was for LATIN-1 which does end at char(255))
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SPAM]-D] How to find broken UTF-8 characters ?
2010-04-26 10:12 Re: [SPAM]-D] How to find broken UTF-8 characters ? silly sad <sad@bankir.ru>
2010-04-26 12:41 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
2010-04-29 02:34 ` Re: [SPAM]-D] How to find broken UTF-8 characters ? Andreas <maps.on@gmx.net>
@ 2010-04-30 10:21 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 6+ messages in thread
From: Jasen Betts @ 2010-04-30 10:21 UTC (permalink / raw)
To: pgsql-sql
On 2010-04-29, Andreas <maps.on@gmx.net> wrote:
> Hi,
>
> while writing the reply below I found it sounds like beeing OT but it's
> actually not.
> I just need a way to check if a collumn contains values that CAN NOT be
> converted from Utf8 to Latin1.
> I tried:
> Select convert_to (my_column::text, 'LATIN1') from my_table;
>
> It raises an error that says translated:
> ERROR: character 0xe28093 in encoding »UTF8« has no equivalent in »LATIN1«
use a regular expression.
ISO8859-1 is easy, all the caracters a grouped together in unicode so
the regular expression consists of a single inverted range class
SELECT pkey FROM tabname
WHERE ( textfield || textfiled2 || textfield3 ) ~ ('[^'||chr(1)||'-'||chr(255)||']');
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-04-30 10:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-26 10:12 Re: [SPAM]-D] How to find broken UTF-8 characters ? silly sad <sad@bankir.ru>
2010-04-26 12:41 ` Andreas <maps.on@gmx.net>
2010-04-29 02:34 ` Andreas <maps.on@gmx.net>
2010-04-29 13:05 ` Justin Graf <justin@magwerks.com>
2010-04-30 10:34 ` Jasen Betts <jasen@xnet.co.nz>
2010-04-30 10:21 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox