Import APQC PCF files into Architool. #jArchi

Import APQC PCF.ajs

// Author: Pedro Duque
// Requires: jArchi - https://www.archimatetool.com/plugins/#jArchi
// Requires: PapaParse – https://www.papaparse.com/
// Purpose: Import APQC PCF excel sheet into ArchiTool 
// Date: 2022-08-19
// (credits: some code from https://gist.github.com/christhearchitect/34cde79ef73e5ee45deaaf55b1509ee1 used to import from CSV )
// Version 0.1
// Change: Initial version

/*
 * Add this function to excel with APQC PCF:
 *
 * Function GetComments(pRng As Range) As String
 *   If Not pRng.Comment Is Nothing Then
 *     GetComments = pRng.Comment.Text
 *    End If
 * End Function
 * 
 * add a column with =GetComments(C2) in combined worksheet and copy down.
 *
 * Export APQC PCF full model into a CSV file.
 *
 * This script reads the CSV file with APQC PCF content and the following columns: 
 * "PCF ID","Hierarchy ID","Name","Difference Index","Change details","Metrics available?"
 * 
 */
 
var debug = false;
var limitProcessing = 9999; 

// ****** CONFIGURATION ****** 
var levels = ["Category","Process Group","Process","Activity","Task"]
var elementType = ["business-process","business-process","business-process","business-process","business-process"]; //Element type for each level
var elementFolder = ["folder.Business","folder.Business","folder.Business","folder.Business","folder.Business","folder.Relations"]; //add all 5 Folders per level and an extra one for relationships
//var elementType = ["grouping","grouping","business-process","business-process","business-process"]; //Element type for each level
//var elementFolder = ["folder.Other","folder.Other","folder.Business","folder.Business","folder.Business","folder.Relations"]; //add all 5 Folders per level and an extra one for relationships
var relationType = ["","composition-relationship","composition-relationship","composition-relationship","composition-relationship"] //Relationship type with previous level
var mainFolder = "PCF"
var folderLevels = 3 // 0 will create only the main folder. Higher numbers will create subfolders to aggregate elements of that level.

var maxImportLevel = 5 // 0 will disable the creation/update of elements, other than that [1..5]
var maxViewLevel = -1 //-1 will disable view creation

var fileName=""

main();

// *******************************************************
// Main orchestrator
// *******************************************************
function main() {
	load(__DIR__ + "papaparse/papaparse.min.js");
	debug?console.log("// Loaded library: PapaParse"):null;
	debug?console.clear():null; 
	debug?console.log("Import APQC PCF\n---------------------\n"):null;
	if (maxImportLevel>0)  {
		// read data from CSV file
		var theData = readCSV();
		// create the elements and relationships
		if (theData) createModel(theData, maxImportLevel);
	}
	// create relevant views (TBD)
	if (maxViewLevel>=0) createView(maxViewLevel);
}


// *******************************************************
// Open a CSV file, parse it and return array with values
// *******************************************************
function readCSV() {
	var filePath = window.promptOpenFile({ title: "Open CSV file", filterExtensions: ["*.CSV"], fileName: "" });

	if (filePath) {
		fileName = filePath.replace(/^.*(\\|\/|\:)/, '');
		var FileReader = Java.type("java.io.FileReader");
		var Types = Java.type("java.nio.charset.StandardCharsets");
		var theCSVFile = new FileReader(filePath,Types.UTF_8);

		var theCSV ="";
		
		var data = theCSVFile.read();
		debug?console.log("// Loading file:"+fileName):null;
		while(data != -1) {
			var theCharacter = String.fromCharCode(data);
			theCSV+=theCharacter;
			data = theCSVFile.read();
		}
		theCSVFile.close();
		console.log("> CSV file loaded");
		
		theDataFile = Papa.parse(theCSV);
		theData = theDataFile.data;
		console.log("> Parsing Complete");
		
		return theData;
	}
}

// *******************************************
// returns the level based on hierarchy [1..5]
// *******************************************
function getLevel(hierarchy) {
	//lucky that PCF only have .0 on first level categories!
	return (hierarchy.slice(-2)=='.0'?1:hierarchy.split('.').length);
}

// ***********************************************
// returns the Hierarchy ID of the parent element
// ***********************************************
function getParentID(hierarchy) {
	var hierarchyArray = hierarchy.split('.');
	hierarchyArray.pop();
	return (hierarchyArray.join(".")).concat((getLevel(hierarchy)===2)?".0":"");	
}

// *******************************************************
// returns the Hierarchy ID of the specific ancestor element
// *******************************************************
function getAncestorID(hierarchy,level) {
	var hierarchyArray = hierarchy.split('.');
	return (hierarchyArray.slice(0,level).join(".")).concat(level===1?".0":"");	
}

