Copy and paste from Subset Editor to Excel

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Copy and paste from Subset Editor to Excel

Post by Steve Rowe »

I often copy and paste elements from the subset editor as a quick and easy way of building reports and so forth. What I'd like to know is if there is an option to "stop excel thinking that things may just possbly represent a date so I'll intefere with the format in the cell, oh and I think I'll change the underlyng value as well"?

For example there's an element in the dimension "6-1900", when I paste this into Excel the format gets changes so the cell value is the 01/06/1900 and when I put the cell format to text I get 153.

It's a real pain in the butt! There's various cumbersome workarounds in TM1 that I can do to get the information into Excel but anyone know of a way of stopping it happening in the first place?
Cheers,
Technical Director
www.infocat.co.uk
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Copy and paste from Subset Editor to Excel

Post by Eric »

I think formatting the column/row/cell to text before your paste should do it.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Copy and paste from Subset Editor to Excel

Post by paulsimon »

Steve

I long ago got in to the habit of always giving element names an alphabetic prefix, then all these problems go away, as well as the nastier problems that you get when eg 100001 in both the Account and Cost Centre dimensions. If you have A_100001 and C_100001 then there are no problems and you can also tell easily which dimension an element belongs to.

Eric's suggest works up to a point, but I have still had some odd results even when the column is formatted as text. Even standard Excel functions like VLOOKUP can give odd results.

Regards


Paul
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Copy and paste from Subset Editor to Excel

Post by Alan Kirk »

Steve Rowe wrote:I often copy and paste elements from the subset editor as a quick and easy way of building reports and so forth. What I'd like to know is if there is an option to "stop excel thinking that things may just possbly represent a date so I'll intefere with the format in the cell, oh and I think I'll change the underlyng value as well"?

For example there's an element in the dimension "6-1900", when I paste this into Excel the format gets changes so the cell value is the 01/06/1900 and when I put the cell format to text I get 153.
Add to that when you paste something with commas (from an external data source) into Excel, and Excel spreads it across multiple cells assuming that you want it to be comma delimited data. (Unless you go into cell edit mode first, and paste it in that way.)

Unfortunately there are some things that Excel is bound and determined to "help" you with (with no way to turn them off), and you describe a couple of them.

I concur with Eric's suggestion.

The problems that Paul described are ones that I've only had when I've formatted the cells as text after the fact. In that case, I've found that formulas will still sometimes see the cell entries as numeric values rather than strings. Whacking a single quote mark (or other text identifier) in front of the cell entry then makes the formulas treat it as text, but it's the only thing that does. I don't recall ever having had problems when the cell was pre-formatted as text, though. (Doesn't mean that it's never happened, just that I don't recall it ever happening. And I'm most assiduous about keeping an "Oh yes, some day you will pay for this" grudge list of all the software that I use.)

Oh, and one should ALWAYS format the columns beyond the values as text when copying from the transaction log dialog. Especially when you have element names with leading zeroes, and you want to do a DBRW on each row to check the current value. :evil:
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Copy and paste from Subset Editor to Excel

Post by Steve Vincent »

Excel is crap, live with it ;)

Long ago i got bored of being annoyed by the way it tries to help you by converting things that have no need to be converted. Granted, sometimes its really useful but just like TM1 it'd be nice to have the option to turn it on and off, but we don't so you have to live with the consequences. Just like opening a CSV direct in xHell and then loosing all the leading zeroes from fields such as clock numbers...!
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Copy and paste from Subset Editor to Excel

Post by Eric »

Even standard Excel functions like VLOOKUP can give odd results.
If you ever get odd results in VLOOKUP() or any other formulas. nest TEXT(A1,"@") and it will convert the data to text or VALUE(A1) will convert it to a number.

Eric = Excel junkie (I know it explains a lot)
ExHell
:o
Never heard that one... Nice
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Copy and paste from Subset Editor to Excel

Post by paulsimon »

Eric

Thanks. I tend to use =A1&"" which also does a text conversion. The point is that I don't want to have to start fiddling around with formulas, or formatting things as text, which then means that I can't enter a formula without Excel treating it as text. If I prefix all my element names with an alpha character I just don't get these problems (except when I go to a client's site where someone has got there before me, and has used numerics for element names).

Regards


Paul Simon
Post Reply