While working on an ASO optimization problem we need to use the full data set however have it be scrubbed. Based on client preference I went down the MS route and started using Regular Expressions in VB Script.  This quickly became a frustrating exercise as these “Regular Expressions” do not work like those of Perl or other real Regular Expressions. The goal is to replace all numeric data with a hard-coded one — 9.0 in this instance.

Update: Oracle Ace Tim German relates if you happen to have Essbase 11.1.2.4 you can also use MaxL to export anonymous data.

So with Microsoft “Regular Expressions” 3.1459 became 9.0 9.0 9.0 — whereas implementing the same logic in Perl gives the expected 9.0 Working code below:

[code language=”perl”]
# Scrub.pl
# Author: John A. Booth
#
#USAGE:
# perl scrub.pl <FILEPATH>
# e.g.
# perl scrub.pl C:dataasodump.txt
#
# creates C:dataasodump.txt.scrub
#
# Converts every numeric value to 9.0
#

if ( $#ARGV==0 && -f $ARGV[0] ) {
open(INFILE,$ARGV[0]) || die(“Could not open input file”);
open(OUTFILE,”>$ARGV[0].scrub”) || die (“Could not open output file”);

while(<INFILE>) {
s/s[0-9]+[.]*[0-9]*/ 9.0/g;
print OUTFILE $_;
}

print “n”;
print “Created file $ARGV[0].scrubn”;
close(INFILE);
close(OUTFILE);
}
else {
if ($#ARGV==0) {
print “n”;
print “Cannot find input file $ARGV[0]n”;
}
else {
print “n”;
print “Usage: $0 input.txtn”;
}
}
[/code]


John A. Booth