Charset error when inserting xls data into MySql

CloverDX Platform
1

Hello,

I get a charset error when I run my graph.
I read data from excel file (file with french date) and write those data into MySql database.

I get the following error :

ERROR [MYSQL_WRITER.ProducerThread] - Data producer failed: Error while reading input record caused by: java.nio.charset.UnmappableCharacterException: Input length = 1

I don’t know if it is a matter of excel data format or if it is MySql matter.

In the database one line is inserted but not entirely.
I have the first 3 columns filled with data and the 4th one I have

(err)

in it.
the 4th column is a date column.

Do you know what am I doing wrong ?

Thanks

Regards,

Francois

I give you a part of the log :

INFO  [main] - Starting WatchDog thread ...
INFO  [WatchDog] - Thread started.
INFO  [WatchDog] - Running on 2 CPU(s) max available memory for JVM 65088 KB
INFO  [WatchDog] - [Clover] Initializing phase: 0
DEBUG [WatchDog] -  initializing edges: 
DEBUG [WatchDog] -  all edges initialized successfully... 
DEBUG [WatchDog] -  initializing nodes: 
INFO  [WatchDog] - Mapping type set to MAP_NAMES
DEBUG [WatchDog] - Opening input file FilesDBF/moulinette.xls
DEBUG [WatchDog] - Reading input file FilesDBF/moulinette.xls
INFO  [WatchDog] - Reading data from sheet 0 (Copie de moulinette synth�s 22 ).
DEBUG [WatchDog] -      XLS_READER1 ...OK
INFO  [WatchDog] - Executing command: "/home/partagereseau/Documents/papaOrdogest/CloverETL/cloverETL_2.2.1/./myload" with parameters:
1: --user=***
2: --password=***
3: --database=test
4: --hostname=localhost
5: --table=moulinette
6: --port=0

DEBUG [WatchDog] -      MYSQL_WRITER ...OK
INFO  [WatchDog] - [Clover] phase: 0 initialized successfully.
INFO  [WatchDog] - Starting up all nodes in phase [0]
DEBUG [WatchDog] - XLS_READER1 ... started
DEBUG [WatchDog] - MYSQL_WRITER ... started
INFO  [WatchDog] - Sucessfully started all nodes in phase!
ERROR [MYSQL_WRITER.ProducerThread] - Data producer failed: Error while reading input record caused by: java.nio.charset.UnmappableCharacterException: Input length = 1
2

Hello,
Clover uses ISO-8859-1 charset by default . On XlsDataWriter or on DBOutput table you can set charset directly, but MySqlDataWriter can only work with default charset. You can change it in defaultProperties file, which should be located in CLOVER_HOME/lib/lib/cloveretl.engine.jar/org/jetel/data directory.

3

I tried your changes but I am unable to build a new jar file (I did it with gnome graphical arch tool but it didn’t give me nothing good).

I think it is more a date matter.
I changed the FMT file from date to string and did the same on the MySql table.
The insert run correctly on this field but drop in error (err) on the next date of the xls file.

This test let me think that is not a charset problem on MySql but more a date format passed between clover and MySql that is not the proper date format for MySql.

Do you know how can I see the date format passed to MySql ?

Thanks

Regards

Francois

4

Hello,
Date column must be passed to MysqlDataWriter in proper date format for MySql.

You can specify date values using any of a common set of formats:
As a string in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. A “relaxed” syntax is allowed here, too. For example, ‘98-12-31’, ‘98.12.31’, ‘98/12/31’, and ‘98@12@31’ are equivalent.
As a string with no delimiters in either ‘YYYYMMDD’ or ‘YYMMDD’ format, provided that the string makes sense as a date. For example, ‘19970523’ and ‘970523’ are interpreted as ‘1997-05-23’, but ‘971332’ is illegal (it has nonsensical month and day parts) and becomes ‘0000-00-00’.
As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as ‘1983-09-05’.

More info about date format: http://www.mysql.org/doc/refman/5.1/en/datetime.html

5

Hello,
there is a part of sample graph. It could help you with your problem.

