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.
