Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    5

    Unanswered: Adding Leading Zeroes to a Flat File

    Hi,

    I'm using Pervasive to convert an Excel spreadsheet into a flat file. And, I'm having trouble adding leading zeroes to a field that has 6 digits.

    Eg.

    50 -> 000050
    500 -> 000500
    5000 -> 005000
    50000 -> 050000

    Any help is appreciated. Thanks!

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Which tool exactly are you using?
    You'll probably want to convert the value into a string and then pad with zeros.
    How you do that depends on the tool and interface.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Feb 2008
    Posts
    5
    Quote Originally Posted by mirtheil
    Which tool exactly are you using?
    You'll probably want to convert the value into a string and then pad with zeros.
    How you do that depends on the tool and interface.
    I'm using Map Designer... Would it matter if I have defined the particular field as a text data type? I'm a little new to this but what do you mean by converting the value into a string and pad with zeroes

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I'm more familiar with the PSQL database but in Map Designer you can use RIFL script (VB Script like scription) to manipulate the field when it comes from the source and before it is written to the target.
    You'll need to do something like the following (pseudo-code):
    1. Convert the value to string.
    2. Determine length of the value (50 is 2, 500 is 3, 5000 is 4, etc).
    3. Concatenate a string of zeros appropriate for the length. ( "0000" + "50", "000" + "500", etc).
    4. Write the value to the target as a string.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Feb 2008
    Posts
    5
    Quote Originally Posted by mirtheil
    I'm more familiar with the PSQL database but in Map Designer you can use RIFL script (VB Script like scription) to manipulate the field when it comes from the source and before it is written to the target.
    You'll need to do something like the following (pseudo-code):
    1. Convert the value to string.
    2. Determine length of the value (50 is 2, 500 is 3, 5000 is 4, etc).
    3. Concatenate a string of zeros appropriate for the length. ( "0000" + "50", "000" + "500", etc).
    4. Write the value to the target as a string.
    Yes, I'm using the RIFL script to manipulate the field. However, I've hit a roadblock with it. I've tried using the FORMAT query to do what I'd like to achieve but it has not worked so far.

    Thanks for all the information.

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I'm not real familiar with RIFL script but what exactly have you tried and how did it fail?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Feb 2008
    Posts
    5
    Quote Originally Posted by mirtheil
    I'm not real familiar with RIFL script but what exactly have you tried and how did it fail?
    I've tried using this syntax - format(fieldname,"000000") but it doesn't add the leading zeroes. I also tried to convert from numeric to a string value and then add the leading zeroes - that didn't work too.

    But then, I changed the data type to text - and it WORKED!

Posting Permissions

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