1. Junior Member
Join Date
Nov 2013
Posts
16

Hi Everyone,

Sorry for asking another fairly simple question, I've searched everywhere and still haven't found a solution. Here's what I am trying to accomplish in Kettle 4.4 CE (I'm going from DATABASE --> TEXT FILE), hopefully someone can help:

This is the pipe-delimited text file output I have right now:

This is the text file output I want:

Which Transformation Step (or maybe other steps) do I use to Add or Remove Leading/Trailing Zero's? I just find it difficult to work with numeric values, strings are much easier. That said, hopefully I don't have to convert them string. Any help would be much appreciated!

David.

2. Senior Member
Join Date
Apr 2008
Posts
4,696
By definition, a whole number doesn't have leading 0s.
By definition, a trailing 0 on a whole number is important (1 is not the same as 10 when dealing with numbers!)

Your "Number" is actually a string with only numeric characters.

So convert it to a string, and deal with it accordingly.

HOWEVER, how many 0s on the tail end can validly be removed?
Is 80912345 = 809123450 = 8091234500 = 80912345000 ?

You seem to be trying to replicate either a report or a Fixed-Width File for some unspecified reason - These might be easier to accomplish with Reporting step

3. Junior Member
Join Date
Nov 2013
Posts
16

From my source databases, they are usually 8 (with no leading zero) or 10 (with extra trailing zero) digits that represents the financial institution number. However, I need them to be exactly 9 digits so I can do a Database Lookup to another table and map them.

I guess one option is to use the 'Select values' transformation step and change the meta data to String. Then do use the 'String operations' step and go from there? To answer your question, yes 80912345 = 0809123450 = 080912345 (those are the only 3 possible formats). Anyways, thanks for your help.

David
Last edited by zzhang33; 04-08-2014 at 08:09 PM.

4. Senior Member
Join Date
Apr 2008
Posts
4,696
Originally Posted by zzhang33
I guess one option is to use the 'Select values' transformation step and change the meta data to String. Then do use the 'String operations' step and go from there?
Yup, that's pretty much what you have to do... Your "Number" is not actually a number (even it is being stored in the DB as a number) but rather it is a string - Financial Institution Identifier.

You could go through a bunch of convoluted processing to determine if the last digit is a 0, and if so divide by 10, but that doesn't help with your leading 0 issue. A leading 0 can only exist in a string.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•