Change server address in Cognos Analysis for Microsoft Excel (Cafe) files (manually and using NodeJS)

Case: You have a lot of excels created with Cafe tool. But at once you move your servers to another address or may be there is a need to create copy of excel to connect to dev/test server. Cognos TM1 does not give us easy way to do this and the most obvious way is to recreate these files. In this article I will show how to do it another (more geek) way.

First of all – excel file is just a zip archive that contains a lot of different files inside. There are also files that are generated by Cafe extension, which one we will change to get the desired results.

I will show two ways how to do this: first one – manually using text editor Sublime and second one using small NodeJS script.

Manual change with Sublime 3

1. Change extension of .xlsx or .xls or .xlsm file to .zip;
2. Unzip archive;
3. Open Sublime;
4. Open find and replace panel Shift+Cmd+F (on Mac OS) or Shift+Ctrl+F (on Windows);
5. In Find field insert current server name which is used in Excel file (for example http://oldserver:9510;
6. In Where field you need to insert path to folder which contains unzipped excel;
7. In Replace field insert new name of server (for example http://newserver:9510);
8. Press Replace button. Sublime will replace old server name to new one in all files;
9. Save and close all tabs that Sublime opens. These are files where Sublime finds the name of old server;
10. Create new .zip archive with all excels content. Make sure that _rels, docProps, xl and other files/folders are the top level objects in .zip

11. Rename back .zip to .xlxs, .xls or .xlxm.

Thats all, now your new Excel is ready to be used with new server.

Automatic change using NodeJS script

Also there are another way to do this changes. Here is the NodeJS async function that you can use to do all staff. Just call cafeServerChanger function with 3 parameters:

1. Path to the excel file;
2. Previous server name (http://oldserver:9510);
3. Name of server that you would like to use in new file (http://newserver:9510)

Do not forget to install 4 npm packages: extract-zip, moment, archiver, rimraf that are used in script.

const fs = require('fs');
const path = require('path');
const util = require('util')

const extract = require('extract-zip');
const moment = require('moment');
const archiver = require('archiver');
const rimraf = require("rimraf")

const walk = (dir, done) => {
  var results = [];
  fs.readdir(dir, function(err, list) {
    if (err) return done(err);
    var i = 0;
    (function next() {
      var file = list[i++];
      if (!file) return done(null, results);
      file = dir + '/' + file;
      fs.stat(file, function(err, stat) {
        if (stat && stat.isDirectory()) {
          walk(file, function(err, res) {
            results = results.concat(res);
            next();
          });
        } else {
          results.push(file);
          next();
        }
      });
    })();
  });
}

const escapeRegExp = (string) => {
  return string.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); // $& means the whole matched string
}

const cafeServerChanger = async (file, serverFrom, serverTo) => {
  try {
    if(!fs.existsSync(file)) throw `Could not find file '${file}'`

    const fileParse = path.parse(file);
    
    const tempName = 'temp_' + moment().format('YYYYMMDDHHmmssSSS') + '_' + Math.round((Math.random() * (10000 - 1) + 1));
    const tempFolder = path.join(fileParse.dir, tempName);
    fs.mkdirSync(tempFolder)

    // unzip excel to temp folder
    await new Promise((resolve, reject) => {
      extract(file, { dir: tempFolder }, function (err) {
        if(err) return reject(err);
        console.log('Unzip successfully');
        return resolve();
      })
    })
    
    // create bin buffer for server line
    const nullHEX = 0x00;
    let lineLetterBuffArray = [];
    for(let i = 0; i < serverTo.length; i++){
      let l = serverTo[i];
      let buffer = Buffer.from([l.charCodeAt(0), nullHEX]);
      lineLetterBuffArray.push(buffer)
    }
    const finalBuffer = new Buffer.concat(lineLetterBuffArray);

    // replace all findings
    await new Promise((resolve, reject) => {
      let numberOfChanged = 0;
      walk(tempFolder, (err, zipFiles) => {
        if(err) return reject(err);
  
        for(let i = 0; i < zipFiles.length; i++){
          let file = zipFiles[i];
          let fileParse = path.parse(file);
          let content = fs.readFileSync(file).toString()
          let regExp = new RegExp(serverFrom, 'ig');

          if(fileParse.base.match(/^customProperty\d+.bin$/)){
            content = content.replace(/\0/g, '');
            if(content.match(regExp)){
              fs.writeFileSync(file, finalBuffer);
              numberOfChanged++;
            }
          } else {
            if(content.match(regExp)){
              content = content.replace(new RegExp(escapeRegExp(serverFrom), 'ig'), serverTo)
              fs.writeFileSync(file, content);
              numberOfChanged++;
            }
          }
        }
        console.log(`Change ${numberOfChanged} files`);
        return resolve(zipFiles)
      });
    })
    
    // Zip to new file
    const filePathResult = path.join(fileParse.dir, `${fileParse.name}.changed${fileParse.ext}`);
    if(fs.existsSync(filePathResult)) fs.unlinkSync(filePathResult);

    console.log(`Start compression`)
    return await new Promise((resolve, reject) => {
      var output = fs.createWriteStream(filePathResult);
      var archive = archiver('zip', {
        zlib: { level: 5 } // Sets the compression level.
      });

      archive.on('warning', (err) => {
        if (err.code !== 'ENOENT'){ 
          return reject(err);
        }
      });
      archive.on('error', (err) => reject(err));
      output.on('close', () => {
        console.log(archive.pointer() + ' total bytes');
        console.log(`New file is '${filePathResult}'`)
        console.log(`Delete temp folder`)
        rimraf.sync(tempFolder);
        return resolve(filePathResult);
      });

      output.on('end', () => console.log('Data has been drained'));

      archive.directory(tempFolder, false);
      archive.pipe(output);
      archive.finalize();
    })
  } catch(e){
    return Promise.reject(e);
  }
}

module.exports = cafeServerChanger;

You can use and modify these code in any way. Good luck!