topic Re: Separator for Excel Files in Talend Studio

Separator for Excel Files

patricesoulet — Sat, 16 Nov 2024 14:11:22 GMT

Hello,
I use an Excel file with number formatted like this : n nnn,nn
For reading this number, i've define ' ' for thousands separator and ',' for decimal separator.
I make a first test with 'String' for type. It's ok, and result are : ... |2 987,49| ...
For the second test, i use 'Float' for type, and :
- row with number greater then 999,99 as rejected with this message : For input string: "1 638.32"
- other row are ok ...
For the third test, i change decimal separator for '.', and all rows are rejected with the message : "For input string: "430,41"
I don't know what i need use for thousands separator, and if it's a bug ...
I've read on forum to use strings and convert after on float, but in this case :
- pity to must use a second component for transform strings to number
- why there are the Thousands operator's option if this option doesn't work ... ?

Re: Separator for Excel Files

Anonymous — Wed, 22 Oct 2008 14:08:38 GMT

This is not related to the thousands separator but to the decimal separator.
Take a look at your datas, you have 2 different decimal separator in your file : , and .
This is probably in 2 different columns.
Am I right ?

Re: Separator for Excel Files

patricesoulet — Wed, 22 Oct 2008 14:27:59 GMT

To be sure, i've create an another Excel file with two columns:
Test deuxieme
1 111,92 a
230,84 c
The forst row are the header of columns.
I've create a job with tFileInputExcel and tLogRow.
For Excel, he thousand separator define in metadata is ' ', and decimal is ','.
The result of job for the main row :
Starting job t5 at 15:18 22/10/2008.
For input string: "1 111.92"
230.84|c
Job t5 ended at 15:18 22/10/2008.
The result job for reject :
Starting job t5 at 15:25 22/10/2008.
|||For input string: "1 111.92"
Job t5 ended at 15:25 22/10/2008.

I use a Excel file from Microsoft Excel 2000, on windows XP, with regional settings in french, and the number are formatted with 2 decimal and using decimal separator.
The encoding type on TOS is windows-1252.
I use TOS 3.0.0.

P.S. : i've find 3372 related to thousands seprator ...

Re: Separator for Excel Files

patricesoulet — Thu, 23 Oct 2008 11:07:11 GMT

Well, i've find the problem ... the thousands separator is not the space but the caracter 160.
When i use (char)160 for thousands separator in metadata, there are no reject.