Does anyone know how to get the load data infile command to load utf8 data?

I have setup a database as utf8 with a collation of utf8_general_ci, the
mysqld server is started with --character-set-server=utf8. Server variables
say character_set_database = utf8. I use the sql below

LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

Yet when i try to load a file with an accented value in it e.g. "Agustín"
the value gets truncated to "Agust"

anyone got any solutions to this?

Regards

Cal

Caleb Racey wrote:
> Does anyone know how to get the load data infile command to load utf8 data?
>
> I have setup a database as utf8 with a collation of utf8_general_ci, the
> mysqld server is started with --character-set-server=utf8. Server variables
> say character_set_database = utf8. I use the sql below
>
> LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8
> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
>
> Yet when i try to load a file with an accented value in it e.g. "Agustín"
> the value gets truncated to "Agust"

If you haven't already, issue this first:

SET CHARACTER SET utf8;
SET NAMES utf8;
LOAD DATA INFILE ...

b

Caleb Racey wrote:
> Does anyone know how to get the load data infile command to load utf8 data?
>
> I have setup a database as utf8 with a collation of utf8_general_ci, the
> mysqld server is started with --character-set-server=utf8. Server variables
> say character_set_database = utf8. I use the sql below
>
> LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8
> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
>
> Yet when i try to load a file with an accented value in it e.g. "Agustín"
> the value gets truncated to "Agust"
>
> anyone got any solutions to this?

It is indeed buggy and badly documented. It depends on the current
database's character set instead. Try this:

SET NAMES utf8;
SET character_set_database=utf8;
LOAD DATA INFILE...

Baron

Caleb Racey wrote:
> On 10/26/07, Baron Schwartz <baron@xaprb.com> wrote:
>> Caleb Racey wrote:
>>>> It is indeed buggy and badly documented. It depends on the current
>>>> database's character set instead. Try this:
>>>>
>>>> SET NAMES utf8;
>>>> SET character_set_database=utf8;
>>>> LOAD DATA INFILE...
>>>>
>>>> Baron
>>>>
>>> Thanks for the suggestion
>>>
>>> I'm afraid i get the same behaviour when i try this approach accented
>> values
>>> still truncate at the accent when i use load data infile.
>>>
>> OK, the next suggestion is to use the 'binary' character set. By the
>> way, I accidentally omitted quotes above; I should have typed
>>
>> SET character_set_database='utf8';
>>
>> You should verify your connection's character sets with
>>
>> SHOW VARIABLES LIKE '%character%';
>>
>> Try it with 'binary' and see if that works.
>>
>> Baron
>
> thanks again
> I'm afraid 'binary' achieved the same result as did using utf8 with
> quotes.

I'm out of ideas, then. I wish I could more.

Baron

Are you sure your file is coded in utf8? Character set of your file must
be same as charset of your database.

Dusan

Caleb Racey napsal(a):
> Does anyone know how to get the load data infile command to load utf8 data?
>
> I have setup a database as utf8 with a collation of utf8_general_ci, the
> mysqld server is started with --character-set-server=utf8. Server variables
> say character_set_database = utf8. I use the sql below
>
> LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8
> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
>
> Yet when i try to load a file with an accented value in it e.g. "Agustín"
> the value gets truncated to "Agust"
>
> anyone got any solutions to this?
>
> Regards
>
> Cal
>
>

Hi,
Try this.

set session collation_database=latin1_swedish_ci;
set session character_set_database=latin1;

regards
anandkl


On 10/29/07, Dušan Pavlica <pavlica@unidataz.cz> wrote:
>
> Are you sure your file is coded in utf8? Character set of your file must
> be same as charset of your database.
>
> Dusan
>
> Caleb Racey napsal(a):
> > Does anyone know how to get the load data infile command to load utf8
> data?
> >
> > I have setup a database as utf8 with a collation of utf8_general_ci,
> the
> > mysqld server is started with --character-set-server=utf8. Server
> variables
> > say character_set_database = utf8. I use the sql below
> >
> > LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET
> utf8
> > FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
> >
> > Yet when i try to load a file with an accented value in it e.g.
> "Agustín"
> > the value gets truncated to "Agust"
> >
> > anyone got any solutions to this?
> >
> > Regards
> >
> > Cal
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

Ananda Kumar wrote:
> Hi,
> Try this.
>
> set session collation_database=latin1_swedish_ci;
> set session character_set_database=latin1;
>

Rather:

set session collation_database=utf8_general_ci;
set session character_set_database=utf8;

Also, make sure you have these in my.cnf:

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8
init-connect='SET NAMES utf8'

And you might want to add these also:

collation_server=utf8_unicode_ci
character_set_server=utf8