DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type
	:content-transfer-encoding:content-disposition;
	b=wbQFC1fLuENEHYJShzuZq9Rqy0PYvS9AYOvTBX4I4HKCDP2lMcZTu4Xl7fHMcF0DSu
	K4wtNNizJoTG3MYnNuVPuUBIT0kj05YlMnzEEXZR31fCohtp7fQqobjQ9sothGsfGCb8
	hE87fLpcON7IpaJKpRHY5+Lf5xVMgf8Dp0Z4U=
Message-ID: <57a815bf0807101902o193644ear50e211e67663d874@mail.gmail.com>
Date: Fri, 11 Jul 2008 12:02:01 +1000
From: "Mikel Lindsaar" <raasdnil@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Importing data - possible UTF8 import bug?
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi all,

I am importing a bunch of data exported from an Oracle database.

This came out as a flat file, tab delimited.

I have parsed this through a ruby script that does the following:

For each line
  split it at the tab
  for each column
    parse it through Iconv and convert from UTF8 to UTF8
      on failure dump the line into a log for inspection and continue
    check to see if the column value ends in a single \
      if so, add another \ to escape it and not the tab that will follow
    check to see if the column value is blank
      if so, replace with \N
  join the array back together with tabs
  write it out to the import file.


This works quite well.  The ruby script handles about 10,000 lines
every 15 seconds or so.  Theoretically no invalid utf-8 sequences
could end up in the output file as everything is passed through Iconv
and if it raises an except it dumps the offending line into a separate
file for manual handling.

But I am hitting a problem.  When I try to import the result with COPY
I am getting:

ERROR: invalid byte sequence for encoding "UTF8": 0xa2

And gives me a line reference.

I then grabbed that line out of the file, unpacked each byte into a
decimal representation and 162 does not appear anywhere.

Additionally I did:

Iconv.iconv('UTF-8', 'UTF-8', line).join == line

Which takes the line and converts it from UTF8 to UTF8 using the Iconv
library (--f UTF-8 -t UTF-8) and this returns true.

Interestingly, the following also returns true:

Iconv.iconv('ASCII', 'UTF-8', line).join == line #=> true

So there can't be any non ASCII characters in there.

Unpacking the line and uniq'ing it and sorting it gives the following:

@result.unpack('U'*@result.length).uniq.sort
=> [9, 10, 32, 45, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 65, 66,
67, 68, 69, 70, 72, 73, 74, 76, 77, 78, 79, 80, 82, 83, 84, 85, 87,
91, 92, 93, 97, 98, 99, 101, 109, 110, 111, 114, 116, 117, 121]

Anyone got any ideas how I can hunt down what is in this row, visually
looking at it hasn't helped?

Mikel


-- 
http://lindsaar.net/
Rails, RSpec and Life blog....