Parse MaxL export outline xml to flat file - see next post for updated, faster code

The new (as of 11.1.2) MaxL "export outline" command has made using the Essbase Java API to export outlines obsolete. It is an order of magnitude faster and doesn't run into the typical Essbase japi issue with ports. The only problem is that it exports to XML format. The resulting file is more difficult to use in downstream processes than a delimited text file. It also can't be used to import back into Essbase. Luckily, however, it is a fairly simple matter to parse the XML to a flat file with a delimiter of your choice:

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.ErrorHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;

/***
 * 
 * @author Harry Gates

Usage
 * 1) Use the MaxL "export outline" command to export a dimension of the outline
 *   export outline "/path_to_otl/Sample.otl" list dimensions {"Account"} with alias_table "Default" to xml_file "/path_to_save_xml/sample_account_dim.xml";
 * 2) Change the delimiter, inputFile, and outputFile
 * 3) Compile and run MaxLExportOutlineParseXML.java
 */

public class MaxLExportOutlineParseXML implements ErrorHandler {
 public static String delimiter;
 public static String inputFile;
 public static String outputFile;
 
 public static void main(String[] args) {
  delimiter = "?";
  inputFile = "/path_to_save_xml/sample_account_dim.xml";
  outputFile = "/path_to_save_result/sample_account_dim.txt";
  
  try {
   convertXML();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
 
  public static void convertXML() throws Exception {
    String output = "";
   
    DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();
    builderFactory.setNamespaceAware(false);
    builderFactory.setValidating(false);
    builderFactory.setIgnoringElementContentWhitespace(true);
    DocumentBuilder builder = null;
    
    File file = new File(inputFile);
       
    InputStream inputStream= new FileInputStream(file);
    Reader reader = new InputStreamReader(inputStream);
     
    InputSource is = new InputSource(reader);
    is.setEncoding("UTF-8");
    
    builder = builderFactory.newDocumentBuilder();
    builder.setErrorHandler(new MaxLExportOutlineParseXML());
    Document xmlDoc = builder.parse(is);
    
    NodeList mbrNL = xmlDoc.getElementsByTagName("Member");
    
    NodeList dimNL = xmlDoc.getElementsByTagName("Dimension");
    String dimName = dimNL.item(0).getAttributes().getNamedItem("name").getNodeValue().replaceAll("\\r\\n|\\r|\\n", " ");
     
    String header = "PARENT0," + dimName + delimiter + "CHILD0," + dimName + delimiter + "ALIAS0," + dimName
     + delimiter + "PROPERTY0," + dimName + delimiter + "FORMULA0," + dimName + delimiter;
    int udaCountTotal = 0;
      
    BufferedWriter outputStream;
    outputStream = new BufferedWriter(new FileWriter(outputFile));
    
    if (mbrNL.getLength() > 0) {
        Node node = null;
  
  for (int i = 0; i < mbrNL.getLength(); i++) {
      node = mbrNL.item(i);
   
      if (node.hasChildNodes()) {
       String memberName = "";
       String parentName = "";
       String UDA = "";
       String alias = "";
       String dataStorage = "";
       String timeBalance = "";
       String memberFormula = "";
       String twoPassCalc = "";
       String consolidation = "";
       String varianceReporting = "";
       int udaCount = 0;
       
       NodeList list = node.getChildNodes();
       for (int j = 0; j < list.getLength(); j++) {
         if (list.item(j).getNodeName().equals("UDA")) {
          UDA += list.item(j).getTextContent().replaceAll("\\r\\n|\\r|\\n", " ") + delimiter;
          udaCount++;
         }
         if (list.item(j).getNodeName().equals("Alias")) {
          alias = list.item(j).getTextContent().replaceAll("\\r\\n|\\r|\\n", " ").replaceAll("\"", " ");
         }      
       }
       udaCountTotal = udaCount > udaCountTotal ? udaCount : udaCountTotal;
    
    NamedNodeMap nodAttrs = node.getAttributes();
       for (int k = 0; k < nodAttrs.getLength(); k++) {
           Attr nodAttr = (Attr)nodAttrs.item(k);
           String nodName = nodAttr.getNodeName();
           if (nodName.equals("name")) {
             parentName = node.getParentNode().getAttributes().getNamedItem("name").getNodeValue().replaceAll("\\r\\n|\\r|\\n", " ");
             memberName = node.getAttributes().getNamedItem("name").getNodeValue().replaceAll("\\r\\n|\\r|\\n", " ");
             parentName = parentName.replaceAll("\"", " ");
             memberName = memberName.replaceAll("\"", " ");
           } else if (nodName.equals("Consolidation")) {
            consolidation = nodAttr.getNodeValue().replaceAll("\"", " ");
            consolidation = consolidation.trim();
           } else if (nodName.equals("DataStorage")) {
            dataStorage = nodAttr.getNodeValue().replaceAll("\"", " ");
            if (dataStorage.equals("DynamicCalc")) {
             dataStorage = "X";
            } else if (dataStorage.equals("DynamicCalcAndStore")) {
             dataStorage = "V";
            } else if (dataStorage.equals("LabelOnly")) {
             dataStorage = "O";
            } else if (dataStorage.equals("NeverShare")){
             dataStorage = "N";
            } else if (dataStorage.equals("ShareData")){
             dataStorage = "";
            }
            dataStorage = dataStorage.trim();
           } else if (nodName.equals("MemberFormula")) {
            memberFormula = nodAttr.getNodeValue();
           } else if (nodName.equals("TwoPassCalc")) {
            twoPassCalc = nodAttr.getNodeValue().replaceAll("\"", " ");
            twoPassCalc = twoPassCalc.equals("Y") ? "T" : twoPassCalc;
            twoPassCalc = twoPassCalc.trim();
           } else if (nodName.equals("TimeBalance")) {
            timeBalance = nodAttr.getNodeValue().replaceAll("\"", " ");
            if (timeBalance.equals("Last")) {
             timeBalance = "L";
            } else if (timeBalance.equals("First")) {
             timeBalance = "F";
            } else if (timeBalance.equals("Average")) {
             timeBalance = "A";
            }
            timeBalance = timeBalance.trim();
           } else if (nodName.equals("VarianceReporting")) {
            varianceReporting = nodAttr.getNodeValue().replaceAll("\"", " ");
            varianceReporting = varianceReporting.equals("Expense") ? "E" : varianceReporting;
            varianceReporting = varianceReporting.trim();
           } else {
            System.out.println(nodAttr.getNodeName() + ": " + nodAttr.getNodeValue());
           }
       }
       
       consolidation = consolidation.equals("") ? "+" : consolidation;
       consolidation = consolidation.equals("") ? "" : " " + consolidation;
       timeBalance = timeBalance.equals("") ? "" : " " + timeBalance;
       dataStorage = dataStorage.equals("") ? "" : " " + dataStorage;
       twoPassCalc = twoPassCalc.equals("") ? "" : " " + twoPassCalc;
       varianceReporting = varianceReporting.equals("") ? "" : " " + varianceReporting;
       
       String properties = consolidation + timeBalance + dataStorage + twoPassCalc + varianceReporting;
          
       output += parentName.replaceAll(" +", " ").trim()
         + delimiter + memberName.replaceAll(" +", " ").trim()
         + delimiter + alias.replaceAll(" +", " ").trim()
         + delimiter + properties.trim()
         + delimiter + memberFormula
         + delimiter + UDA.replaceAll(" +", " ").trim() + "\n";
      }
      }
    }
    System.out.println("udaCountTotal: " + udaCountTotal);
    String udaHeader = "";
    for (int i = 0; i < udaCountTotal; i++) {
     udaHeader += "UDA0," + dimName + delimiter;
    }
    header += udaHeader;
    output = header + "\n" + output;
    outputStream.append(output);
    outputStream.close();
  }
  
  public void fatalError(SAXParseException spe) throws SAXException {
    System.out.println("Fatal error at line " + spe.getLineNumber());
    System.out.println(spe.getMessage());
    throw spe;
  }

  public void warning(SAXParseException spe) {
    System.out.println("Warning at line " + spe.getLineNumber());
    System.out.println(spe.getMessage());
  }

  public void error(SAXParseException spe) {
    System.out.println("Error at line " + spe.getLineNumber());
    System.out.println(spe.getMessage());
  }
}