Fixed Width File Format from TM1

When outputting data from TM1 usually we can get away with comma separated values format. Which is standard functionality from a Turbo Integrator process [See ASCIIOutput, TextOutput functions].

Every now and then we need to interface with a ERP system which requires Fixes Width Fields. This presents a few problems, not insurmountable but still not expressly easy either. The tips below will make things easier for you.

ASCII Quote Character Options

To start your extract process set the ASCII Quote character to an empty string (nothing). This will prevent the output from the ASCIIOutput function from having a set of double quotes around it.

For example:

ASCIIOUTPUT('Sample Journal.txt','0000211Journal Name829199+000000000213');

Will write a line to a file like:

"0000211Journal Name829199+000000000213"

But after configuring the Quote Charicter:

DatasourceASCIIQuoteCharacter = '';
ASCIIOUTPUT('Sample Journal.txt','0000211Journal Name829199+000000000213');

Will write a line to a file like:

0000211Journal Name829199+000000000213

(Notice this doesn’t have double quotes surrounding the string)

Note: DatasourceASCIIQuoteCharacter only needs to be called once at the beginning of the prolog.

Formatting Names and Codes for Fixed Width

Account Codes, Transaction Descriptions and other “Tagging” Fields all need to be formatted to given widths. Typically these fields require a space filler as opposed to zeros (often used for numeric fields in this format).

I’ll post some samples of how to achieve given formats below:

Format: Cost Centre – 10 Charicter, Zero Filled.
Before: 60900
After: 0000060900

From a String Variable:

sResult = SUBST('0000000000' | TRIM(sCC), LONG('0000000000' | TRIM(sCC)) - 9,10);

From a Numeric Variable:

sResult = SUBST('0000000000' | NUMBERTOSTRING(nCC), LONG('0000000000' | NUMBERTOSTRING(nCC)) - 9,10);

Format: Journal Description – 55 Charicter, Space Filled.

Before: ‘Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Nam cursus.’
After: ‘Lorem ipsum dolor sit amet, consectetuer adipiscing eli’

Before: ‘Hello World’
After: ‘                                            Hello World’

From a String Variable:

sResult = FILL(' ', 55 - LONG(TRIM(SUBST(sText,1,55))) ) | TRIM(SUBST(sText,1,55));

Formatting Figures for Fixed Width

The figures in this format often are the harder fields to convert when it comes to decimal formats.

Format: Sign Indicator (1 Character) followed by 26 numeric Characters. 3 Decimal Places are implied.

Before: $150.00
After: +00000000000000000000150000

Before: $-2500.15
After: -00000000000000000002500150

From a Numeric Variable:

sResult = IF(value>=0,'+','-') |  FILL('0',26-LONG(  Numbertostring( INT(value * 1000)))) | Numbertostring( INT(value * 1000))

Note: Number formats which require a decimal place indicator at a particular position can be done using the above code and then after generating the field string inserting the dot ‘.’ character at the correct position – this method often yields better results (tidier code, less calculations) then using rounding on the initial numeric value.

Outputting the Resulting Fields

Take the results of the above formatting functions and concatenate using the standard concatenation operator in TM1 (which is the Pipe character “|” ).

sLine = sCCResult | sAccountResult | sValueResult | sMonthResult | sDescResult;
DatasourceASCIIQuoteCharacter = '';
ASCIIOUTPUT(sFileName,sLine);

This should give you the result you are after.

000006090000006601010-0000000000000000000250015001012013                                            Hello World

Utter Gibberish…