Formatting a file

[Nicole]

I need to format a file - the input looks like:

2008-12-31 223.00PL0921870976
2008-12-313432.00PL0922928729

I need it to look like:
COL1 COL2 COL3
2008-12-200000000223.00PL1234567
2008-12-200000003432.00PL3456788

The zeroes above would actually be spaces, but they weren't appearing for some reason.

So I need to padd the amount field with spaces and make sure the field after the amount is lined up. For some reason, i'm having trouble with this today - its probably an easy one. Any advice would be appreciated.

Thanks,

Nicole

[chulett]

Don't see any padding in your example, without 'code' tags all excess whitespace is removed by the forum software. What have you tried? And you need a space between Col1/Col2 but not between Col2/Col3? That would be... odd. Assuming no.

What you seem to have is a fixed-width file with no spaces between the fields. Define your file as such with char output fields and you're almost there, you would just need to manually pad the amount to the right size.

[Nicole]

I edited my post and added '0''s where the spaces would be in the amount field - for some reason, spaces weren't displaying.

Yes, it would be a fixed width file - but the input -the amount fields are different depending on the dollar amount if it is in the thousands or hundreds, for instance. In the correctly formatted one, the spaces would make it so that they would all be the same length. It is just giving me trouble because the amount field is now variable length -and I don't know how to define it in the input. Am I making sense. Not sure why this one is difficult for me to grasp today, but it is.

Don't see any padding in your example, without 'code' tags all excess whitespace is removed by the forum software. What have you tried? And you need a space between Col1/Col2 but not between Col2/Col3? That would be... odd. Assuming no.

What you seem to have is a fixed-width file with no spaces between the fields. Define your file as such with char output fields and you're almost there, you would just need to manually pad the amount to the right size.

[chulett]

Like I said, the forum software removes 'extra' spaces from what it thinks is normal text, so you need to wrap your example in Code tags to preserve the whitespace. Do the same for any actual code you need to post to preserve any formatting / indenting as well.

You need to know what the total size of the output field is and then pad your source data to 'space pad and right align' the data in the field, since it will want to left-align if it had its way. Probably one of the simplest ways is to use the Right() function after concatenting on more spaces than you need. Looks like your output should be 13 total, so something like:

Code: Select all

Right("0000000000000":Trim(YourField),13)

Play with that and see if it helps.

[Nicole]

Thank you - I was able to get it formatted correctly - thanks for the help.