AsciiOutput with Pipe Delimiter

Yesterday I needed to export some data from TM1 for loading into SQL Server. I know I can connect directly and publish from TM1 to SQL Server, but I just wanted to use the simple method – create a text file and load it up.

Problem with Comma Separated Values

My problem was that some of the columns contained text that had random commas, so a regular AsciiOutput wouldn’t work cos there was extra commas and when read by SQL Server it delimited that data as well. Grrr.

Solution – Pipe Delimiter

Mmmm. How to solve this one. Bingo!

The solution was that I created a string variable and concatenated pipes between each field . Then I simply used the new string variable in the AsciiOutput and boom tish!

Example

If we define two variables as:

sVariable1 = 'John';

sVariable2 = '10,5';

A normal AsciiOutput would be:

AsciiOutput('c:\text.txt', sVariable1, sVariable2);

This would create a text record in text.txt of “John”,”10″,”5″, i.e. three columns rather than the 2 desired.

Creating a new string variable:

sConcatenated = sVariable1 | '"|"' | sVariable2;

and then putting that into the output as

AsciiOutput('c:\text.txt', sConcatenated);

gives the result in text.txt of “John|10.5”, i.e. two columns only, each separated by a pipe delimiter.

This allows the data then to be loaded into SQL Server by defining the delimiter as a pipe.