[spurs72]

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!

[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.

[spurs72]

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

[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.

[spurs72]

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.

[mirtheil]

I'm not real familiar with RIFL script but what exactly have you tried and how did it fail?

[spurs72]

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!