Re: Bulk loading file with non-UTF8 characters

6 Dec 2013

      On 2013-12-06 20:01, Lee Hachadoorian wrote:
...
On Fri, Dec 6, 2013 at 5:50 AM, Sjoerd Mullender <sjoerd@acm.org
mailto:sjoerd@acm.org> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2013-12-06 10:38, Lee Hachadoorian wrote:
> I need to bulk load some files with characters like ñ and é. I
> would do this using the LATIN1 encoding. I read that bulk loading
> from file assumes UTF8, but bulk loading from STDIN will use
> encoding of mclient. I would like to know:
>
> (a) How do I determine and change the client encoding? (b) What is
> an example for piping STDIN to mclient?
>
> Regarding the latter, I tried:
>
> cat /my/file.csv | mclient -d mydb -u monetdb -s "COPY INTO mytable
>  FROM (STDIN) USING DELIMITERS ',', '\n' , '"' NULL AS '';"
>
> I wasn't sure how the embedded " would be handled, but the
> terminal basically became unresponsive until I ctl-C'ed out of it.
> Note the the file was similar in size to several others (without
> non-UTF8 characters) that loaded in seconds within mclient.
>
> Best, --Lee
>

See [1] for recipes for bulk loading, including the relevant
invocation of mclient.
The default character set that mclient uses depends on the
environment.  You can specify a specific character set using the -E
option.  See the manual at [2].

Quoting commands is an art.  Look at the command in [1] how you can do
it correctly.  Hint: when quoting using double quotes ("), you need to
use a backslash (\) to quote embedded backslashes and double quotes.

[1]
http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData
[2] http://www.monetdb.org/Documentation/mclient-man-page

- --
Sjoerd Mullender

Thanks Sjoerd. OK, now the file is getting to mclient, and I am at least
at the point where I am getting the same error message as when I COPY
... FROM 'file' within mclient.
$ mclient -E LATIN9 -d universe -u monetdb -s "COPY INTO
acs2010_5yr.geoheader FROM STDIN USING DELIMITERS ',', '\n', '"'" - <
/data/lee/postgres_upload/acs2010_5yr/All_Geographies_Not_Tracts_Block_Groups/g20105az.csv
password:
value from line 10560 field 50 not inserted, expecting type varchar(200)
failed to import table
current transaction is aborted (please ROLLBACK)
syntax error, unexpected sqlINT in: "04"
I tried it both with the terminal's character encoding set to UTF8 and
set to LATIN9. Does that make a difference? When I sed the problematic
line with the terminal in LATIN9, it is human-readable, while in UTF8 it
is not.
The -E option overrides the setting mclient takes from the environment
(i.e. the terminal setting).
The first message is important (value from line 10560 field 50 not
inserted, expecting type varchar(200)).  The second message about the
int "04" is just because the server reads the next line that happens to
start with 04 after it encountered the error and decides that that is
not valid SQL.
I turns out you also need to specify the -i option to mclient for it to
translate the input using the encoding.  Without -i, mclient takes a
more efficient path, but that path doesn't involve translating the
character encoding.  It can be argued that's a bug.
...
At this point I could just use iconv on the file, but I'm would like to
know how to get this working.
Best,
--Lee

users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
-- 
Sjoerd Mullender