Hello everyone,
Just a quick question as I'm dealing with an issue which I'm sure has been solved but can't seem to find anything on this.
I'm doing an ASCIIOUTPUT and one of the fields is an account dimension. This account dimension has 6 digits and various accounts include leading zeros i.e. 000123, 000456, etc.
When exporting to .csv and opening the exported file it drops the leading zeros so the result of this account dim is 123, 456, etc.
Normally I'd output to a .txt and it wouldn't matter but the intent is to grab data from the .csv and copy and paste it to an upload file to an external system.
The account dim that I am exporting is being read from the variables tab on the TI i.e. vAccount.
The ASCIIOutPut looks like this ( sFile, vAccount, aaa, bbb, ccc, etc.)
The .csv I am exporting from TM1 is laid out exactly in the order and format required to make the copy/paste function fairly simple except for this small nuisance of the leading zeros being dropped.
Any advice would be much appreciated!
Cheers
Leading zeros in AsciiOutPut being dropped
-
- 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: Leading zeros in AsciiOutPut being dropped
Do not open the csv with Excel but rather Notepad++
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: Leading zeros in AsciiOutPut being dropped
I'm pretty sure Wim is correct and this is just Excel trying to be "helpful" by converting anything that looks like a number into a number. This is decidedly unhelpful when dealing with date strings and account numbers.
If you open in notepad you can verify pretty easily that the account numbers are actually written to the file correctly but text editors are not great when you need to do things like insert or remove columns. To be able to edit the file in Excel don't just open it, use the "import from text file" function then you can specify to treat the column as text and not number.
Also be careful if you then save from Excel that your locale setting for CSV delimiter is the same as the original. We always have issues with users replacing commas with semi-colons and vice versa.
If you open in notepad you can verify pretty easily that the account numbers are actually written to the file correctly but text editors are not great when you need to do things like insert or remove columns. To be able to edit the file in Excel don't just open it, use the "import from text file" function then you can specify to treat the column as text and not number.
Also be careful if you then save from Excel that your locale setting for CSV delimiter is the same as the original. We always have issues with users replacing commas with semi-colons and vice versa.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- MVP
- Posts: 2831
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Leading zeros in AsciiOutPut being dropped
Here's a better idea (at least I think so). Don't give your output file the .csv extension, use .txt or anything else. That way when you open it in Excel it will ask you how you want all the columns defined and then you can say "Text" for the Account column, which will leave your leading zeros. When you open a file in Excel with the .csv extension it doesn't ask you the format and just assumes anything numeric is a number and formats it as such, meaning you lose the leading zeros.
-
- 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: Leading zeros in AsciiOutPut being dropped
Or, if your goal is to open in Excel rather than Notepad, do an AsciiOutput like for example:
Ascii character 34 is a double quote so this is equivalent but somewhat more difficult to write:
Have a look at DataSourceAsciiDelimiter and DataSourceAsciiQuoteCharacter as well.
Originally posted here: http://www.wimgielis.com/tm1_asciioutputtoexcel_EN.htm
Code: Select all
AsciiOutput( 'test.csv', vCostCenter, '=' | Char(34) | vAccount | Char(34), NumberToString( vValue ));
Code: Select all
AsciiOutput( 'test.csv', vCostCenter, '="' | vAccount | '"', NumberToString( vValue ));
Originally posted here: http://www.wimgielis.com/tm1_asciioutputtoexcel_EN.htm
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