View Full Version : removing ".0"

04-02-2006, 02:21 AM

I got column in a excel file called itemcode. sample values are as follows 123-456, A12.023, 123456.0 [img]/wiki/images/newpage.gif ("] 123-456, A12.023, 123456 [img]/wiki/images/newpage.gif[/url][/url]

The data type is "String" but when i try to load into database the values as follows [url=")[/url]

Why is the process putting a ".0" even though I specified that it is a string datatype? How do i get rid of the ".0" from the third value?


04-02-2006, 02:41 AM

Because the data type in Excel is strongly typed, we read a Double precision floating point value.
So one option is to leave it as Number in the Excel Input value and change the data type to Integer.
You can change the data type to Integer using the Select Values step, use the meta-data tab.
The table output will then convert to String and the .0 will no longer be there in that case.

OR change the value in Excel to '123456 making it a String data type.

BTW, in the coming weeks we'll add conversion logic to the Excel Input in development as well.
For the people browsing the archive: that means that this work-around is only valid for versions <= 2.2.2

All the best,


04-02-2006, 03:21 AM

In the case i mentioned the data type is string because the itemcode can have alpha characters so i need to use javascript to eleminate ".0" !

As you mention that you are working on the excel step. Can I suggest that you have an option to "Read All sheets" and another option to say "Only Sheet Number x" instead of having to select the sheet name always. This will benifit many of us having to open the excel file and rename the sheets manually so that the job will work.

Can i know some indicative date by when you will rollout the version 2.3.


04-02-2006, 06:18 AM
The .0 at the back comes from the fact that Kettle converts the double precision Number value in Excel to a String because you ask for a String.

What can I say, if you key in the "string" 123456 in excel, then Excel takes that automagically as a number.

See also: http://www.javaforge.com/proj/tracker/itemDetails.do?task_id=1590&navigation=true

We will add the options you suggested. Version 2.3.0 is released when it&#39;s ready, which is probably in a month or so.

Take care,