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 into a single string variable. Then I simply used the string variable in the AsciiOutput and boom tish!

Example

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 AsciiOutput as (‘c:\text.txt’, sConcatenated); gives the result in text.txt of “John”|”10.5”, i.e. two columns only, each also with a double inverted comma.

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