Import scripts

Import scripts are used to import data with a custom format.

The script read the data to import and return them as text in a format comptatible with Banana. For details of the formats see Import data from a txt file.

Banana Accounting has already some predefined scripts for the majors banks, for a full list see http://www.banana.ch/portal/addons/filters. Those predefined scripts are automatically installed and updated in Banana through the dialog "Manage filters".

To install an own import script, copy it in one of the following paths. Once enabled in "Manage filter" it will appear in the dialog "Import to accounting".

Example to import a Credit Suisse account statement.

This and other examples are found at the following page: http://www.banana.ch/portal/addons/filters.

// @id = ch.banana.scripts.import.creditsuisse
// @version = 1.2
// @pubdate = 2013-01-31
// @publisher = Banana.ch SA
// @description = Credit Suisse bank (*.csv)
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputfilefilter = Text files (*.txt *.csv);;All files (*.*)
// @inputfilefilter.de = Text (*.txt *.csv);;Alle Dateien (*.*)
// @inputfilefilter.fr = Texte (*.txt *.csv);;Tous (*.*)
// @inputfilefilter.it = Testo (*.txt *.csv);;Tutti i files (*.*)

/**
* Parse the data and return the data to be imported as a tab separated file.
*/
function exec(string) {

    if (Banana.application.version < "7.0.2")
        throw ("This filter requires Banana Accounting 7.0.2 or a newer version");

    //If the file contains double quotations marks, remove them
    var cleanString = string;
    if (cleanString.match(/""/)) {
        cleanString = cleanString.replace(/^"/mg, "");
        cleanString = cleanString.replace(/"$/mg, "");
        cleanString = cleanString.replace(/""/g, "\"");
    }

    var transactions = Banana.Converter.csvToTable(cleanString, ',', '"');

    // Format 1
    var format1 = new CSFormat1();
    if (format1.match(transactions)) {
        transactions = format1.convert(transactions);
        return Banana.Converter.tableToTsv(transactions);
    }

    // Format is unknow, return an error
    return "@Error: Unknow format";
}

/**
* CS Format 1
*
* Example A creditsuisse.#20111109.csv
* Date comptable,Texte,Débit,Crédit,Date de valeur,Solde
* 12.10.2011,"Ordre de bonific. Electronic Banking",950.00,,12.10.2011,15947.29
* 12.10.2011,"Ordre de bonific. Electronic Banking",1600.00,,12.10.2011,
* 12.10.2011,"Ordre de bonific. Electronic Banking",1868.87,,12.10.2011,
* 03.10.2011,"Ordre de bonific. Electronic Banking",1764.18,,03.10.2011,20366.16
 
* Example B creditsuisse.#20121115.csv
* Buchungsdatum,Text,Belastung,Gutschrift,Valutadatum,Saldo
* 12.11.2012,"Vergütungsauftr. Electronic Banking",10000.00,,12.11.2012,4965.05
* 12.11.2012,"Vergütungsauftr. Electronic Banking",20000.00,,12.11.2012,
* 09.11.2012,"Vergütungsauftr. Electronic Banking",,33849.60,09.11.2012,34965.05
*/

function CSFormat1() {

    this.colDate = 0;
    this.colDescr = 1;
    this.colDebit = 2;
    this.colCredit = 3;
    this.colDateValuta = 4;
    this.colBalance = 5;

    /** Return true if the transactions match this format */
    this.match = function (transactions) {
        if (transactions.length === 0)
            return false;

        for (i = 0; i < transactions.length; i++) {
            var transaction = transactions[i];

            var formatMatched = false;
            if (transaction.length === (this.colBalance + 1))
                formatMatched = true;
            else
                formatMatched = false;

            if (formatMatched && transaction[this.colDate].match(/[0-9\.]+/g) && transaction[this.colDate].length == 10)
                formatMatched = true;

            if (formatMatched && transaction[this.colDateValuta].match(/[0-9\.]+/g) && transaction[this.colDateValuta].length == 10)
                formatMatched = true;
            else
                formatMatched = false;

            if (formatMatched)
                return true;
        }

        return false;
    }


    /** Convert the transaction to the format to be imported */
    this.convert = function (transactions) {
        var transactionsToImport = [];

        // Filter and map rows
        for (i = 0; i < transactions.length; i++) {
            var transaction = transactions[i];
            if (transaction.length < (this.colBalance + 1))
                continue;
            if (transaction[this.colDate].match(/[0-9\.]+/g) && transaction[this.colDate].length == 10 &&
            transaction[this.colDateValuta].match(/[0-9\.]+/g) && transaction[this.colDateValuta].length == 10)
                transactionsToImport.push(this.mapTransaction(transaction));
        }

        // Sort rows by date (just invert)
        transactionsToImport = transactionsToImport.reverse();

        // Add header and return
        var header = [["Date", "DateValue", "Doc", "Description", "Income", "Expenses"]];
        return header.concat(transactionsToImport);
    }


    this.mapTransaction = function (element) {
        var mappedLine = [];

        mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate]));
        mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDateValuta]));
        mappedLine.push(""); // Doc is empty for now
        var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
        mappedLine.push(tidyDescr);
        mappedLine.push(Banana.Converter.toInternalAmountFormat(element[this.colCredit]));
        mappedLine.push(Banana.Converter.toInternalAmountFormat(element[this.colDebit]));

        return mappedLine;
    }
}