AsciiOutput to Excel
- Nov. 15, 2016
Introduction
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 ));
