A Stupid Excel, Plannning, and Cameron Trick

Will I ever learn?

Will I?  Will I?  Sigh, no, apparently I will not.  I cannot believe I didn’t catch this before.  I have been struggling with this for, oh, about 6 years (whenever 11.1.1.1 came out and with it outlineload.cmd).  I am actually really and truly mad at myself for not knowing this.  Idiot.

The background

During a long, troubling, stressful, and not particularly fun weekend (sorry for the whine but not knowing this really frosts my cookies) trying to get ASO Planning to do something it just cannot do for my KScope14 session, I stumbled across this simple solution to Planning outlineload.cmd files in Excel.  

The problem

When a member has a comma or special character in it on metadata export (outlineload.cmd and with 11.1.2.3, Planning itself will export dimensionality) Planning will wrap double quotes around the member or alias so that it can correctly parse the field and the record on the way back in.  What happens then in Excel (I tested on Excel 2010 but this has been the way it’s worked since at least Excel 2003 and probably before) is this:

In case you can’t tell, an Account with the member name Depr,Taxes,Insur. Is spread over multiple columns in Excel where in fact it should be in the Parent column.

What’s even weirder is that the member name is in the correct column.  See the yellow highlighted cell A477.  So what’s up with the pink highlighted data in A478, B478, C478, and actually just about everything else in row 478?

A quick look in Notepad++ shows little difference.  Or at least one it took me literally almost forever to spot.  Idiot.

Goofus and Gallant

The bad

The good

The test with the answer

Do you see it?  Do you?  Please don’t be as blind as I was.

The answer is spaces.  Or more specifically, spaces after commas.  That’s it.

Bad, bad, bad

Metadata-comma-space-metadata is bad.

Depre, "Depr,Taxes,Insur.", Depreciation, false

Good, good, good

Metadata-comma-metadata is good.

Depre,"Depr,Taxes,Insur.",Depreciation,FALSE

The end of this Stupid Trick

That’s why even in the bad file the first instance of Depr,Taxes,Insur. worked in cell A477 but blew up in B478.  Record 477 didn’t have a leading comma-space as it was the first field in the record.  Record 478 did because it is the second column and had that infernal comma-space preceding it as a delimiter.   This difference in behavior in Excel was the clue that made me finally realize that maybe there was an issue with the way the delimiting was working.

I should also note that I looked around for a definition of comma-delimiting and I could not find:

1. Any document or standard that said that spaces should or should not follow the delimiting commas.
2. Any document or note regarding Excel that stated that Excel works this way.

I can’t believe I am the first to recognize this or figure it out and document it.  Perhaps this is all so obvious that world+dog already know it but it eluded me, hence this post.

To plainly state the solution to this incorrect delimiting in Excel:  All you have to do is go into a text editor and search for comma-space and replace it with plain commas-no space before you import it into Excel.

That second good file?  It’s the result of that search and replace.

Arrrgh, soooooo frustrating.  At least I have one positive thing coming out of this weekend.  May you never make this mistake.  I have a sneaking suspicion that most of you already knew this.  Double arrrgh.

Be seeing you.