AsciiOutput to Excel


We often export data from TM1 to a text file using functions like AsciiOutput and TextOutput. Debugging Turbo Integrator processes is only 1 such example.

It would be nice to export your output to Excel because a lot of TM1 users spend a lot of their time in Excel. Exporting to Excel is not possible though, or at least not without heavy programming. Exporting to a relational database is possible but again that is much more cumbersome than exporting to a simple flat file. Usually we export the data to a *.csv file, which is Comma Separated Values. That is, for some part of the world it is indeed a comma separating 2 adjacent columns in the text file. For other parts in the world like where I live, it would be Semicolon Separated Values file.

The purpose of the article is twofold:

  • show you how to create a *.csv text file that can be opened up easily in Excel, just by double-click on the filename
  • opening up the text file in Excel should survive Excel’s automatic conversions for dates and other numeric values

The trick I discovered goes like this. You can do your AsciiOutput as always but you will create a simple Excel formula in your *.csv output - in such a way that opening up the file in Excel will lead to Excel showing the result of the formula and not change the underlying values. I will show you this visually and with TI code.

TI code

What we have to do, is use the DataSourceAsciiQuoteCharacter and DataSourceAsciiDelimiter functions in a smart way:

DataSourceAsciiQuoteCharacter = '"';
DataSourceAsciiDelimiter = ';=';

Only 1 character that is different from what I would normally use:

DataSourceAsciiQuoteCharacter = '"';
DataSourceAsciiDelimiter = ';';

Yes, the = character does the magic. It allows to have the ="text string" kind of formula in Excel, or for numbers, =5000. In both cases, the formatting is preserved and Excel will not apply its own logic in all wisdom/stubbornness. If you want you can of course copy/paste values the formulas.

In case you only need to apply this trick to 1 column, for example to not have the leading 0's truncated from an account number:

AsciiOutput( 'test.csv', vCostCenter, '=' | Char(34) | vAccount | Char(34), NumberToString( vValue ));
# or:
AsciiOutput( 'test.csv', vCostCenter, '="' | vAccount | '"', NumberToString( vValue ));

See here


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links