Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Padding and Number and String treatment

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default Padding and Number and String treatment

    Hello Everyone,

    Here is my situation.

    I have number "12345678" in Excel cell, I ask PDI to read it as string. PDI reads as "12345678.0". So there are two extra symbols. How can I make PDI read it as "12345678"?

    I do not want to cut two last symbols simply because my column contains alpha-numeric data and I am afraid to cut something meaningful.

    I also tried padding and could not get the correct result. I attached an example of everything above.

    Regards,
    Alex
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you expect numerical data in a column, always set your format.

    In this case, since you are getting both text and data, set your format to # and it will format the numbers the way you want.
    # - optional digit
    0 - required digit

  3. #3
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you alot, gutlez!

    Do you have any guess why padding does not pad?

    Regards,
    Alex

  4. #4
    Join Date
    Sep 2011
    Posts
    171

    Default

    One more thing!

    What if I have both whole integer in cell like "1234" and also decimal in another cell "123.213". If I choose "#" in format option then I will lose decimal digits. Is there way around? Or am I asking too much?

    Regards,
    Alex

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Your format applies to the column, so you should really only be getting one type of data per column.
    I'm not sure if you can format different rows in the same column differently - I really don't see how.

    If you are getting rows:
    ABCD
    1234
    EFGH
    123.213

    you might just be out of luck - but there are smarter minds on the board than me...

  6. #6
    Join Date
    Sep 2011
    Posts
    171

    Default

    I also noticed that nothing similar happens with .csv files.

    Any idea on padding?

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by august_month View Post
    I also noticed that nothing similar happens with .csv files.
    Likely has to do with how Excel stores the data. Since CSV stores it all as text, it can import the data as text and not worry about format strings. This will solve your other problem too (1234, ABCD, 123.212)

    Quote Originally Posted by august_month View Post
    Any idea on padding?
    Nope, can't tell you why it doesn't seem to work... But I don't use it regularly, so I can't tell you how it's supposed to work

  8. #8
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you again, gutlez!

    I will isolate padding case and if nobody replies I will file JIRA.

    Regards,
    Alex

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There already is a JIRA case for these situations: http://jira.pentaho.com/browse/PDI-1028

  10. #10
    Join Date
    Sep 2011
    Posts
    171

    Default

    Matt,

    The file that I am having issue with is attached at the very beginning of my initial post (everything is in .rar archive). My initial post relates to two different issues. I know it is not a good idea to ask two separate questions in one post, therefore I wrote that I will isolate my question about padding in separate post. I need time to create proper example, that is why I did not post it immediately.

    Regarding your JIRA, it seem like different JIRA issue, because I working on the case where PDI needs to know what my boss wants.

    Regards,
    Alex

  11. #11
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Alex,

    I changed your pad length from 5 to 9 on description, and it changed your value werrewr to werrewrtt
    Are you sure it's not working, or is it not configured correctly?

  12. #12
    Join Date
    Sep 2011
    Posts
    171

    Default

    Okay, I see. Padding length is not number of symbols to add, but up to what length to pad. My fault!

    Thank you, gutlez! No need to file JIRA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.