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.
