ASCIIOUTPUT to csv to show data in different columns

Post Reply
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

ASCIIOUTPUT to csv to show data in different columns

Post by Ashleigh W »

Hi everyone,

I am trying to use below code to export some data into csv file. When working with the TM1 cube things are fine but when working with external .txt file it is not working ok. It show results as for example like this
Ashleigh|Final|201709 but i want it in different column in csv and not single text string.

ASCIIOUTPUT(fileName,'Debug', UserName,Version,Period);

please help.
thank you
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Wim Gielis »

I am guessing that the text file settings (like the delimiter for columns) that you use for importing the txt file, still apply when exporting the data. Is this the case maybe ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Ashleigh W »

That's correct Wim. Is there a way to change the delimiter for export?
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ASCIIOUTPUT to csv to show data in different columns

Post by lotsaram »

Ashleigh W wrote: Wed Aug 30, 2017 1:13 pm That's correct Wim. Is there a way to change the delimiter for export?
Well you are stuck my friend as TM1 only provides the local variables DatasourceASCIIDelimiter & DatasourceASCIIQuoteCharacter which apply to BOTH the text file being read AND the text file(s) being exported. You would think it would be sensible to have separate variables but fact is we don't.

So the only thing you can do is to work around it by exporting (from the TI's POV) a single field but splice into this your real delimiter. Expand will be your friend here. To get a good working result with this you will have to have input and export data that doesn't need a quote character.

Code: Select all

###PROLOG
DatasourceASCIIDelimiter = '|';
DatasourceASCIIQuoteCharacter = '';
sDelim = ';'


###DATA
sTextOut = Expand( '%V1%%sDelim%%V2%%sDelim%%V3%%sDelim%%V4%%sDelim%%V5%%sDelim%' );
AsciiOutput( sFileOut, sTextOut );
Let us know how it goes.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Wim Gielis »

Correct, either you use Expand like Lotsaram shows you, either you use the concatenation operator ( | ) to string the data together.
Make sure that your numeric values are exported correctly too, if applicable.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by gtonkin »

Have not had the time to test it but would TextOutput also apply the DatasourceASCIIDelimiter & DatasourceASCIIQuoteCharacter or do comma delimited?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Wim Gielis »

I did a quick test George and TEXTOUTPUT seems to use the datasourceasciidelimiter.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ASCIIOUTPUT to csv to show data in different columns

Post by lotsaram »

Wim Gielis wrote: Wed Aug 30, 2017 2:50 pm Correct, either you use Expand like Lotsaram shows you, either you use the concatenation operator ( | ) to string the data together.
Make sure that your numeric values are exported correctly too, if applicable.
That's actually a hidden benefit of Expand ;) all variables are treated as string even the numerics. If you're not fussy about number formatting then no need to do the additional type conversion.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by gtonkin »

Wim Gielis wrote: Wed Aug 30, 2017 4:16 pm I did a quick test George and TEXTOUTPUT seems to use the datasourceasciidelimiter.
Thanks Wim-noted.
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Ashleigh W »

lotsaram, your example works for me. I also tried to place DatasourceASCIIQuoteCharacter=','; at the bottom of Prolog and it seems to export in comma Delimiter in csv but betweeb every field there is |.

thanks for your help. this should keep me going.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Wim Gielis »

Ashleigh W wrote: Thu Aug 31, 2017 6:30 amit seems to export in comma Delimiter in csv but betweeb every field there is |.
Say again please ?
What is it, a comma or a pipe symbol ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Wim Gielis »

lotsaram wrote: Wed Aug 30, 2017 1:30 pm Well you are stuck my friend as TM1 only provides the local variables DatasourceASCIIDelimiter & DatasourceASCIIQuoteCharacter which apply to BOTH the text file being read AND the text file(s) being exported. You would think it would be sensible to have separate variables but fact is we don't.

So the only thing you can do is to work around it by exporting (from the TI's POV) a single field but splice into this your real delimiter.
In fact, this is not correct.

You can use the local variable DatasourceASCIIDelimiter in the Prolog tab, and again in the Data tab, for instance. It will work.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Ashleigh W »

Wim Gielis wrote: Thu Aug 31, 2017 7:45 am
Ashleigh W wrote: Thu Aug 31, 2017 6:30 amit seems to export in comma Delimiter in csv but betweeb every field there is |.
Say again please ?
What is it, a comma or a pipe symbol ?
there is a pipe '|' between each field. so in csv when opened in excel it looks like this
all these words in different cells -- >> blank | Ashleigh | Final | 201709
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ASCIIOUTPUT to csv to show data in different columns

Post by lotsaram »

Wim Gielis wrote: Thu Aug 31, 2017 7:51 am
lotsaram wrote: Wed Aug 30, 2017 1:30 pm Well you are stuck my friend as TM1 only provides the local variables DatasourceASCIIDelimiter & DatasourceASCIIQuoteCharacter which apply to BOTH the text file being read AND the text file(s) being exported. You would think it would be sensible to have separate variables but fact is we don't.

So the only thing you can do is to work around it by exporting (from the TI's POV) a single field but splice into this your real delimiter.
In fact, this is not correct.

You can use the local variable DatasourceASCIIDelimiter in the Prolog tab, and again in the Data tab, for instance. It will work.
Interesting, I guess you never know until you try!
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: ASCIIOUTPUT to csv to show data in different columns

Post by Wim Gielis »

lotsaram wrote: Thu Aug 31, 2017 12:18 pmInteresting, I guess you never know until you try!
That's what I did :-)
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply