ASCIIOUTPUT to csv to show data in different columns
-
- 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
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
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
-
- 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
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
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
-
- 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
That's correct Wim. Is there a way to change the delimiter for export?
-
- 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
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.Ashleigh W wrote: ↑Wed Aug 30, 2017 1:13 pm That's correct Wim. Is there a way to change the delimiter for export?
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 );
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
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.
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
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
- 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
Have not had the time to test it but would TextOutput also apply the DatasourceASCIIDelimiter & DatasourceASCIIQuoteCharacter or do comma delimited?
-
- 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
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
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
-
- 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
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.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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- 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
Thanks Wim-noted.Wim Gielis wrote: ↑Wed Aug 30, 2017 4:16 pm I did a quick test George and TEXTOUTPUT seems to use the datasourceasciidelimiter.
-
- 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
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.
thanks for your help. this should keep me going.
-
- 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
Say again please ?Ashleigh W wrote: ↑Thu Aug 31, 2017 6:30 amit seems to export in comma Delimiter in csv but betweeb every field there is |.
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
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
-
- 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
In fact, this is not correct.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.
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
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
-
- 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
there is a pipe '|' between each field. so in csv when opened in excel it looks like thisWim Gielis wrote: ↑Thu Aug 31, 2017 7:45 amSay again please ?Ashleigh W wrote: ↑Thu Aug 31, 2017 6:30 amit seems to export in comma Delimiter in csv but betweeb every field there is |.
What is it, a comma or a pipe symbol ?
all these words in different cells -- >> blank | Ashleigh | Final | 201709
-
- 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
Interesting, I guess you never know until you try!Wim Gielis wrote: ↑Thu Aug 31, 2017 7:51 amIn fact, this is not correct.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.
You can use the local variable DatasourceASCIIDelimiter in the Prolog tab, and again in the Data tab, for instance. It will work.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- 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
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
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