Want to know The Truth About CPM?

18 November 2010

Stupid programming tricks #5


Number five in my short and sweet series of tricks and tips.

I call this series “Stupid programming tricks” and this one reflects yr obdnt srvnts’ stupidity.  Try to think of this particular post in the now-I-don’t-have-to-make-Cameron’s-mistake category.

DATAEXPORT is my friend, sometimes

DATAEXPORT came out in Essbase 9.3.1 and allowed Essbase calc scripts to export data without having to resort to the JExport Custom Defined Function.

DATAEXPORT can write to tables or files.  What’s really nice about this method is that you can also define the file field delimiter.  It could be a comma, a pipe, an exclamation point, whatever.  It’s all very easy peasy lemon squeezy

Here’s an example of a comma delimited export in a calc script:
DATAEXPORT "File" "," "c:\temp\testexport.txt" ;

And here’s an example of a pipe delimited export statement:
DATAEXPORT "File" "|" "c:\temp\testexport.txt" ;

But what if you want to do tab delimiting?  Uh-oh, what’s the magic symbol for tab?  I didn’t think it could be doneAnd to be fair to myself, I wasn’t the only one.  You will note a distinct lack of contradiction to my statement in that thread.  In discussions with my good buddy Josie Manzano, she fell into the same trap.  If the Divine Miss Josie can make the mistake, anyone can.

And this is why I like answering questions on OTN

I spend an inordinate amount of time on OTN’s Essbase board in part to answer questions but mostly to learn.  Did I ever in this thread which started out about a poster’s problem in loading a exported data file and quickly detoured into DATAEXPORT.

The way to tab delimit a DATAEXPORT file is to…hit the tab key on your keyboard. 

That’s it.  So simple. So obvious.  Why on earth did I think I needed to send a special character?  Isn’t tab a special character?  Sigh.  My cognitive blinders were on quite firmly for this one.

Here’s the code.  That big space is really a tab character.
DATAEXPORT "File" "              " "c:\temp\testexport.txt" ;

The same approach would go for a space.
DATAEXPORT "File" " " "c:\temp\testexport.txt" ;

Yes, these work – I’ve tried them.

So, definitely a Stupid Programming Trick.  May the next one not be so stupid.

One last note

Btw, Glenn Schwartzberg wrote a great series on DATAEXPORT and I strongly urge you to check them out.

No comments: