Olivier Bertrand added a comment - 2015-01-16 18:16 - edited Indeed, using UltraEdit it was correctly decoded and I could verify if is in UTF-8 (coded in three bytes). I did not check yet what happen with column names. They are retrieved by CONNECT from the file header during the discovery process and perhaps not decoded properly. This can be temporarily avoided by manually defining the columns in the create table statement. However, I traced what happen with the data part. Because it is in UTF-8 (even the ASCII columns can be regarded as UTF-8) I created the table as: CREATE TABLE table_utf8 engine=CONNECT table_type=CSV default charset=UTF8 file_name='E:/Data/table_utf8.csv' header=1 sep_char=',' quoted=1 data_charset=utf8; The data_charset option indicates the coding of the table data. What CONNECT does when reading the table is retrieving the column data and I could verify that it get it correctly and call the matching field store function with the charset parameter set by the data_charset option as UTF-8: rc= fp->store(p, strlen(p), charset, CHECK_FIELD_WARN); From there, all is done by MariaDB. If the default charset of the table is Latin1, MariaDB tries to convert the three bytes characters to Latin1 and fails, then they are displayed as '?' question marks. If the table default charset is UTF-8, the data is not converted (as the from charset is equal to the to charset) The way it is displayed depends on the client program and settings. Therefore this seems to be a MariaDB issue, at least for the data part of the table.

Olivier Bertrand added a comment - 2015-01-16 19:30 Redefining the table as: CREATE TABLE `table_utf8` ( `COUPON_NUM` char(5) NOT NULL, `COUPON_NAME` char(33) NOT NULL, `START_DATE` char(10) NOT NULL, `END_DATE` char(10) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=UTF8 `TABLE_TYPE`='CSV' `FILE_NAME`='E:/Data/table_utf8.csv' `SEP_CHAR`=',' `DATA_CHARSET`='utf8' `HEADER`=1 `QUOTED`=1; I get the following result when character_set_results='latin1': +------------+-------------+------------+------------+ | COUPON_NUM | COUPON_NAME | START_DATE | END_DATE | +------------+-------------+------------+------------+ | A8772 | ????????? | 2005-03-11 | 2005-04-01 | | A6703 | ??????????? | 2005-08-15 | 2005-09-05 | | A4313 | ???????? | 2005-09-13 | 2005-10-04 | +------------+-------------+------------+------------+ This seems normal, those three bytes characters cannot be translated to latin1. Now if character_set_results='utf8' the display is: +------------+-----------------------------------+------------+------------+ | COUPON_NUM | COUPON_NAME | START_DATE | END_DATE | +------------+-----------------------------------+------------+------------+ | A8772 | ´╝ú´╝í´╝¡´╝░´╝í´╝®´╝º´╝«´╝æ | 2005-03-11 | 2005-04-01 | | A6703 | ´╝í´╝«´╝«´╝®´╝´╝Ñ´╝▓´╝│´╝í´╝▓´╝╣ | 2005-08-15 | 2005-09-05 | | A4313 | ´╝«´╝Ñ´╝À´╝┐´╝®´╝┤´╝Ñ´╝¡ | 2005-09-13 | 2005-10-04 | +------------+-----------------------------------+------------+------------+ To me it looks as if the client program were not decoding the result even the character_set_client is set to UTF-8. So it could also be a problem coming from the client program.

Olivier Bertrand added a comment - 2015-01-17 01:58 Concerning the column names, there was a problem when specified in UTF8 because, MariaDB internally handling column names in UTF8, CONNECT was translating them before creating the table. Now for CSV tables specifying DATA_CHARSET=UTF8 this translation is no more done, considering they already are coded in UTF8. For instance, starting from the file: Opération,Monnaie,Débit,Crédit Chèque,£,100,0 Virement,€,0,5600 I can create the table: create table tab_csv8 engine=connect table_type=csv file_name='E:/Data/csv8.csv' header=1 lrecl=64 data_charset=utf8; Describe tab_csv8 replies: Field Type Null Key Default Extra Opération char(8) NO <null> <null> <null> Monnaie char(3) NO <null> <null> <null> Débit int(3) NO <null> <null> <null> Crédit int(4) NO <null> <null> <null> and select * from tab_csv8; Returns: Opération Monnaie Débit Crédit Chèque £ 100 0 Virement € 0 5600 However, if instead of using my graphic client program I use the mysql client, the table is displayed as: MariaDB [test]> select * from tab_csv8; +-----------+---------+-------+--------+ | Opération | Monnaie | Débit | Crédit | +-----------+---------+-------+--------+ | Chèque | £ | 100 | 0 | | Virement | ? | 0 | 5600 | +-----------+---------+-------+--------+ See; the Euro character is not recognized and printed as a question mark. This shows that the problem is a problem of character recognition by the mysql client program. Note that this is done with: MariaDB [test]> show variables like '%char%'; +--------------------------+-----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------------------------------+ | character_set_client | cp850 | | character_set_connection | cp850 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | cp850 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | d:\CommonSource\mariadb-10.0.5\10.0-connect\sql\share\charsets\ | +--------------------------+-----------------------------------------------------------------+ If those character sets are set to UTF8, things are even worst and the table is displayed as: MariaDB [test]> select * from tab_csv8; +------------+---------+--------+---------+ | Op├®ration | Monnaie | D├®bit | Cr├®dit | +------------+---------+--------+---------+ | Ch├¿que | ┬ú | 100 | 0 | | Virement | Ôé¼ | 0 | 5600 | +------------+---------+--------+---------+ Therefore, I think this issue is fixed for CONNECT (column names are now retrieved correctly) but of course there are pending problem for displaying some characters that are not CONNECT wise.

Takuya Aoki (Inactive) added a comment - 2015-01-19 05:52 Indeed, setting default charset=UTF8 and configuring the client solves the problem for the data part. This is the same in my test I submitted in 2015-01-14. But for the header part, I don't understand what is fixed at the moment. Do you mean data is handled correctly inside, but displaying it errors? Is it possible to fix the client so that all characters can be shown properly?

Olivier Bertrand added a comment - 2015-01-19 12:22 As I said, the header part was a bug that will be fixed in next version.