// **************************************************************
// Return destination folder. If not created, created folder tree
// Max depth: folderLevels
// **************************************************************
function getDestFolder(hierarchy) {
	var level=getLevel(hierarchy);
	var theFolder = $(elementFolder[level-1]).find("."+mainFolder);
	
	if ((Math.min(folderLevels,level)<1)) {
	// if not using folders per level
		destFolder=theFolder.first();
		debug?console.log("(getDestFolder) Not using Folder structure for ",hierarchy,". Dest Folder:",destFolder):null;
	} else {
		// Get destination folder. 
		destFolder=theFolder.children("folder").find("."+getAncestorID(hierarchy,Math.min(folderLevels,level)));		
		var targetHierarchyID=getAncestorID(hierarchy,Math.min(folderLevels,level));
		debug?console.log("(getDestFolder) Using Folder structure for ",hierarchy," found folders:",destFolder," desired Folder:",targetHierarchyID):null;
		
		if (destFolder.length==0) {
		// destFolder not found. Create it!
			debug?console.log("(getDestFolder) Creating folder:",getAncestorID(hierarchy,Math.min(folderLevels,level))):null;
			// Navigate folder structure and create necessary folders
			var i=1;
			while (destFolder.prop("Hierarchy ID")!=targetHierarchyID) {
				debug?console.log("(getDestFolder) Search for:",getAncestorID(hierarchy,i)):null;
				destFolder=theFolder.children("folder").filter(function(e) {return (e.prop("Hierarchy ID")===getAncestorID(hierarchy,i))});
				if (destFolder.length===0) {
					debug?console.log("(getDestFolder) Not Found. Creating folder: i=",i,"hierarchy=",hierarchy," ancestorID=",getAncestorID(hierarchy,i)):null;
					var newFolder=theFolder.first().createFolder(getAncestorID(hierarchy,i));
					newFolder.prop("Hierarchy ID",getAncestorID(hierarchy,i));
					debug?console.log("(getDestFolder) folder created: i=",i,"ancestor=",theFolder.first(),"folder=",newFolder):null;
					destFolder=newFolder;
					theFolder=$(newFolder);
				} else {
					debug?console.log("(getDestFolder) Folder found:",destFolder):null;
					destFolder=destFolder.first();
					theFolder=$(destFolder);
				}
				i++;
			}
		} else {
			destFolder=destFolder.first();
		}
	}
	return destFolder;
}

// **************************************************************
// Create model, separate elements in diferent folders
// **************************************************************
function createModel(theData, maxLevel) {
	// Counters
	var updated=0;
	var created=0;

	var headerColumns = ["PCF ID","Hierarchy ID","Name","Difference Index","Change details","Metrics available?","Comments"]; 

	debug?console.log("// Actual CSV header: '"+theData[0].slice(0,headerColumns.length)+"' / Expected header: '"+headerColumns+"'"):null;
	if (theData[0].slice(0,headerColumns.length).toString()==headerColumns.toString()) {
		// Header matches the expected columns
		
		// Create main folders
		for (i=0; i<elementFolder.length; i++) {
			if ($(elementFolder[i]).find("."+mainFolder).length==0) {
				var folder = $(elementFolder [i]).first();
				folder.createFolder(mainFolder);
				debug?console.log("Created folder:",mainFolder," in ",folder):null;
			}
		}

		// Iterate over each data row and create/update the corresponding new elements
		for (i=1; i<(debug?Math.min(limitProcessing,theData.length):theData.length); i++) { 
			debug?console.log("// Read from CSV",i,theData[i].length,theData[i][0].length==0?"(skipping)":"(processing)"):null;
			if (i%100===0) 
				if (debug) {
					if (window.confirm("Line "+i+": updates="+updated+" new="+created+". Stop?")) return;
				} else {
					console.log("Line "+i+": updates="+updated+" new="+created);
				}
			if (theData[i][0].length>0)  { 
				// Read data from non empty line
				var elemPCFID = theData[i][0];
				var elemHierarchy = theData[i][1];
				var elemName = elemHierarchy+" "+theData[i][2];
				var elemDocumentation = theData[i][6];
				
				// Determine Level based on the hierarchy
				var elemLevel=getLevel(elemHierarchy);

				//skip if level out of scope
				if (elemLevel>maxLevel) continue;
				
				// Find element it it already exists
				var elem = $(elementType[elemLevel]).filter(
					function(e) {
						return (e.prop("PCF ID")==elemPCFID)
					}
				).first();
				
				if (elem) {
					// Update element
					elem.prop("Hierarchy ID",elemHierarchy);
					elem.prop("PCF Level",levels[elemLevel-1]);
					elem.prop("Filename",fileName);
					elem.documentation=elemDocumentation;
					updated++;
					
				} else {
					// Create new element
					var destFolder=getDestFolder(elemHierarchy);
					debug?console.log("-> Creating element: ",elemName, " Hierarchy:",elemHierarchy," Level:",elemLevel," Type:",elementType[elemLevel-1]," Folder:",destFolder):null;
					var newElem = model.createElement(elementType[elemLevel-1], elemName, destFolder);
					newElem.prop("PCF ID",elemPCFID);
					newElem.prop("PCF Level",levels[elemLevel-1]);
					newElem.prop("Hierarchy ID",elemHierarchy);
					newElem.prop("Filename",fileName);
					newElem.documentation=elemDocumentation;
					if (elemLevel>1) {
						// Create relationship with parent element
						var parentID = getParentID(elemHierarchy);
						debug?console.log("-> me:",elemHierarchy," Father ID:",parentID," Search type:",elementFolder[elemLevel-1]+"."+mainFolder):null;
						var srcElem = $(elementType[elemLevel-2]).filter(
						// Find father
							function(e) {
								return (e.prop("Hierarchy ID")==parentID)
							}
						).first();
						destFolder = $(elementFolder[5]).find("."+mainFolder).first();
						debug?console.log("-> Creating Relationship: ",relationType[elemLevel-1],elemName,srcElem,newElem,destFolder):null;
						var newRel = model.createRelationship(relationType[elemLevel-1],elemName,srcElem,newElem,destFolder);
					}
					created++;
				}
			}
		}
	}
}

function createView() {
	
}