Xls file metadata. This format of date (dd-MM-yy) equals format of date in xls file (You change this format according to your format of date in xls file).

<Metadata id="Metadata0">
<Record name="fileMetadata" recordDelimiter="" recordSize="-1" type="delimited">
<Field delimiter=";" format="dd-MM-yy" name="Field2" nullable="true" shift="0" type="date"/>
</Record>
</Metadata>

MySql metadata. This format of date (yyyy-MM-dd) equals standard format of date for MySql.

<Metadata id="Metadata1">
<Record name="mysqlMetadata" recordDelimiter="" recordSize="-1" type="delimited">
<Field delimiter=";" format="yyyy-MM-dd" name="Field2" nullable="true" shift="0" type="date"/>
</Record>
</Metadata>

In addition to XLS Data Reader and MySql Writer you use Simple Copy component. It is used only for converting input format of date (xls file) to output format of date (MySql).

<Edge fromNode="XLS_READER0:0" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="SIMPLE_COPY0:0"/>
<Edge fromNode="SIMPLE_COPY0:0" id="Edge1" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="MYSQL_DATA_WRITER0:0"/>

This graph didn’t tested. But I hope that it’s ok.

6

Hello,

Thanks a lot for your help. I used the solution proposed by mhaupt (thanks again) and now it is working fine.

If some one has the same problem here is an example of the graph that worked for me :

In this solution I added a node id=“BROADCAST” witch serves to convert the date format from dd/MM/yyyy to Mysql date format yyyy-MM-dd.

<?xml version="1.0" encoding="UTF-8"?>
<Graph created="Tue Oct 24 10:36:22 CEST 2006" description="In this example data are read from ORDERS.xls file, partitioned to 3 parts and each of thees parts are saved in orders.partitioned.xls file in different sheets. " guiVersion="1.7" licenseType="Unlicensed clover.GUI" modified="Mon Jun 25 12:09:41 CEST 2007" name="loadXlsMoulinetteTrs" revision="1.21">
<Global>
<Metadata fileURL="FilesFMT/moulinette.fmt" id="Metadata0"/>
<Metadata fileURL="FilesFMT/moulinetteTrs.fmt" id="Metadata1"/>

<Property fileURL="workspace.prm" id="GraphParameter0"/>
</Global>
<Phase number="0">

<Node enabled="enabled" id="BROADCAST" type="SIMPLE_COPY"/>

  <Node	enabled="enabled" 
	fileURL="FilesDBF/moulinette.xls" 
	id="XLS_READER1"
	dataPolicy="Lenient"
	metadataRow="1"
	startRow="2"
	sheetNumber="0" 
	type="XLS_READER"/>

  <Node id="MYSQL_WRITER"
        type="MYSQL_DATA_WRITER"
        myload="myload"
        hostname="localhost"
        database="test"
        dbTable="table_name"
        username="USER"
	password="XXXXXXX"/>

<Edge fromNode="XLS_READER1:0" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="BROADCAST:0"/>
<Edge fromNode="BROADCAST:0" id="Edge1" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="MYSQL_WRITER:0"/> 

</Phase>
</Graph>

and here are the metadata file :

the moulinette.fmt file (this file reads the XLS file) :

<?xml version="1.0" encoding="UTF-8"?>
<Record name="moulinette"  type="delimited" charset="UTF-8">
<Field delimiter="|" name="NAME" nullable="true" type="string"/>
<Field delimiter="|" name="BIRTH_DATE" nullable="true" type="date" format="dd/MM/yyyy"/>
</Record>

the moulinetteTrs.fmt file (this file is used to convert date into MySql date format) :

<?xml version="1.0" encoding="UTF-8"?>
<Record name="moulinetteTrs"  type="delimited" charset="UTF-8">
<Field delimiter=";" name="NAME" nullable="true" type="string"/>
<Field delimiter=";" name="BIRTH_DATE" nullable="true" type="date" format="yyyy-MM-dd"/>

Hope it will be useful to somebody.

Regards,

Francois