Embedded Extensions examples Tutorial 3 Import CSV

Documentación •
En este artículo

On this page we show some examples of transactions import extensions.

Each example consists of:

  • The complete JavaScript code of the import extension.
  • The .CSV files in different formats with the transactions data to be imported.
    Some examples contains anonymized data, the description text has no meanings. 
  • The output in .TSV format (Tabulator Separated Values) of the import extension, which contains the transformed csv data in the “transactions.simple” format used to import the transactions in Banana Accounting.

Import transactions example 1

Migros Bank (Switzerland) import transactions with multiple csv formats


// Banana Accounting Extension Javascript
// @id = ch.banana.switzerland.import.migrosbank.tutorial
// @api = 1.0
// @pubdate = 2023-10-10
// @publisher = Banana.ch SA
// @description = Migros Bank - Import account statement .csv (Banana+ Advanced)
// @description.en = Migros Bank - Import account statement .csv (Banana+ Advanced)
// @description.de = Migros Bank - Bewegungen importieren .csv (Banana+ Advanced)
// @description.fr = Migros Bank - Importer mouvements .csv (Banana+ Advanced)
// @description.it = Migros Bank - Importa movimenti .csv (Banana+ Advanced)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @timeout = -1
// @inputencoding = latin1
// @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, isTest) {

   var importUtilities = new ImportUtilities(Banana.document);

   if (isTest !== true && !importUtilities.verifyBananaAdvancedVersion())
      return "";

   var fieldSeparator = findSeparator(string);
   var transactions = Banana.Converter.csvToArray(string, fieldSeparator, '"');

   // Format 1
   var format1 = new MBFormat1();
   if (format1.match(transactions)) {
      transactions = format1.convert(transactions);
      Banana.console.log(Banana.Converter.arrayToTsv(transactions));
      return Banana.Converter.arrayToTsv(transactions);
   }

   importUtilities.getUnknownFormatError();

   return "";
}

/**
 * Migros Bank Format 1 A):
 * Kontoauszug bis: 04.09.2023 ;;;
 * ;;;
 * Kontonummer: 543.278.22;;;
 * Bezeichnung: Privat;;;
 * Saldo: CHF 38547.7;;;
 * ;;;
 * Ramer E. & Ramer-Zahner D.;;;
 * In den Steinreben 6C;;;
 * 4153 Reinach BL;;;
 * ;;;
 * ;;;
 * Datum;Buchungstext;Betrag;Valuta
 * 04.09.23;Zahlungseingang;1838.00;04.09.23
 * 04.09.23;Zahlungs;-204.45;04.09.23
 * 
 * Migros Bank Format 1 B), valutare in futuro se fare un formato differente per conti privati,
 * per ora cambia solo la data e le intestazioni:
 * Moristra rerva eo:;2023-09-13
 * Moristra rerva lant:;2023-10-10
 * ;
 * Sciercipsidea:;Rerva haragine
 * ;
 * ;
 * ;
 * Data;Testo di registrazione;Importo;Valuta
 * 15.09.2023;Frunt stantuisu me quaesecerinum XXX/UT/PUS, Dis Frangunattis 47h, 1782 Raraequone;-105.45;15.09.2023
 * 15.09.2023;DIDUNT Humquit-Costripe EO, Dis Volluvis 1, 7888 Prescrente;-230.95;15.09.2023
 * 19.09.2023;CLAVIANTO AUFERVA EO, DIS MINENT 8, 6686 COLUMEA;-150.80;19.09.2023
 */
var MBFormat1 = class MBFormat1 {

   constructor() {
      this.colDate = 0;
      this.colDescr = 1;
      this.colAmount = 2;
      this.colDateValuta = 3;

      this.colCount = 4;
      this.decimalSeparator = ".";
      this.dateFormat = "dd.mm.yy";
   }

   /** Return true if the transactions match this format */
   match(transactions) {

      if (transactions.length === 0)
         return false;

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

         var formatMatched = false;
         /* array should have all columns */
         if (transaction.length == this.colCount)
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDate] &&
            transaction[this.colDate].match(/^(0[1-9]|[12][0-9]|3[01])[-.](0[1-9]|1[0-2])[-.]\d{2}$/)) {
            formatMatched = true;
         } else if (formatMatched && transaction[this.colDate] &&
            transaction[this.colDate].match(/^(0[1-9]|[12][0-9]|3[01])[-.](0[1-9]|1[0-2])[-.]\d{4}$/)) {
            this.dateFormat = "dd.mm.yyyy";
            formatMatched = true;
         }
         else {
            formatMatched = false;
         }

         if (formatMatched && transaction[this.colDateValuta] &&
            transaction[this.colDateValuta].match(/\b\d{2}[.-]\d{2}[.-](?:\d{2}|\d{4})\b/g)) {
            formatMatched = true;
         }
         else {
            formatMatched = false;
         }

         if (formatMatched) {
            return true;
         }
      }

      return false;
   }

   /** Convert the transaction to the format to be imported */
   convert(rows) {
      var transactionsToImport = [];

      for (var i = 0; i < rows.length; i++) {
         let transaction = rows[i];
         if (transaction.length == this.colCount &&
            transaction[this.colDate].match(/^(0[1-9]|[12][0-9]|3[01])[-.](0[1-9]|1[0-2])[-.](\d{4}|\d{2})$/)) {
            transactionsToImport.push(this.mapTransaction(rows[i]));
         }
      }

      // Sort rows by date
      if (this.dateFormat !== "dd.mm.yyyy") // transactions in the format B are already provided in the correct order.
         transactionsToImport = transactionsToImport.reverse();

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

   /** Return the transaction converted in the import format */
   mapTransaction(transaction) {
      var mappedLine = [];

      mappedLine.push(Banana.Converter.toInternalDateFormat(transaction[this.colDate], this.dateFormat));
      mappedLine.push(""); // Doc is empty for now
      mappedLine.push(transaction[this.colDescr]);
      var amount = transaction[this.colAmount];
      if (amount.length > 0) {
         //check decimal separator, if is comma, we replace it.
         if (amount.indexOf(",") >= 0)
            amount = amount.replace(',', '.');
         if (amount[0] === "-") {
            amount = amount.replace(/-/g, ''); //remove minus sign
            mappedLine.push("");
            mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, "."));

         } else {
            mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, "."));
            mappedLine.push("");
         }
      }

      return mappedLine;
   }

}

/**
 * The function findSeparator is used to find the field separator.
 */
function findSeparator(string) {

   var commaCount = 0;
   var semicolonCount = 0;
   var tabCount = 0;

   for (var i = 0; i < 1000 && i < string.length; i++) {
      var c = string[i];
      if (c === ',')
         commaCount++;
      else if (c === ';')
         semicolonCount++;
      else if (c === '\t')
         tabCount++;
   }

   if (tabCount > commaCount && tabCount > semicolonCount) {
      return '\t';
   }
   else if (semicolonCount > commaCount) {
      return ';';
   }

   return ',';
}


/*
* class ImportUtilities
* Contains methods that can be shared by extensions for importing bank data
*/

var ImportUtilities = class ImportUtilities {
    constructor(banDocument) {
        this.banDocument = banDocument;
        if (this.banDocument === undefined)
            this.banDocument = Banana.document;
    }

    //The purpose of this function is to convert all the data into a format supported by Banana
    convertToBananaFormat(intermediaryData) {
        var columnTitles = [];

        //Create titles only for fields not starting with "_"
        for (var name in intermediaryData[0]) {
            if (name.substring(0, 1) !== "_") {
                columnTitles.push(name);
            }
        }
        //Function call Banana.Converter.objectArrayToCsv() to create a CSV with new data just converted
        var convertedCsv = Banana.Converter.objectArrayToCsv(columnTitles, intermediaryData, "\t");

        return convertedCsv;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertCsvToIntermediaryData(inData, convertionParam) {
        var form = [];
        var intermediaryData = [];
        //Add the header if present 
        if (convertionParam.header) {
            inData = convertionParam.header + inData;
        }

        //Read the CSV file and create an array with the data
        var csvFile = Banana.Converter.csvToArray(inData, convertionParam.separator, convertionParam.textDelim);

        //Variables used to save the columns titles and the rows values
        var columns = this.getHeaderData(csvFile, convertionParam.headerLineStart); //array
        var rows = this.getRowData(csvFile, convertionParam.dataLineStart); //array of array

        //Load the form with data taken from the array. Create objects
        this.loadForm(form, columns, rows);

        //Create the new CSV file with converted data
        var convertedRow;
        //For each row of the form, we call the rowConverter() function and we save the converted data
        for (var i = 0; i < form.length; i++) {
            convertedRow = convertionParam.rowConverter(form[i]);
            intermediaryData.push(convertedRow);
        }

        //Return the converted CSV data into the Banana document table
        return intermediaryData;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertHtmlToIntermediaryData(inData, convertionParam) {
        var form = [];
        var intermediaryData = [];

        //Read the HTML file and create an array with the data
        var htmlFile = [];
        var htmlRows = inData.match(/<tr[^>]*>.*?<\/tr>/gi);
        for (var rowNr = 0; rowNr < htmlRows.length; rowNr++) {
            var htmlRow = [];
            var htmlFields = htmlRows[rowNr].match(/<t(h|d)[^>]*>.*?<\/t(h|d)>/gi);
            for (var fieldNr = 0; fieldNr < htmlFields.length; fieldNr++) {
                var htmlFieldRe = />(.*)</g.exec(htmlFields[fieldNr]);
                htmlRow.push(htmlFieldRe.length > 1 ? htmlFieldRe[1] : "");
            }
            htmlFile.push(htmlRow);
        }

        //Variables used to save the columns titles and the rows values
        var columns = this.getHeaderData(htmlFile, convertionParam.headerLineStart); //array
        var rows = this.getRowData(htmlFile, convertionParam.dataLineStart); //array of array

        //Convert header names
        for (var i = 0; i < columns.length; i++) {
            var convertedHeader = columns[i];
            convertedHeader = convertedHeader.toLowerCase();
            convertedHeader = convertedHeader.replace(" ", "_");
            var indexOfHeader = columns.indexOf(convertedHeader);
            if (indexOfHeader >= 0 && indexOfHeader < i) { // Header alreay exist
                //Avoid headers with same name adding an incremental index
                var newIndex = 2;
                while (columns.indexOf(convertedHeader + newIndex.toString()) !== -1 && newIndex < 99)
                    newIndex++;
                convertedHeader = convertedHeader + newIndex.toString()
            }
            columns[i] = convertedHeader;
        }

        // Banana.console.log(JSON.stringify(columns, null, "   "));

        //Load the form with data taken from the array. Create objects
        this.loadForm(form, columns, rows);

        //Create the new CSV file with converted data
        var convertedRow;
        //For each row of the form, we call the rowConverter() function and we save the converted data
        for (var i = 0; i < form.length; i++) {
            convertedRow = convertionParam.rowConverter(form[i]);
            intermediaryData.push(convertedRow);
        }

        //Return the converted CSV data into the Banana document table
        return intermediaryData;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertToIntermediaryData(inData, convertionParam) {
        if (convertionParam.format === "html") {
            return this.convertHtmlToIntermediaryData(inData, convertionParam);
        } else {
            return this.convertCsvToIntermediaryData(inData, convertionParam);
        }
    }

    //The purpose of this function is to return all the titles of the columns
    getHeaderData(csvFile, startLineNumber) {
        if (!startLineNumber) {
            startLineNumber = 0;
        }
        var headerData = csvFile[startLineNumber];
        for (var i = 0; i < headerData.length; i++) {

            headerData[i] = headerData[i].trim();

            if (!headerData[i]) {
                headerData[i] = i;
            }

            //Avoid duplicate headers
            var headerPos = headerData.indexOf(headerData[i]);
            if (headerPos >= 0 && headerPos < i) { // Header already exist
                var postfixIndex = 2;
                while (headerData.indexOf(headerData[i] + postfixIndex.toString()) !== -1 && postfixIndex <= 99)
                    postfixIndex++; // Append an incremental index
                headerData[i] = headerData[i] + postfixIndex.toString()
            }

        }
        return headerData;
    }

    getLang() {

        var lang = 'en';
        if (this.banDocument)
            lang = this.banDocument.locale;
        else if (Banana.application.locale)
            lang = Banana.application.locale;
        if (lang.length > 2)
            lang = lang.substr(0, 2);
        return lang;
    }
    
    //The purpose of this function is to return all the data of the rows
    getRowData(csvFile, startLineNumber) {
        if (!startLineNumber) {
            startLineNumber = 1;
        }
        var rowData = [];
        for (var i = startLineNumber; i < csvFile.length; i++) {
            rowData.push(csvFile[i]);
        }
        return rowData;
    }

    //The purpose of this function is to load all the data (titles of the columns and rows) and create a list of objects.
    //Each object represents a row of the csv file
    loadForm(form, columns, rows) {
        for (var j = 0; j < rows.length; j++) {
            var obj = {};
            for (var i = 0; i < columns.length; i++) {
                obj[columns[i]] = rows[j][i];
            }
            form.push(obj);
        }
    }

    // The purpose of this function is to sort the data
    sortData(intermediaryData, convertionParam) {
        if (convertionParam.sortColums && convertionParam.sortColums.length) {
            intermediaryData.sort(
                function (row1, row2) {
                    for (var i = 0; i < convertionParam.sortColums.length; i++) {
                        var columnName = convertionParam.sortColums[i];
                        if (row1[columnName] > row2[columnName])
                            return 1;
                        else if (row1[columnName] < row2[columnName])
                            return -1;
                    }
                    return 0;
                });

            if (convertionParam.sortDescending)
                intermediaryData.reverse();
        }

        return intermediaryData;
    }

    verifyBananaPlusVersion() {
        if (!this.banDocument)
            return false;

        var BAN_VERSION_MIN = "10.0";

        // supported Version
        if (Banana.compareVersion && Banana.compareVersion(Banana.application.version, BAN_VERSION_MIN) >= 0) {
            return true;
        }

        // not supported version
        var lang = this.getLang();
        var msg = "This extension requires Banana Accounting+";
        this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
        return false;
    }

    //Check if the version of Banana Accounting is compatible with this class
    verifyBananaAdvancedVersion() {
        if (!this.banDocument)
            return false;

        if (!Banana.application.license || Banana.application.license.licenseType !== "advanced") {
            var lang = this.getLang();
            var msg = "This extension requires Banana Accounting+ Advanced";
            this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
            return false;
        }

        return true;
    }

    getErrorMessage(errorId, lang) {
        if (!lang)
            lang = 'en';
        switch (errorId) {
            case "ID_ERR_FORMAT_UNKNOWN":
                 if (lang == 'it')
                    return "Formato del file *.csv non riconosciuto";
                 else if (lang == 'fr')
                    return "Format de fichier *.csv non reconnu";
                 else if (lang == 'de')
                    return "Unerkanntes *.csv-Dateiformat";
                 else
                    return "Unrecognised *.csv file format";
        }
        return '';
     }

    getLang() {
        var lang = 'en';
        if (Banana.application.locale)
            lang = Banana.application.locale;
        if (lang.length > 2)
            lang = lang.substring(0, 2);
        return lang;
     }

    getUnknownFormatError(){
        let errId = "ID_ERR_FORMAT_UNKNOWN"; //error
        let lang = this.getLang();
        let msg = this.getErrorMessage(errId, lang);
        Banana.document.addMessage(msg, errId);
    }
}

Input data - csv_migrosbank_example_format1_20230906.csv

Input text passed to the exec() function.


Alevenequis xxx: 78.71.5440 ;;;
;;;
Ausplecutum: 457.211.73;;;
Agnuluviant: Neriam;;;
Oprit: BOX 54060.3;;;
;;;
Agile G. & Agile-Tumere L.;;;
Ut lum Ascungilis 1Q;;;
4153 Reinach BL;;;
;;;
;;;
Datum;Buchungstext;Betrag;Valuta
04.09.23;Partuducartudie;1838.00;04.09.23

Output data - csv_migrosbank_example_format1_20230906.tsv

Output text returned by the exec() function.


Date\tDoc\tDescription\tIncome\tExpenses
2023-09-04\t\tPartuducartudie\t1838.00\t

Input data - csv_migrosbank_example_format1_20230908.csv

Input text passed to the exec() function.


Officulo patem crem: 42.35.6581 ;;;
;;;
Office eo patem: 133.273.44;;;
Tabilliquaest: Patem plucinto;;;
Widum: SUB 30221.84;;;
;;;
Amniuntum-Reles Addunnes;;;
Flone H. Nolvo 13 O;;;
10090 Villarbasse TO;;;
;;;
;;;
Data;Testo di registrazione;Importo;Valuta
06.09.23;Anispus rentemple;975.00;06.09.23
04.09.23;Rentemple;-204.45;04.09.23
01.09.23;Anispus rentemple;2300.00;01.09.23
01.09.23;Anispus rentemple;2400.00;01.09.23
31.08.23;Anispus rentemple;2610.00;31.08.23
30.08.23;Rentemple;-166.00;30.08.23
29.08.23;Rentemple;-161.55;29.08.23
29.08.23;Anispus rentemple;140.00;29.08.23
28.08.23;Anispus rentemple;2640.00;28.08.23
18.08.23;Anispus rentemple;2190.00;18.08.23
10.08.23;Vicum eo eviore patem usa 84.60.6581 vi 80.60.6581;-3.00;10.08.23
10.08.23;Raepribulle multubiti morpus usa 84.60.6581 vi 80.60.6581 triente 67010185;-5.00;10.08.23
08.08.23;Rentemple;-216.50;08.08.23
07.08.23;Anispus rentemple;975.00;07.08.23
04.08.23;Anispus rentemple;2500.00;04.08.23
03.08.23;Anispus rentemple;2300.00;03.08.23
31.07.23;Anispus rentemple;2610.00;31.07.23
31.07.23;Anispus rentemple;2540.00;31.07.23
28.07.23;Anispus rentemple;140.00;28.07.23
28.07.23;Anispus rentemple;2640.00;28.07.23
25.07.23;Rentemple;-371.75;25.07.23
24.07.23;Rentemple;-708.25;24.07.23
20.07.23;Anispus rentemple;2190.00;20.07.23
18.07.23;Rentemple;-51.76;18.07.23

Output data - csv_migrosbank_example_format1_20230908.tsv

Output text returned by the exec() function.


Date\tDoc\tDescription\tIncome\tExpenses
2023-07-18\t\tRentemple\t\t51.76
2023-07-20\t\tAnispus rentemple\t2190.00\t
2023-07-24\t\tRentemple\t\t708.25
2023-07-25\t\tRentemple\t\t371.75
2023-07-28\t\tAnispus rentemple\t2640.00\t
2023-07-28\t\tAnispus rentemple\t140.00\t
2023-07-31\t\tAnispus rentemple\t2540.00\t
2023-07-31\t\tAnispus rentemple\t2610.00\t
2023-08-03\t\tAnispus rentemple\t2300.00\t
2023-08-04\t\tAnispus rentemple\t2500.00\t
2023-08-07\t\tAnispus rentemple\t975.00\t
2023-08-08\t\tRentemple\t\t216.50
2023-08-10\t\tRaepribulle multubiti morpus usa 84.60.6581 vi 80.60.6581 triente 67010185\t\t5.00
2023-08-10\t\tVicum eo eviore patem usa 84.60.6581 vi 80.60.6581\t\t3.00
2023-08-18\t\tAnispus rentemple\t2190.00\t
2023-08-28\t\tAnispus rentemple\t2640.00\t
2023-08-29\t\tAnispus rentemple\t140.00\t
2023-08-29\t\tRentemple\t\t161.55
2023-08-30\t\tRentemple\t\t166.00
2023-08-31\t\tAnispus rentemple\t2610.00\t
2023-09-01\t\tAnispus rentemple\t2400.00\t
2023-09-01\t\tAnispus rentemple\t2300.00\t
2023-09-04\t\tRentemple\t\t204.45
2023-09-06\t\tAnispus rentemple\t975.00\t

Input data - csv_migrosbank_example_format1_20231010.csv

Input text passed to the exec() function.


Moristra rerva eo:;2023-09-13
Moristra rerva lant:;2023-10-10
;
Sciercipsidea:;Rerva haragine
;
;
;
Data;Testo di registrazione;Importo;Valuta
15.09.2023;Frunt stantuisu me quaesecerinum XXX/UT/PUS, Dis Frangunattis 47h, 1782 Raraequone;-105.45;15.09.2023
15.09.2023;DIDUNT Humquit-Costripe EO, Dis Volluvis 1, 7888 Prescrente;-230.95;15.09.2023
19.09.2023;CLAVIANTO AUFERVA EO, DIS MINENT 8, 6686 COLUMEA;-150.80;19.09.2023
20.09.2023;Quibula Mindum Eaniertus Habit, Dis cat Brende 28, 1880 Reline, QL3478184100087423875;2190.00;20.09.2023
22.09.2023;CONDUONE PAVIONENT, DIS CAT BRENDE 48, 1880 RELINE, AG04803336078414G776D;6534.85;22.09.2023
28.09.2023;CURICA MITATE, DIS FERBO MEN 87 UT 1880 RELINE, EC143102680158501757U;2640.00;28.09.2023
29.09.2023;Perrede Deceripam, Insunt Pulus 60, 8533 Inatu Serneripe, HK3780577203472652012;140.00;29.09.2023
29.09.2023;Eorore Quam, Dis cat Brende 28, UT-1880 Reline, NW8367667075683018488;2610.00;29.09.2023
29.09.2023;Ludicita Lumilla, Dis Ferbo Men 87, 1880 Reline, JX6823625561033213063;2400.00;29.09.2023
03.10.2023;Biberi Tere, Dis cat Brende 28, 1880 Reline, YD8147137112546525622;2300.00;03.10.2023
06.10.2023;Aufere Adecro in commensectum, Horint Coluva-Exerent 56, 8367 Ireèex, WG0071635058848171552;975.00;06.10.2023
09.10.2023;Nanno Spirit Ripere, Dis cat Brende 28, 1880 Reline, XA1816106073487284374;2500.00;09.10.2023
10.10.2023;Mendisistra sustudabo difico nos 44.60.3252 at 18.60.3252 sideano 23418626;-5.00;10.10.2023
10.10.2023;Humea me punium rerva nos 44.60.3252 at 18.60.3252;-3.00;10.10.2023

Output data - csv_migrosbank_example_format1_20231010.tsv

Output text returned by the exec() function.


Date\tDoc\tDescription\tIncome\tExpenses
2023-09-15\t\tFrunt stantuisu me quaesecerinum XXX/UT/PUS, Dis Frangunattis 47h, 1782 Raraequone\t\t105.45
2023-09-15\t\tDIDUNT Humquit-Costripe EO, Dis Volluvis 1, 7888 Prescrente\t\t230.95
2023-09-19\t\tCLAVIANTO AUFERVA EO, DIS MINENT 8, 6686 COLUMEA\t\t150.80
2023-09-20\t\tQuibula Mindum Eaniertus Habit, Dis cat Brende 28, 1880 Reline, QL3478184100087423875\t2190.00\t
2023-09-22\t\tCONDUONE PAVIONENT, DIS CAT BRENDE 48, 1880 RELINE, AG04803336078414G776D\t6534.85\t
2023-09-28\t\tCURICA MITATE, DIS FERBO MEN 87 UT 1880 RELINE, EC143102680158501757U\t2640.00\t
2023-09-29\t\tPerrede Deceripam, Insunt Pulus 60, 8533 Inatu Serneripe, HK3780577203472652012\t140.00\t
2023-09-29\t\tEorore Quam, Dis cat Brende 28, UT-1880 Reline, NW8367667075683018488\t2610.00\t
2023-09-29\t\tLudicita Lumilla, Dis Ferbo Men 87, 1880 Reline, JX6823625561033213063\t2400.00\t
2023-10-03\t\tBiberi Tere, Dis cat Brende 28, 1880 Reline, YD8147137112546525622\t2300.00\t
2023-10-06\t\tAufere Adecro in commensectum, Horint Coluva-Exerent 56, 8367 Ireèex, WG0071635058848171552\t975.00\t
2023-10-09\t\tNanno Spirit Ripere, Dis cat Brende 28, 1880 Reline, XA1816106073487284374\t2500.00\t
2023-10-10\t\tMendisistra sustudabo difico nos 44.60.3252 at 18.60.3252 sideano 23418626\t\t5.00
2023-10-10\t\tHumea me punium rerva nos 44.60.3252 at 18.60.3252\t\t3.00

Import transactions example 2

Postfinance (Switzerland) import transactions with multiple csv formats


// Banana Accounting Extension Javascript
// @id = ch.banana.switzerland.import.postfinance.tutorial
// @api = 1.0
// @pubdate = 2023-09-29
// @publisher = Banana.ch SA
// @description = Postfinance - Import account statement .csv (Banana+ Advanced)
// @description.de = Postfinance - Bewegungen importieren .csv (Banana+ Advanced)
// @description.en = Postfinance - Import account statement .csv (Banana+ Advanced)
// @description.fr = Postfinance - Importer mouvements .csv (Banana+ Advanced)
// @description.it = Postfinance - Importa movimenti .csv (Banana+ Advanced)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @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(inData, isTest) {

   if (!inData) return "";

   var importUtilities = new ImportUtilities(Banana.document);

   if (isTest !== true && !importUtilities.verifyBananaAdvancedVersion())
      return "";

   if (inData.indexOf("<html") >= 0) {
      var formatHtml1 = new PFHtmlFormat1();
      var rows = formatHtml1.convert(inData);
      var csv = Banana.Converter.objectArrayToCsv(
         ["Date", "DateValue", "Description", "Income", "Expenses"],
         rows);
      return csv;

   } else {
      var fieldSeparator = findSeparator(inData);
      let inDataCleared = clearText(inData);
      var transactions = Banana.Converter.csvToArray(inDataCleared, fieldSeparator);

      // Format SBU 1
      var formatSBU1 = new PFCSVFormatSBU1();
      if (formatSBU1.match(transactions)) {
         transactions = formatSBU1.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Credit Card format 1
      var format1_CreditCard = new PFCSVFormat1_CreditCard();
      if (format1_CreditCard.match(transactions)) {
         transactions = format1_CreditCard.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Format 1
      var format1 = new PFCSVFormat1();
      if (format1.match(transactions)) {
         transactions = format1.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Format 2
      var format2 = new PFCSVFormat2();
      if (format2.match(transactions)) {
         transactions = format2.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Format 3
      var format3 = new PFCSVFormat3();
      if (format3.match(transactions)) {
         transactions = format3.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Format 4
      var format4 = new PFCSVFormat4();
      if (format4.match(transactions)) {
         transactions = format4.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Format 5
      var format5 = new PFCSVFormat5();
      if (format5.match(transactions)) {
         transactions = format5.convert(transactions);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(transactions);
      }

      // Format 6, works with translated column headers.
      var format6 = new PFCSVFormat6();
      // getFormattedData () works with specifics headers and to translate them. 
      let transactionsData = format6.getFormattedData(transactions, importUtilities);
      if (format6.match(transactionsData)) {
         let convTransactions = format6.convert(transactionsData);
         Banana.console.log(Banana.Converter.arrayToTsv(transactions));
         return Banana.Converter.arrayToTsv(convTransactions);
      }
   }

   importUtilities.getUnknownFormatError();

   return "";
}

/**
 * Pulisce il testo dai doppi a capo, con la versione 6 del formato csv, per qualche motivo quando il file .csv
 * viene aperto su windows vengono aggiunti degli a capo aggiuntivi (uno o più).
 * Ogni riga dovrebbe contenere un "\r\n" non di più, anche quelle vuote.
 */
function clearText(text) {
   // Sostituisce tutte le occorrenze multiple di "\r\r\n" con un singolo "\r\n"
   return text.replace(/\r\r\n/g, "\r\n");
}

/**
 * PFCSV Format 6, since february 2024.
 * Date de début:;26.02.2022;;;;;
 * Date de fin:;26.02.2024;;;;;
 * Catégorie:;Tous;;;;;
 * Compte:;CH00000000000000000000;;;;;
 * Monnaie:;CHF;;;;;
 * ;;;;;;
 * Date;Type de transaction;Texte de notification;Crédit en CHF;Débit en CHF;Label;Catégorie
 * ;;;;;;
 * 26.02.2024;Enregistrement comptable;Descr;;-100;;Dépenses autres
 * 26.02.2024;Enregistrement comptable;Descr;;-15;;Dépenses autres
 * ;;;;;;
 * Disclaimer:;;;;;;
 * Le contenu du document a été généré à partir des paramètres de filtrage des clientes et des clients. PostFinance n’est pas responsable du contenu et de son exhaustivité.;;;;;;
 */
function PFCSVFormat6() {

   this.getFormattedData = function (transactions, importUtilities) {
      let headerLineStart = this.getHeaderLineStart(transactions);
      let dataLineStart = headerLineStart == 6 ? 8 : 7;
      // We do a copy as the getHeaderData modifies the content and we need to keep the original version clean.
      var transactionsCopy = transactions.map(function (arr) {
         return arr.slice();
      });
      if (transactionsCopy.length < dataLineStart)
         return [];
      let columns = importUtilities.getHeaderData(transactionsCopy, headerLineStart); //array
      let rows = importUtilities.getRowData(transactionsCopy, dataLineStart); //array of array
      let form = [];

      /** We convert the original headers into a custom format to be able to work with the same
       * format regardless of original's headers language or the position of the header column.
       * We need to translate all the .csv fields as the loadForm() method expects the header and
       * the rows to have the same length.
       * */
      let convertedColumns = [];

      convertedColumns = this.convertHeaderDe(columns, convertedColumns);
      if (convertedColumns.length > 0) {
         importUtilities.loadForm(form, convertedColumns, rows);
         return form;
      }
      // Convert headers from italian. 
      convertedColumns = this.convertHeaderIt(columns, convertedColumns);
      if (convertedColumns.length > 0) {
         importUtilities.loadForm(form, convertedColumns, rows);
         return form;
      }
      // Convert headers from french.
      convertedColumns = this.convertHeaderFr(columns, convertedColumns);
      if (convertedColumns.length > 0) {
         importUtilities.loadForm(form, convertedColumns, rows);
         return form;
      }
      // Convert headers from english.
      convertedColumns = this.convertHeaderEn(columns, convertedColumns);
      if (convertedColumns.length > 0) {
         importUtilities.loadForm(form, convertedColumns, rows);
         return form;
      }

      return [];

   }

   /**
    * With this format, if user does not explicitly set the End Date of the movements
    * when is exporting, then the header and data start at a different row, then we have
    * to figure out if the end date is present or not.
    */
   this.getHeaderLineStart = function (transactions) {
      let endDate = transactions[1][1];
      if (endDate.match(/^\d{2}.\d{2}.\d{4}$/))
         return 6; // the Header is on row 6.
      else
         return 5; // the Header is on row 5.
   }

   this.convertHeaderDe = function (columns) {
      let convertedColumns = [];
      for (var i = 0; i < columns.length; i++) {
         switch (columns[i]) {
            case "Datum":
               convertedColumns[i] = "Date";
               break;
            case "Bewegungstyp":
               convertedColumns[i] = "Type";
               break;
            case "Avisierungstext":
               convertedColumns[i] = "Description";
               break;
            case "Gutschrift in CHF":
            case "Gutschrift in EUR":
            case "Gutschrift in USD":
               convertedColumns[i] = "Income";
               break;
            case "Lastschrift in CHF":
            case "Lastschrift in EUR":
            case "Lastschrift in USD":
               convertedColumns[i] = "Expenses";
               break;
            case "Label":
               convertedColumns[i] = "Label";
               break;
            case "Kategorie":
               convertedColumns[i] = "Category";
               break;
            default:
               break;
         }
      }

      if (convertedColumns.indexOf("Date") < 0
         || convertedColumns.indexOf("Description") < 0
         || convertedColumns.indexOf("Income") < 0
         || convertedColumns.indexOf("Expenses") < 0) {
         return [];
      }
      return convertedColumns;
   }

   this.convertHeaderIt = function (columns, convertedColumns) {
      for (var i = 0; i < columns.length; i++) {
         switch (columns[i]) {
            case "Data":
               convertedColumns[i] = "Date";
               break;
            case "Tipo di movimento":
               convertedColumns[i] = "Type";
               break;
            case "Testo di avviso":
               convertedColumns[i] = "Description";
               break;
            case "Accredito in CHF":
            case "Accredito in EUR":
            case "Accredito in USD":
               convertedColumns[i] = "Income";
               break;
            case "Addebito in CHF":
            case "Addebito in EUR":
            case "Addebito in USD":
               convertedColumns[i] = "Expenses";
               break;
            case "Tag":
               convertedColumns[i] = "Label";
               break;
            case "Categoria":
               convertedColumns[i] = "Category";
               break;
            default:
               break;
         }
      }

      if (convertedColumns.indexOf("Date") < 0
         || convertedColumns.indexOf("Description") < 0
         || convertedColumns.indexOf("Income") < 0
         || convertedColumns.indexOf("Expenses") < 0) {
         return [];
      }

      return convertedColumns;
   }

   this.convertHeaderFr = function (columns, convertedColumns) {
      for (var i = 0; i < columns.length; i++) {
         switch (columns[i]) {
            case "Date":
               convertedColumns[i] = "Date";
               break;
            case "Type de transaction":
               convertedColumns[i] = "Type";
               break;
            case "Texte de notification":
               convertedColumns[i] = "Description";
               break;
            case "Crédit en CHF":
            case "Crédit en EUR":
            case "Crédit en USD":
               convertedColumns[i] = "Income";
               break;
            case "Débit en CHF":
            case "Débit en EUR":
            case "Débit en USD":
               convertedColumns[i] = "Expenses";
               break;
            case "Label":
               convertedColumns[i] = "Label";
               break;
            case "Catégorie":
               convertedColumns[i] = "Category";
               break;
            default:
               break;
         }
      }

      if (convertedColumns.indexOf("Date") < 0
         || convertedColumns.indexOf("Description") < 0
         || convertedColumns.indexOf("Income") < 0
         || convertedColumns.indexOf("Expenses") < 0) {
         return [];
      }

      return convertedColumns;
   }

   this.convertHeaderEn = function (columns, convertedColumns) {
      for (var i = 0; i < columns.length; i++) {
         switch (columns[i]) {
            case "Date":
               convertedColumns[i] = "Date";
               break;
            case "Type of transaction":
               convertedColumns[i] = "Type";
               break;
            case "Notification text":
               convertedColumns[i] = "Description";
               break;
            case "Credit in CHF":
            case "Credit in EUR":
            case "Credit in USD":
               convertedColumns[i] = "Income";
               break;
            case "Debit in CHF":
            case "Debit in EUR":
            case "Debit in USD":
               convertedColumns[i] = "Expenses";
               break;
            case "Tag":
               convertedColumns[i] = "Label";
               break;
            case "Category":
               convertedColumns[i] = "Category";
               break;
            default:
               break;
         }
      }

      if (convertedColumns.indexOf("Date") < 0
         || convertedColumns.indexOf("Description") < 0
         || convertedColumns.indexOf("Income") < 0
         || convertedColumns.indexOf("Expenses") < 0) {
         return [];
      }

      return convertedColumns;
   }

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

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

         if (formatMatched && transaction["Date"] && transaction["Date"].length >= 10 &&
            transaction["Date"].match(/^\d{2}.\d{2}.\d{4}$/))
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched)
            return true;
      }

      return false;
   }

   this.convert = function (transactionsData) {
      var transactionsToImport = [];

      for (var i = 0; i < transactionsData.length; i++) {
         if (transactionsData[i]["Date"] && transactionsData[i]["Date"].length >= 10 &&
            transactionsData[i]["Date"].match(/^\d{2}.\d{2}.\d{4}$/)) {
            transactionsToImport.push(this.mapTransaction(transactionsData[i]));
         }
      }

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

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

   this.mapTransaction = function (transaction) {
      let mappedLine = [];

      mappedLine.push(Banana.Converter.toInternalDateFormat(transaction["Date"], "dd.mm.yyyy"));
      mappedLine.push(Banana.Converter.toInternalDateFormat("", "dd.mm.yyyy"));
      mappedLine.push("");
      mappedLine.push("");
      let trDescription = transaction["Description"] + ", " + transaction["Type"];
      mappedLine.push(trDescription);
      mappedLine.push(Banana.Converter.toInternalNumberFormat(transaction["Income"], '.'));
      let expAmount = transaction["Expenses"].replace(/-/g, ''); //remove minus sign
      mappedLine.push(Banana.Converter.toInternalNumberFormat(expAmount, '.'));

      return mappedLine;
   }

}

/**
 * Credit Card format 1
 * Kartenkonto:;0000 1234 5467 7654
 * Karte:;XXXX XXXX XXXX 1111 PostFinance Visa Business Card
 * Datum;Buchungsdetails;Gutschrift in CHF;Lastschrift in CHF
 * 2023-08-24;"Tankstelle Marche        Brugg BE";;-94.70
 * 2023-08-21;"Tankstelle Marche        Brugg BE";;-114.05
 * 2023-08-10;"6131 STORNO JAHRESPREIS";80.00;
**/
function PFCSVFormat1_CreditCard() {

   this.colDate = 0;
   this.colDescr = 1;
   this.colCredit = 2;
   this.colDebit = 3;

   this.dateFormat = 'dd-mm-yyyy';

   /** 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.colDebit + 1)
            formatMatched = true;
         else
            formatMatched = false;
         if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\-)[0-9]{2}(\-)[0-9]{4}/g)) {
            formatMatched = true;
         } else if (formatMatched && transaction[this.colDate].match(/[0-9]{4}(\-)[0-9]{2}(\-)[0-9]{2}/g)) {
            formatMatched = true;
            this.dateFormat = 'yyyy-mm-dd';
         } 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.colAmount + 1))
            continue;
         if (transaction[this.colDate] && transaction[this.colDate].match(/[0-9]{2,4}(\-)[0-9]{2}(\-)[0-9]{2,4}/g)
            && transaction[this.colDate].length == 10)
            transactionsToImport.push(this.mapTransaction(transaction));
      }

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

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


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

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

      return mappedLine;
   }
}
/**
 * PFCSV Format 5
 * Example: pfcsv.#20230901
**/
function PFCSVFormat5() {

   this.colDate = 0;
   this.colMovType = 1;
   this.colDescr = 2;
   this.colCredit = 3;
   this.colDebit = 4;

   this.dateFormat = 'dd.mm.yyyy';

   /** 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.colDebit + 1)
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.)[0-9]{2}(\.)[0-9]{2}/g)) {
            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.colAmount + 1))
            continue;
         if (transaction[this.colDate] && transaction[this.colDate].match(/[0-9]{2}(\.)[0-9]{2}(\.)[0-9]{4}/g) && transaction[this.colDate].length == 10)
            transactionsToImport.push(this.mapTransaction(transaction));
      }

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

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


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

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

      return mappedLine;
   }
}

/**
 * PFCSV Format 4
 * Example: pfcsv.#20230509
 * Fœnum porto natio:;0000 8003 3386 9363
 * Natio:;LIAM LIAM LIAM 5526 PecuLeverba Aturaequat Cocet Voluna
 * Tuundit nostinsan:;06.04.2022 - 05.05.2022
 * Data;Denominazione;Accredito in CHF;Addebito in CHF;Importo in CHF
 * 2022-05-04;"ARTION *PRATIUNDICO      52163467544  XXX";;52.00;
 * 2022-05-04;"1.7% SUPPL. CHF ALL'ESTERO";;0.88;
 * 2022-05-04;"ARTION *EXPECT CUNT      1324126664   NOS";;21.93;
 * 2022-05-03;"ARTION *EXPECT CUNT      1324126664   NOS";;11.11;
 * 2022-05-03;"ARTION *MENTIO SET       1324126664   STO";;15.00;
 * 2022-05-03;"1.7% SUPPL. CHF ALL'ESTERO";;0.26;
 * 2022-05-02;"PATTINDE NATHOC FŒNUM NATIO";300.00;;
 * 2022-05-01;"ARATIMOTE PATUBIT        MODO CONDE MONCH NIS 0.56 Effect 8.1480 ost 37.77.6604 TER 0.62 8.52% de todivispect cor pasus fertumquobsemo TER 0.77";;8.44;
**/
function PFCSVFormat4() {

   this.colDate = 0;
   this.colDescr = 1;
   this.colCredit = 2;
   this.colDebit = 3;
   this.colAmount = 4;

   this.dateFormat = 'dd-mm-yyyy';

   /** 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.colAmount + 1)
            formatMatched = true;
         else
            formatMatched = false;
         if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\-)[0-9]{2}(\-)[0-9]{4}/g)) {
            formatMatched = true;
         } else if (formatMatched && transaction[this.colDate].match(/[0-9]{4}(\-)[0-9]{2}(\-)[0-9]{2}/g)) {
            formatMatched = true;
            this.dateFormat = 'yyyy-mm-dd';
         } 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.colAmount + 1))
            continue;
         if (transaction[this.colDate].match(/[0-9]{2,4}(\-)[0-9]{2}(\-)[0-9]{2,4}/g) && transaction[this.colDate].length == 10)
            transactionsToImport.push(this.mapTransaction(transaction));
      }

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

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


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

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

      return mappedLine;
   }
}

/**
 * PF Html Format 1
 * Html table with the followings colums:
 * 0:Details; 1:Date; 2:Description; 3:Income; 4:Expenses; 5:DateValue; 6:Balance;
**/
function PFHtmlFormat1() {

   /** This function defines the convertion of the single html table rows to Banana fields.
     This is the only function to be adapted to the desired format. */
   this.htmlRowToObject = function (htmlString) {

      // Extract html fields (tags td)
      var htmlTableFields = htmlString.match(/<td[^>]*>[\s\S]*?<\/td>/g); //[\s\S]*? match all chars non gready
      if (!htmlTableFields)
         return null;

      // Verify fields count
      if (htmlTableFields.length < 6)
         return null;

      // Verify if date field match
      var date = this.htmlText(htmlTableFields[1]);
      if (!date.match(/[0-9.]{8}/))
         return null;

      // Convert row
      var rowObject = {};
      rowObject.Date = Banana.Converter.toInternalDateFormat(this.htmlText(htmlTableFields[1]));
      rowObject.Description = Banana.Converter.stringToCamelCase(this.htmlText(htmlTableFields[2]));
      rowObject.ContraAccount = "";
      rowObject.Income = Banana.Converter.toInternalNumberFormat(this.htmlText(htmlTableFields[3]));
      rowObject.Expenses = Banana.Converter.toInternalNumberFormat(this.htmlText(htmlTableFields[4]));
      rowObject.DateValue = Banana.Converter.toInternalDateFormat(this.htmlText(htmlTableFields[5]));
      rowObject._Balance = Banana.Converter.toInternalNumberFormat(this.htmlText(htmlTableFields[6]));
      return rowObject;
   }

   /** This function extract from the html the data to be imported in Banana Accounting.
       It use the function htmlRowToObject to convert the single data rows. */
   this.convert = function (htmlString) {
      var rows = [];
      var htmlTables = htmlString.match(/<tbody[^>]*>[\s\S]*?<\/tbody>/g);  //[\s\S]*? match all chars non gready
      if (htmlTables) {
         for (var t = 0; t < htmlTables.length; t++) {
            var htmlTableRows = htmlTables[t].match(/<tr[^>]*>[\s\S]*?<\/tr>/g); //[\s\S]*? match all chars non gready
            if (htmlTableRows) {
               for (var r = 0; r < htmlTableRows.length; r++) {
                  var row = this.htmlRowToObject(htmlTableRows[r]);
                  if (row) {
                     rows.push(row);
                  }
               }
            }
         }
      }
      return rows;
   }

   /** This function extract the text inside an html element */
   this.htmlText = function (htmlString) {
      // Read text from html string
      // The text is found between each ">...<" sequence
      var retText = "";
      var htmlTexts = htmlString.match(/>[^<]+</g);
      if (htmlTexts) {
         for (var i = 0; i < htmlTexts.length; i++) {
            var htmlSubText = htmlTexts[i];
            if (htmlSubText.length > 2)
               retText = retText + htmlSubText.substr(1, htmlSubText.length - 2);
         }
      }

      // Remove line feeds
      retText = retText.replace(/^[ \n\r]+/, ""); // at the beginning
      retText = retText.replace(/[ \n\r]+$/, ""); // at the end
      retText = retText.replace(/ *[\n\r]+ */g, ", "); // in the middle

      return retText;
   }
}

/**
 * PFCSV Format 3
 * Example: pfcsv.#20101031
 * BookingDate;BookingText;Details;ValutaDate;DebitAmount;CreditAmount;Balance
 * 31.10.2010;FÜR DAS ONLINE-SET SEPTEMBER XXXX;;31.10.2010;;0.00;5831.73
 * 29.10.2010;E-FINANCE XXX;1;29.10.2010;-45.00;;5831.73
 * 29.10.2010;E-FINANCE XXX;1;29.10.2010;-131.55;;
 * Example: pfcsv.#20131231
 * Buchung;Buchungstext;Details;Valuta;Belastung;Gutschrift;Saldo;Kategorie;Familienmitglied;Kommentar
 * "31.12.2013";"ZINSABSCHLUSS 010113 - 311213";"";"31.12.2013";"-0.15";"";"2549.30";"";"";""
 * "24.12.2013";"KAUF/DIENSTLEISTUNG
 * VOM 23.12.2013
 * KARTEN NR. 82770597
 * CUCINA PERO AG
 * WƒDENSWIL";"1";"23.12.2013";"-124.00";"";"2549.45";"";"";""
**/
function PFCSVFormat3() {

   this.colDate = 0;
   this.colDescr = 1;
   this.colDateValuta = 3;
   this.colDebit = 4;
   this.colCredit = 5;
   this.colBalance = 6;
   this.colComment = 9;

   /** 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) ||
            transaction.length === (this.colComment + 1))
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDate].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g) &&
            transaction[this.colDate].length === 10)
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDateValuta].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/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]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g) && transaction[this.colDate].length == 10 &&
            transaction[this.colDateValuta].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/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], 'dd-mm-yyyy'));
      mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDateValuta], 'dd-mm-yyyy'));
      mappedLine.push(""); // Doc is empty for now
      var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
      mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
      var amount = element[this.colCredit].replace(/-/g, ''); //remove minus sign
      mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
      amount = element[this.colDebit].replace(/-/g, ''); //remove minus sign
      mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));

      return mappedLine;
   }
}


/**
 * PFCSV Format 2
 * Example: pfcsv.#private20090401
 * Example: pfcsv.#private20090401
 * Data	Testo d'avviso	Accredito	Addebito	Data della valuta	Saldo	
 * 20090401	/t ACQUISTO/SERVIZIO DEL XX.XX.XXXX  CARTA N. XXX	/t99.9	/t20090331	/t			/t
 * 20090331	/t ORDINE DEBIT DIRECT NUMERO CLIENTE XXX			/t85.9	/t20090331	/t7881.35	/t
 * 20090330	/t ACQUISTO/SERVIZIO DEL XX.XX.XXXX  CARTA N. XXX	/t43	/t20090328	/t7967.25	/t
 *
 *
**/
function PFCSVFormat2() {

   this.colDate = 0;
   this.colDescr = 1;
   this.colCredit = 2;
   this.colDebit = 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 + 2))
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDate].match(/[0-9]{6}/g)
            && transaction[this.colDate].length === 8)
            formatMatched = true;

         if (formatMatched && transaction[this.colDateValuta].match(/[0-9]{6}/g) &&
            transaction[this.colDateValuta].length == 8)
            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 == 8 &&
            transaction[this.colDateValuta].match(/[0-9]+/g) && transaction[this.colDateValuta].length == 8)
            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(element[this.colDate]);
      mappedLine.push(element[this.colDateValuta]);
      mappedLine.push(""); // Doc is empty for now
      var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ' '); //remove white spaces
      mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
      mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colCredit]));
      mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colDebit]));

      return mappedLine;
   }
}


/**
 * PFCSV Format 1
 * Example: pfcsv.#20030903-B
 * Example: pfcsv.#20121101-B
 * Example: pfcsv.#20160707
 * Data;Descrizione della transazione;Accreditamento;Debito;Valuta;Saldo
 * 31.08.2003;Saldo;;;;50078.40
 * 01.09.2003;"YELLOWNET SAMMELAUFTRAG NR. X,YELLOWNET NUMMER XXXXXX";;-28.60;01.09.2003;50049.80
 * 01.09.2003;"AUFTRAG DEBIT DIRECT,AUFTRAGSNUMMER X,KUNDENNUMMER XXXX";26.80;;01.09.2003;50076.60
**/

function PFCSVFormat1() {

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

   this.dateFormat = 'dd-mm-yyyy';
   this.decimalSeparator = '.';


   /** 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) || transaction.length === (this.colBalance + 2))
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.)[0-9]{2}(\.)[0-9]{2}/g)) {
            this.dateFormat = 'dd.mm.yy';
            formatMatched = true;
         } else if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.|-)[0-9]{2}(\.|-)[0-9]{4}/g)) {
            formatMatched = true;
         } else if (formatMatched && transaction[this.colDate].match(/[0-9]{4}(\.|-)[0-9]{2}(\.|-)[0-9]{2}/g)) {
            formatMatched = true;
            this.dateFormat = 'yyyy-mm-dd';
         } else {
            formatMatched = false;
         }

         if (formatMatched && transaction[this.colDateValuta].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g))
            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\.]{3}/g) && transaction[this.colDateValuta].match(/[0-9\.]{3}/g))
            transactionsToImport.push(this.mapTransaction(transaction));
      }

      // Sort rows by date
      transactionsToImport = this.sort(transactionsToImport);

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


   /** Sort transactions by date */
   this.sort = function (transactions) {
      if (transactions.length <= 0)
         return transactions;
      var i = 0;
      var previousDate = transactions[0][this.colDate];
      while (i < transactions.length) {
         var date = transactions[i][this.colDate];
         if (previousDate.length > 0 && previousDate > date)
            return transactions.reverse();
         else if (previousDate.length > 0 && previousDate < date)
            return transactions;
         i++;
      }
      return transactions;
   }

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

      mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
      mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDateValuta], this.dateFormat));
      mappedLine.push(""); // Doc is empty for now
      var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
      mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
      var amount = element[this.colCredit].replace(/\+/g, ''); //remove plus sign
      mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, this.decimalSeparator));
      amount = element[this.colDebit].replace(/-/g, ''); //remove minus sign
      mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, this.decimalSeparator));

      return mappedLine;
   }
}

/**
 * The function findSeparator is used to find the field separator.
 */
function findSeparator(string) {

   var commaCount = 0;
   var semicolonCount = 0;
   var tabCount = 0;

   for (var i = 0; i < 1000 && i < string.length; i++) {
      var c = string[i];
      if (c === ',')
         commaCount++;
      else if (c === ';')
         semicolonCount++;
      else if (c === '\t')
         tabCount++;
   }

   if (tabCount > commaCount && tabCount > semicolonCount) {
      return '\t';
   }
   else if (semicolonCount > commaCount) {
      return ';';
   }

   return ',';
}

/**
 * PFCSV Smart Business Format 1
 * Example: pfcsv.#20180220-SBU
 * "client_name";"paid_date";"paid_amount"
 * "Schaub Thomas";"21.02.2018";"100.00"
 * "Prins Carla";"20.02.2018";"150.00"
 * "Mario Wlotzka";"15.02.2018";"960.00"
**/

function PFCSVFormatSBU1() {

   this.colDate = 1;
   this.colDescr = 0;
   this.colCredit = 2;

   this.dateFormat = 'dd.mm.yyyy';
   this.decimalSeparator = '.';

   /** 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.colCredit + 1))
            formatMatched = true;
         else
            formatMatched = false;

         if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.|-)[0-9]{2}(\.|-)[0-9]{4}/g)) {
            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 (var i = 0; i < transactions.length; i++) {
         var transaction = transactions[i];
         if (transaction.length < this.colCredit)
            continue;
         if (transaction[this.colDate].match(/[0-9\.]{3}/g))
            transactionsToImport.push(this.mapTransaction(transaction));
      }

      // Sort rows by date
      transactionsToImport = this.sort(transactionsToImport);

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


   /** Sort transactions by date */
   this.sort = function (transactions) {
      if (transactions.length <= 0)
         return transactions;
      var i = 0;
      var previousDate = transactions[0][this.colDate];
      while (i < transactions.length) {
         var date = transactions[i][this.colDate];
         if (previousDate.length > 0 && previousDate > date)
            return transactions.reverse();
         else if (previousDate.length > 0 && previousDate < date)
            return transactions;
         i++;
      }
      return transactions;
   }

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

      mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
      mappedLine.push("");
      mappedLine.push("");
      mappedLine.push(element[this.colDescr]);
      mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colCredit], this.decimalSeparator));
      mappedLine.push("");

      return mappedLine;
   }
}

/**
 * The function findSeparator is used to find the field separator.
 */
function findSeparator(string) {

   var commaCount = 0;
   var semicolonCount = 0;
   var tabCount = 0;

   for (var i = 0; i < 1000 && i < string.length; i++) {
      var c = string[i];
      if (c === ',')
         commaCount++;
      else if (c === ';')
         semicolonCount++;
      else if (c === '\t')
         tabCount++;
   }

   if (tabCount > commaCount && tabCount > semicolonCount) {
      return '\t';
   }
   else if (semicolonCount > commaCount) {
      return ';';
   }

   return ',';
}

/*
* class ImportUtilities
* Contains methods that can be shared by extensions for importing bank data
*/

var ImportUtilities = class ImportUtilities {
    constructor(banDocument) {
        this.banDocument = banDocument;
        if (this.banDocument === undefined)
            this.banDocument = Banana.document;
    }

    //The purpose of this function is to convert all the data into a format supported by Banana
    convertToBananaFormat(intermediaryData) {
        var columnTitles = [];

        //Create titles only for fields not starting with "_"
        for (var name in intermediaryData[0]) {
            if (name.substring(0, 1) !== "_") {
                columnTitles.push(name);
            }
        }
        //Function call Banana.Converter.objectArrayToCsv() to create a CSV with new data just converted
        var convertedCsv = Banana.Converter.objectArrayToCsv(columnTitles, intermediaryData, "\t");

        return convertedCsv;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertCsvToIntermediaryData(inData, convertionParam) {
        var form = [];
        var intermediaryData = [];
        //Add the header if present 
        if (convertionParam.header) {
            inData = convertionParam.header + inData;
        }

        //Read the CSV file and create an array with the data
        var csvFile = Banana.Converter.csvToArray(inData, convertionParam.separator, convertionParam.textDelim);

        //Variables used to save the columns titles and the rows values
        var columns = this.getHeaderData(csvFile, convertionParam.headerLineStart); //array
        var rows = this.getRowData(csvFile, convertionParam.dataLineStart); //array of array

        //Load the form with data taken from the array. Create objects
        this.loadForm(form, columns, rows);

        //Create the new CSV file with converted data
        var convertedRow;
        //For each row of the form, we call the rowConverter() function and we save the converted data
        for (var i = 0; i < form.length; i++) {
            convertedRow = convertionParam.rowConverter(form[i]);
            intermediaryData.push(convertedRow);
        }

        //Return the converted CSV data into the Banana document table
        return intermediaryData;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertHtmlToIntermediaryData(inData, convertionParam) {
        var form = [];
        var intermediaryData = [];

        //Read the HTML file and create an array with the data
        var htmlFile = [];
        var htmlRows = inData.match(/<tr[^>]*>.*?<\/tr>/gi);
        for (var rowNr = 0; rowNr < htmlRows.length; rowNr++) {
            var htmlRow = [];
            var htmlFields = htmlRows[rowNr].match(/<t(h|d)[^>]*>.*?<\/t(h|d)>/gi);
            for (var fieldNr = 0; fieldNr < htmlFields.length; fieldNr++) {
                var htmlFieldRe = />(.*)</g.exec(htmlFields[fieldNr]);
                htmlRow.push(htmlFieldRe.length > 1 ? htmlFieldRe[1] : "");
            }
            htmlFile.push(htmlRow);
        }

        //Variables used to save the columns titles and the rows values
        var columns = this.getHeaderData(htmlFile, convertionParam.headerLineStart); //array
        var rows = this.getRowData(htmlFile, convertionParam.dataLineStart); //array of array

        //Convert header names
        for (var i = 0; i < columns.length; i++) {
            var convertedHeader = columns[i];
            convertedHeader = convertedHeader.toLowerCase();
            convertedHeader = convertedHeader.replace(" ", "_");
            var indexOfHeader = columns.indexOf(convertedHeader);
            if (indexOfHeader >= 0 && indexOfHeader < i) { // Header alreay exist
                //Avoid headers with same name adding an incremental index
                var newIndex = 2;
                while (columns.indexOf(convertedHeader + newIndex.toString()) !== -1 && newIndex < 99)
                    newIndex++;
                convertedHeader = convertedHeader + newIndex.toString()
            }
            columns[i] = convertedHeader;
        }

        // Banana.console.log(JSON.stringify(columns, null, "   "));

        //Load the form with data taken from the array. Create objects
        this.loadForm(form, columns, rows);

        //Create the new CSV file with converted data
        var convertedRow;
        //For each row of the form, we call the rowConverter() function and we save the converted data
        for (var i = 0; i < form.length; i++) {
            convertedRow = convertionParam.rowConverter(form[i]);
            intermediaryData.push(convertedRow);
        }

        //Return the converted CSV data into the Banana document table
        return intermediaryData;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertToIntermediaryData(inData, convertionParam) {
        if (convertionParam.format === "html") {
            return this.convertHtmlToIntermediaryData(inData, convertionParam);
        } else {
            return this.convertCsvToIntermediaryData(inData, convertionParam);
        }
    }

    //The purpose of this function is to return all the titles of the columns
    getHeaderData(csvFile, startLineNumber) {
        if (!startLineNumber) {
            startLineNumber = 0;
        }
        var headerData = csvFile[startLineNumber];
        for (var i = 0; i < headerData.length; i++) {

            headerData[i] = headerData[i].trim();

            if (!headerData[i]) {
                headerData[i] = i;
            }

            //Avoid duplicate headers
            var headerPos = headerData.indexOf(headerData[i]);
            if (headerPos >= 0 && headerPos < i) { // Header already exist
                var postfixIndex = 2;
                while (headerData.indexOf(headerData[i] + postfixIndex.toString()) !== -1 && postfixIndex <= 99)
                    postfixIndex++; // Append an incremental index
                headerData[i] = headerData[i] + postfixIndex.toString()
            }

        }
        return headerData;
    }

    getLang() {

        var lang = 'en';
        if (this.banDocument)
            lang = this.banDocument.locale;
        else if (Banana.application.locale)
            lang = Banana.application.locale;
        if (lang.length > 2)
            lang = lang.substr(0, 2);
        return lang;
    }
    
    //The purpose of this function is to return all the data of the rows
    getRowData(csvFile, startLineNumber) {
        if (!startLineNumber) {
            startLineNumber = 1;
        }
        var rowData = [];
        for (var i = startLineNumber; i < csvFile.length; i++) {
            rowData.push(csvFile[i]);
        }
        return rowData;
    }

    //The purpose of this function is to load all the data (titles of the columns and rows) and create a list of objects.
    //Each object represents a row of the csv file
    loadForm(form, columns, rows) {
        for (var j = 0; j < rows.length; j++) {
            var obj = {};
            for (var i = 0; i < columns.length; i++) {
                obj[columns[i]] = rows[j][i];
            }
            form.push(obj);
        }
    }

    // The purpose of this function is to sort the data
    sortData(intermediaryData, convertionParam) {
        if (convertionParam.sortColums && convertionParam.sortColums.length) {
            intermediaryData.sort(
                function (row1, row2) {
                    for (var i = 0; i < convertionParam.sortColums.length; i++) {
                        var columnName = convertionParam.sortColums[i];
                        if (row1[columnName] > row2[columnName])
                            return 1;
                        else if (row1[columnName] < row2[columnName])
                            return -1;
                    }
                    return 0;
                });

            if (convertionParam.sortDescending)
                intermediaryData.reverse();
        }

        return intermediaryData;
    }

    verifyBananaPlusVersion() {
        if (!this.banDocument)
            return false;

        var BAN_VERSION_MIN = "10.0";

        // supported Version
        if (Banana.compareVersion && Banana.compareVersion(Banana.application.version, BAN_VERSION_MIN) >= 0) {
            return true;
        }

        // not supported version
        var lang = this.getLang();
        var msg = "This extension requires Banana Accounting+";
        this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
        return false;
    }

    //Check if the version of Banana Accounting is compatible with this class
    verifyBananaAdvancedVersion() {
        if (!this.banDocument)
            return false;

        if (!Banana.application.license || Banana.application.license.licenseType !== "advanced") {
            var lang = this.getLang();
            var msg = "This extension requires Banana Accounting+ Advanced";
            this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
            return false;
        }

        return true;
    }

    getErrorMessage(errorId, lang) {
        if (!lang)
            lang = 'en';
        switch (errorId) {
            case "ID_ERR_FORMAT_UNKNOWN":
                 if (lang == 'it')
                    return "Formato del file *.csv non riconosciuto";
                 else if (lang == 'fr')
                    return "Format de fichier *.csv non reconnu";
                 else if (lang == 'de')
                    return "Unerkanntes *.csv-Dateiformat";
                 else
                    return "Unrecognised *.csv file format";
        }
        return '';
     }

    getLang() {
        var lang = 'en';
        if (Banana.application.locale)
            lang = Banana.application.locale;
        if (lang.length > 2)
            lang = lang.substring(0, 2);
        return lang;
     }

    getUnknownFormatError(){
        let errId = "ID_ERR_FORMAT_UNKNOWN"; //error
        let lang = this.getLang();
        let msg = this.getErrorMessage(errId, lang);
        Banana.document.addMessage(msg, errId);
    }
}

Input data - csv_postfinance_example_format1_CreditCard_20230929.csv

Input text passed to the exec() function.


Kartenkonto:;0000 1234 5467 7654
Karte:;XXXX XXXX XXXX 1111 PostFinance Visa Business Card
Datum;Buchungsdetails;Gutschrift in CHF;Lastschrift in CHF
2023-08-24;"Tankstelle";;-94.70
2023-08-21;"Tankstelle";;-114.05
2023-08-10;"6131 STORNO JAHRESPREIS";80.00;

Disclaimer:
Der Dokumentinhalt wurde durch Filtereinstellungen der Kund:innen generiert. PostFinance ist für den Inhalt und die Vollständigkeit nicht verantwortlich.

Output data - csv_postfinance_example_format1_CreditCard_20230929.tsv

Output text returned by the exec() function.


Date\tDoc\tDescription\tIncome\tExpenses
2023-08-10\t\t6131 Storno Jahrespreis\t80.00\t
2023-08-21\t\tTankstelle\t\t114.05
2023-08-24\t\tTankstelle\t\t94.70

Input data - csv_postfinance_example_format2_20090401.csv

Input text passed to the exec() function.


Data	Testo d'avviso	Accredito	Addebito	Data della valuta	Saldo	
20090401	ACQUISTO/SERVIZIO DES 31.03.2009  UNDAE W. 47655099 AURSENTATREPE-8250 LENSIT C. A LENSIT		71.8	66667620		
20090331	ORDINE PERIS NAVITI HORTIS PERUCUM 109631  PRIETE NABO ARITATIS OS HORTIS EO VOCTENENDIT: 000000000001107565001543539 PARTERIT EVENT PRINNAM DES 26.03.2009 W. REPEM UNDAE 1107 5650 0154 3539		85.9	20090331	7881.35	
20090330	ACQUISTO/SERVIZIO DES 28.03.2009  UNDAE W. 47655099 ACUTION ASTO RODO POST		30	11025450	1423.50	
20090330	ACQUISTO/SERVIZIO DES 28.03.2009  UNDAE W. 47655099 ACUTION ASTO RODO POST		47	11025450		
20090330	ACQUISTO/SERVIZIO DES 29.03.2009  UNDAE W. 47655099 DICITIMPTIONSI EST POST		81	78665580		
20090330	ACQUISTO/SERVIZIO DES 27.03.2009  UNDAE W. 47655099 MARTURRIUNGI EO VOLANEREM ANT LENSIT		58	55580477		
20090326	OPERAZIONE VENT CHRARDUCI ALIUNDI DES 25.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS		556.5	28683443	5582.50	
20090325	GIRATA EX RET MODEST 8465 DITIUNIS: /SECT SECTIA ET: WNQGR7Q513MBKEVW ACINSA OS OS NOSSUS ET: 79777957 090325CH81880950 IUSUPARTERIPUS: 690505731	4570.25		20090325	8388.85	
20090322	OPERAZIONE VENT CHRARDUCI ALIUNDI DES 21.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS		1606.8	13330272	8568.5	
20090319	PRELIEVO DE HABERUNT DES 19.03.2009  UNDAE W. 47655099 ANTINDIS: PUTULO PUTULO		636	68200818	2224.0	
20090316	OPERAZIONE VENT CHRARDUCI ALIUNDI DES 13.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS		8233	86784617	8577.0	
20090316	ACQUISTO/SERVIZIO DES 14.03.2009  UNDAE W. 47655099 IURRIDICUM PUPILODEANTE ET DIO QUA VOCARURA		32	68227047		
20090316	ACQUISTO/SERVIZIO DES 12.03.2009  UNDAE W. 47655099 SOMULTUSA OS PLUCET		30	60283240		
20090316	ACQUISTO/SERVIZIO DES 14.03.2009  UNDAE W. 47655099 FRUCEM B. VOCARURA B. VOCARURA		22	68227047		
20090311	OPERAZIONE VENT CHRARDUCI ALIUNDI DES 10.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS		4018	23204372	35808.0	
20090309	ACQUISTO/SERVIZIO DES 07.03.2009  UNDAE W. 47655099 PLEX-8801 B. VOCARURA QUA VOCARURA		50.34	17246434	83387.0	
20090309	ACQUISTO/SERVIZIO DES 07.03.2009  UNDAE W. 47655099 FRUCEM B. VOCARURA B. VOCARURA		31.34	17246434		
20090309	ACQUISTO/SERVIZIO DES 07.03.2009  UNDAE W. 47655099 AURSENTATREPE-7652 QUA VENTABO QUA VOCARURA		318	17246434		
20090309	ACQUISTO/SERVIZIO DES 05.03.2009  UNDAE W. 47655099 SERE-NUNT, COCTUUNT-POSTERTE PUTULO PHRATUS 52.00		52	20090305		
20090306	PRELIEVO DE HABERUNT DES 06.03.2009  UNDAE W. 47655099 ANTINDIS: LENSIT CUNGUA OBABO LENSIT		472	32032225	83803.1	
20090306	ACQUISTO/SERVIZIO DES 05.03.2009  UNDAE W. 47655099 FRUCEM FORGENS FORGENS		7.1	24280353		
20090304	ACQUISTO/SERVIZIO DES 03.03.2009  UNDAE W. 47655099 POSTO BONCH BONCH		50.50	84402074	84572.7	
20090303	ORDINE PERIS NAVITI HORTIS PERUCUM 109631  PRIETE NABO ARITATIS OS HORTIS EO VOCTENENDIT: 000000000001107565001543539 PARTERIT EVENT PRINNAM DES 26.02.2009 W. REPEM UNDAE 1107 5650 0154 3539		92.4	20090303	16062.65	


Output data - csv_postfinance_example_format2_20090401.tsv

Output text returned by the exec() function.


Date\tDateValue\tDoc\tDescription\tIncome\tExpenses
20090303\t20090303\t\tOrdine Peris Naviti Hortis Perucum 109631 Priete Nabo Aritatis Os Hortis Eo Voctenendit: 000000000001107565001543539 Parterit Event Prinnam Des 26.02.2009 W. Repem Undae 1107 5650 0154 3539\t\t92.4
20090304\t84402074\t\tAcquisto/servizio Des 03.03.2009 Undae W. 47655099 Posto Bonch Bonch\t\t50.50
20090306\t24280353\t\tAcquisto/servizio Des 05.03.2009 Undae W. 47655099 Frucem Forgens Forgens\t\t7.1
20090306\t32032225\t\tPrelievo De Haberunt Des 06.03.2009 Undae W. 47655099 Antindis: Lensit Cungua Obabo Lensit\t\t472
20090309\t20090305\t\tAcquisto/servizio Des 05.03.2009 Undae W. 47655099 Sere-Nunt, Coctuunt-Posterte Putulo Phratus 52.00\t\t52
20090309\t17246434\t\tAcquisto/servizio Des 07.03.2009 Undae W. 47655099 Aursentatrepe-7652 Qua Ventabo Qua Vocarura\t\t318
20090309\t17246434\t\tAcquisto/servizio Des 07.03.2009 Undae W. 47655099 Frucem B. Vocarura B. Vocarura\t\t31.34
20090309\t17246434\t\tAcquisto/servizio Des 07.03.2009 Undae W. 47655099 Plex-8801 B. Vocarura Qua Vocarura\t\t50.34
20090311\t23204372\t\tOperazione Vent Chrarduci Aliundi Des 10.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t4018
20090316\t68227047\t\tAcquisto/servizio Des 14.03.2009 Undae W. 47655099 Frucem B. Vocarura B. Vocarura\t\t22
20090316\t60283240\t\tAcquisto/servizio Des 12.03.2009 Undae W. 47655099 Somultusa Os Plucet\t\t30
20090316\t68227047\t\tAcquisto/servizio Des 14.03.2009 Undae W. 47655099 Iurridicum Pupilodeante Et Dio Qua Vocarura\t\t32
20090316\t86784617\t\tOperazione Vent Chrarduci Aliundi Des 13.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t8233
20090319\t68200818\t\tPrelievo De Haberunt Des 19.03.2009 Undae W. 47655099 Antindis: Putulo Putulo\t\t636
20090322\t13330272\t\tOperazione Vent Chrarduci Aliundi Des 21.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t1606.8
20090325\t20090325\t\tGirata Ex Ret Modest 8465 Ditiunis: /sect Sectia Et: WNQGR7Q513MBKEVW Acinsa Os Os Nossus Et: 79777957 090325CH81880950 Iusuparteripus: 690505731\t4570.25\t
20090326\t28683443\t\tOperazione Vent Chrarduci Aliundi Des 25.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t556.5
20090330\t55580477\t\tAcquisto/servizio Des 27.03.2009 Undae W. 47655099 Marturriungi Eo Volanerem Ant Lensit\t\t58
20090330\t78665580\t\tAcquisto/servizio Des 29.03.2009 Undae W. 47655099 Dicitimptionsi Est Post\t\t81
20090330\t11025450\t\tAcquisto/servizio Des 28.03.2009 Undae W. 47655099 Acution Asto Rodo Post\t\t47
20090330\t11025450\t\tAcquisto/servizio Des 28.03.2009 Undae W. 47655099 Acution Asto Rodo Post\t\t30
20090331\t20090331\t\tOrdine Peris Naviti Hortis Perucum 109631 Priete Nabo Aritatis Os Hortis Eo Voctenendit: 000000000001107565001543539 Parterit Event Prinnam Des 26.03.2009 W. Repem Undae 1107 5650 0154 3539\t\t85.9
20090401\t66667620\t\tAcquisto/servizio Des 31.03.2009 Undae W. 47655099 Aursentatrepe-8250 Lensit C. A Lensit\t\t71.8

Input data - csv_postfinance_example_format3_20101031.csv

Input text passed to the exec() function.


BookingDate;BookingText;Details;ValutaDate;DebitAmount;CreditAmount;Balance
31.10.2010;HS IGE HYACTA-XXX ANDUXEMUM 4682 - VIVIVIOFIG DEAGNA INE PAM 3555.77;;31.10.2010;;0.00;5831.73
29.10.2010;W-GANGUNT 75-13080-1 - MAGNUNANS;2;29.10.2010;-45.00;;5831.73
29.10.2010;W-GANGUNT 75-88716-7 - DIUSQUAM OSTO;2;29.10.2010;-131.55;;
29.10.2010;W-GANGUNT 75-73547-1 - MULTIUNT DE. TABICUSTIO VI;2;29.10.2010;-110.00;;
29.10.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;29.10.2010;-119.65;;
29.10.2010;W-GANGUNT 75-88560-5 - MULTIUNT DE. TABICUSTIO VI;2;29.10.2010;-140.00;;
29.10.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;29.10.2010;-249.30;;
29.10.2010;W-GANGUNT 75-28743-1 - MATE - TRUNT UT;2;29.10.2010;-122.85;;
29.10.2010;W-GANGUNT 75-45521-6 - SUBSTO BY;2;29.10.2010;-115.50;;
29.10.2010;W-GANGUNT 75-36815-7 - VERANEST;2;29.10.2010;-300.00;;
29.10.2010;W-GANGUNT 75-2055-5 - DE.SOLONE EXACTIANAVIT UT;2;29.10.2010;-1400.00;;
25.10.2010;SOLO DEM RIDEO 28-2-2 - TEM UT;2;25.10.2010;;110.00;8565.58
19.10.2010;REGERUMERI - XXX INE. RIDEO XXX 10.63.4682;2;19.10.2010;;500.00;8455.58
13.10.2010;OVUM/SEDIVISCATIRIS - XXX 36.63.4682;2;11.10.2010;-39.95;;7955.58
06.10.2010;SOLO DEM RIDEO 28-2-2 - TEM UT;2;06.10.2010;;392.00;7995.53
06.10.2010;REGERUMERI - XXX INE. RIDEO XXX 30.63.4682;2;06.10.2010;;900.00;
04.10.2010;OVUM/SEDIVISCATIRIS - XXX 25.63.4682;2;02.10.2010;-30.15;;6703.53
30.09.2010;HS IGE HYACTA-XXX LODUCI 4682 - VIVIVIOFIG HABULAC INE PAM 3555.77;;30.09.2010;-3.00;;6733.68
30.09.2010;W-GANGUNT 75-27675-7 - TESTANES (PROXITA) UT, AGNA;2;30.09.2010;-36.25;;
30.09.2010;W-GANGUNT 75-34663-7 - SENIURA ASTIURUNGOBTIS UT;2;30.09.2010;-58.85;;
30.09.2010;W-GANGUNT 75-88560-5 - MULTIUNT DE. TABICUSTIO VI;2;30.09.2010;-140.00;;
30.09.2010;W-GANGUNT 75-7670-4 - PRIATE CHABIT;2;30.09.2010;-141.50;;
30.09.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;30.09.2010;-203.95;;
30.09.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;30.09.2010;-249.30;;
30.09.2010;W-GANGUNT 75-36815-7 - VERANEST;2;30.09.2010;-300.00;;
30.09.2010;W-GANGUNT 75-2055-5 - DE.SOLONE EXACTIANAVIT UT;2;30.09.2010;-1400.00;;
30.09.2010;SOLO DEM HYACTA-RAL 700 - HUMELUMENTEM:;2;30.09.2010;;400.00;
28.09.2010;REGERUMERI XXX INE. RIDEO - XXX 51.28.4682;2;28.09.2010;;600.00;8866.53
28.09.2010;OVUM/SEDIVISCATIRIS - XXX 58.28.4682;2;24.09.2010;-49.00;;
27.09.2010;W-GANGUNT 24315 - PENDIRESTIFFICTRANSOLUNIUS DIO;2;27.09.2010;-282.60;;8315.53
20.09.2010;REGERUMERI XXX INE. RIDEO - XXX 58.28.4682;2;20.09.2010;;500.00;8598.13
17.09.2010;REGERUMERI XXX INE. RIDEO - XXX 75.28.4682;2;17.09.2010;;700.00;8098.13
14.09.2010;OVUM/SEDIVISCATIRIS - XXX 63.28.4682;2;10.09.2010;-44.95;;7398.13
03.09.2010;REGERUMERI XXX INE. RIDEO - XXX 63.28.4682;2;03.09.2010;;1200.00;7443.08
02.09.2010;OVUM/SEDIVISCATIRIS - XXX 28.28.4682;2;31.08.2010;-42.75;;6243.08
01.09.2010;REGERUMERI XXX INE. RIDEO - XXX 75.28.4682;2;01.09.2010;;800.00;6285.83

Output data - csv_postfinance_example_format3_20101031.tsv

Output text returned by the exec() function.


Date\tDateValue\tDoc\tDescription\tIncome\tExpenses
2010-09-01\t2010-09-01\t\tRegerumeri Xxx Ine. Rideo - Xxx 75.28.4682\t800.00\t
2010-09-02\t2010-08-31\t\tOvum/sediviscatiris - Xxx 28.28.4682\t\t42.75
2010-09-03\t2010-09-03\t\tRegerumeri Xxx Ine. Rideo - Xxx 63.28.4682\t1200.00\t
2010-09-14\t2010-09-10\t\tOvum/sediviscatiris - Xxx 63.28.4682\t\t44.95
2010-09-17\t2010-09-17\t\tRegerumeri Xxx Ine. Rideo - Xxx 75.28.4682\t700.00\t
2010-09-20\t2010-09-20\t\tRegerumeri Xxx Ine. Rideo - Xxx 58.28.4682\t500.00\t
2010-09-27\t2010-09-27\t\tW-Gangunt 24315 - Pendirestiffictransolunius Dio\t\t282.60
2010-09-28\t2010-09-24\t\tOvum/sediviscatiris - Xxx 58.28.4682\t\t49.00
2010-09-28\t2010-09-28\t\tRegerumeri Xxx Ine. Rideo - Xxx 51.28.4682\t600.00\t
2010-09-30\t2010-09-30\t\tSolo Dem Hyacta-Ral 700 - Humelumentem:\t400.00\t
2010-09-30\t2010-09-30\t\tW-Gangunt 75-2055-5 - De.Solone Exactianavit Ut\t\t1400.00
2010-09-30\t2010-09-30\t\tW-Gangunt 75-36815-7 - Veranest\t\t300.00
2010-09-30\t2010-09-30\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t249.30
2010-09-30\t2010-09-30\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t203.95
2010-09-30\t2010-09-30\t\tW-Gangunt 75-7670-4 - Priate Chabit\t\t141.50
2010-09-30\t2010-09-30\t\tW-Gangunt 75-88560-5 - Multiunt De. Tabicustio Vi\t\t140.00
2010-09-30\t2010-09-30\t\tW-Gangunt 75-34663-7 - Seniura Astiurungobtis Ut\t\t58.85
2010-09-30\t2010-09-30\t\tW-Gangunt 75-27675-7 - Testanes (Proxita) Ut, Agna\t\t36.25
2010-09-30\t2010-09-30\t\tH�s Ige Hyacta-Xxx Loduci 4682 - Vivivio�fig Habulac Ine Pam 3555.77\t\t3.00
2010-10-04\t2010-10-02\t\tOvum/sediviscatiris - Xxx 25.63.4682\t\t30.15
2010-10-06\t2010-10-06\t\tRegerumeri - Xxx Ine. Rideo Xxx 30.63.4682\t900.00\t
2010-10-06\t2010-10-06\t\tSolo Dem Rideo 28-2-2 - Tem Ut\t392.00\t
2010-10-13\t2010-10-11\t\tOvum/sediviscatiris - Xxx 36.63.4682\t\t39.95
2010-10-19\t2010-10-19\t\tRegerumeri - Xxx Ine. Rideo Xxx 10.63.4682\t500.00\t
2010-10-25\t2010-10-25\t\tSolo Dem Rideo 28-2-2 - Tem Ut\t110.00\t
2010-10-29\t2010-10-29\t\tW-Gangunt 75-2055-5 - De.Solone Exactianavit Ut\t\t1400.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-36815-7 - Veranest\t\t300.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-45521-6 - Substo By\t\t115.50
2010-10-29\t2010-10-29\t\tW-Gangunt 75-28743-1 - Mate - Trunt Ut\t\t122.85
2010-10-29\t2010-10-29\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t249.30
2010-10-29\t2010-10-29\t\tW-Gangunt 75-88560-5 - Multiunt De. Tabicustio Vi\t\t140.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t119.65
2010-10-29\t2010-10-29\t\tW-Gangunt 75-73547-1 - Multiunt De. Tabicustio Vi\t\t110.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-88716-7 - Diusquam Osto\t\t131.55
2010-10-29\t2010-10-29\t\tW-Gangunt 75-13080-1 - Magnunans\t\t45.00
2010-10-31\t2010-10-31\t\tH�s Ige Hyacta-Xxx Anduxemum 4682 - Vivivio�fig De�agna Ine Pam 3555.77\t0.00\t

Import transactions example 3

Ubs (Switzerland) import transactions with multiple csv formats


// Banana Accounting Extension Javascript
// @id = ch.banana.switzerland.import.ubs.tutorial
// @api = 1.0
// @pubdate = 2024-06-24
// @publisher = Banana.ch SA
// @description = UBS - Import account statement .csv (Banana+ Advanced)
// @description.en = UBS - Import account statement .csv (Banana+ Advanced)
// @description.de = UBS - Bewegungen importieren .csv (Banana+ Advanced)
// @description.fr = UBS - Importer mouvements .csv (Banana+ Advanced)
// @description.it = UBS - Importa movimenti .csv (Banana+ Advanced)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @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 (*.*)
// @timeout = -1



/*
* class ImportUtilities
* Contains methods that can be shared by extensions for importing bank data
*/
var ImportUtilities = class ImportUtilities {
    constructor(banDocument) {
        this.banDocument = banDocument;
        if (this.banDocument === undefined)
            this.banDocument = Banana.document;
    }

    //The purpose of this function is to convert all the data into a format supported by Banana
    convertToBananaFormat(intermediaryData) {
        var columnTitles = [];

        //Create titles only for fields not starting with "_"
        for (var name in intermediaryData[0]) {
            if (name.substring(0, 1) !== "_") {
                columnTitles.push(name);
            }
        }
        //Function call Banana.Converter.objectArrayToCsv() to create a CSV with new data just converted
        var convertedCsv = Banana.Converter.objectArrayToCsv(columnTitles, intermediaryData, "\t");

        return convertedCsv;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertCsvToIntermediaryData(inData, convertionParam) {
        var form = [];
        var intermediaryData = [];
        //Add the header if present 
        if (convertionParam.header) {
            inData = convertionParam.header + inData;
        }

        //Read the CSV file and create an array with the data
        var csvFile = Banana.Converter.csvToArray(inData, convertionParam.separator, convertionParam.textDelim);

        //Variables used to save the columns titles and the rows values
        var columns = this.getHeaderData(csvFile, convertionParam.headerLineStart); //array
        var rows = this.getRowData(csvFile, convertionParam.dataLineStart); //array of array

        //Load the form with data taken from the array. Create objects
        this.loadForm(form, columns, rows);

        //Create the new CSV file with converted data
        var convertedRow;
        //For each row of the form, we call the rowConverter() function and we save the converted data
        for (var i = 0; i < form.length; i++) {
            convertedRow = convertionParam.rowConverter(form[i]);
            intermediaryData.push(convertedRow);
        }

        //Return the converted CSV data into the Banana document table
        return intermediaryData;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertHtmlToIntermediaryData(inData, convertionParam) {
        var form = [];
        var intermediaryData = [];

        //Read the HTML file and create an array with the data
        var htmlFile = [];
        var htmlRows = inData.match(/<tr[^>]*>.*?<\/tr>/gi);
        for (var rowNr = 0; rowNr < htmlRows.length; rowNr++) {
            var htmlRow = [];
            var htmlFields = htmlRows[rowNr].match(/<t(h|d)[^>]*>.*?<\/t(h|d)>/gi);
            for (var fieldNr = 0; fieldNr < htmlFields.length; fieldNr++) {
                var htmlFieldRe = />(.*)</g.exec(htmlFields[fieldNr]);
                htmlRow.push(htmlFieldRe.length > 1 ? htmlFieldRe[1] : "");
            }
            htmlFile.push(htmlRow);
        }

        //Variables used to save the columns titles and the rows values
        var columns = this.getHeaderData(htmlFile, convertionParam.headerLineStart); //array
        var rows = this.getRowData(htmlFile, convertionParam.dataLineStart); //array of array

        //Convert header names
        for (var i = 0; i < columns.length; i++) {
            var convertedHeader = columns[i];
            convertedHeader = convertedHeader.toLowerCase();
            convertedHeader = convertedHeader.replace(" ", "_");
            var indexOfHeader = columns.indexOf(convertedHeader);
            if (indexOfHeader >= 0 && indexOfHeader < i) { // Header alreay exist
                //Avoid headers with same name adding an incremental index
                var newIndex = 2;
                while (columns.indexOf(convertedHeader + newIndex.toString()) !== -1 && newIndex < 99)
                    newIndex++;
                convertedHeader = convertedHeader + newIndex.toString()
            }
            columns[i] = convertedHeader;
        }

        // Banana.console.log(JSON.stringify(columns, null, "   "));

        //Load the form with data taken from the array. Create objects
        this.loadForm(form, columns, rows);

        //Create the new CSV file with converted data
        var convertedRow;
        //For each row of the form, we call the rowConverter() function and we save the converted data
        for (var i = 0; i < form.length; i++) {
            convertedRow = convertionParam.rowConverter(form[i]);
            intermediaryData.push(convertedRow);
        }

        //Return the converted CSV data into the Banana document table
        return intermediaryData;
    }

    // Convert to an array of objects where each object property is the banana columnNameXml
    convertToIntermediaryData(inData, convertionParam) {
        if (convertionParam.format === "html") {
            return this.convertHtmlToIntermediaryData(inData, convertionParam);
        } else {
            return this.convertCsvToIntermediaryData(inData, convertionParam);
        }
    }

    //The purpose of this function is to return all the titles of the columns
    getHeaderData(csvFile, startLineNumber) {
        if (!startLineNumber) {
            startLineNumber = 0;
        }
        var headerData = csvFile[startLineNumber];
        for (var i = 0; i < headerData.length; i++) {

            headerData[i] = headerData[i].trim();

            if (!headerData[i]) {
                headerData[i] = i;
            }

            //Avoid duplicate headers
            var headerPos = headerData.indexOf(headerData[i]);
            if (headerPos >= 0 && headerPos < i) { // Header already exist
                var postfixIndex = 2;
                while (headerData.indexOf(headerData[i] + postfixIndex.toString()) !== -1 && postfixIndex <= 99)
                    postfixIndex++; // Append an incremental index
                headerData[i] = headerData[i] + postfixIndex.toString()
            }

        }
        return headerData;
    }

    getLang() {

        var lang = 'en';
        if (this.banDocument)
            lang = this.banDocument.locale;
        else if (Banana.application.locale)
            lang = Banana.application.locale;
        if (lang.length > 2)
            lang = lang.substr(0, 2);
        return lang;
    }
    
    //The purpose of this function is to return all the data of the rows
    getRowData(csvFile, startLineNumber) {
        if (!startLineNumber) {
            startLineNumber = 1;
        }
        var rowData = [];
        for (var i = startLineNumber; i < csvFile.length; i++) {
            rowData.push(csvFile[i]);
        }
        return rowData;
    }

    //The purpose of this function is to load all the data (titles of the columns and rows) and create a list of objects.
    //Each object represents a row of the csv file
    loadForm(form, columns, rows) {
        for (var j = 0; j < rows.length; j++) {
            var obj = {};
            for (var i = 0; i < columns.length; i++) {
                obj[columns[i]] = rows[j][i];
            }
            form.push(obj);
        }
    }

    // The purpose of this function is to sort the data
    sortData(intermediaryData, convertionParam) {
        if (convertionParam.sortColums && convertionParam.sortColums.length) {
            intermediaryData.sort(
                function (row1, row2) {
                    for (var i = 0; i < convertionParam.sortColums.length; i++) {
                        var columnName = convertionParam.sortColums[i];
                        if (row1[columnName] > row2[columnName])
                            return 1;
                        else if (row1[columnName] < row2[columnName])
                            return -1;
                    }
                    return 0;
                });

            if (convertionParam.sortDescending)
                intermediaryData.reverse();
        }

        return intermediaryData;
    }

    verifyBananaPlusVersion() {
        if (!this.banDocument)
            return false;

        var BAN_VERSION_MIN = "10.0";

        // supported Version
        if (Banana.compareVersion && Banana.compareVersion(Banana.application.version, BAN_VERSION_MIN) >= 0) {
            return true;
        }

        // not supported version
        var lang = this.getLang();
        var msg = "This extension requires Banana Accounting+";
        this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
        return false;
    }

    //Check if the version of Banana Accounting is compatible with this class
    verifyBananaAdvancedVersion() {
        if (!this.banDocument)
            return false;

        if (!Banana.application.license || Banana.application.license.licenseType !== "advanced") {
            var lang = this.getLang();
            var msg = "This extension requires Banana Accounting+ Advanced";
            this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
            return false;
        }

        return true;
    }

    getErrorMessage(errorId, lang) {
        if (!lang)
            lang = 'en';
        switch (errorId) {
            case "ID_ERR_FORMAT_UNKNOWN":
                 if (lang == 'it')
                    return "Formato del file *.csv non riconosciuto";
                 else if (lang == 'fr')
                    return "Format de fichier *.csv non reconnu";
                 else if (lang == 'de')
                    return "Unerkanntes *.csv-Dateiformat";
                 else
                    return "Unrecognised *.csv file format";
        }
        return '';
     }

    getLang() {
        var lang = 'en';
        if (Banana.application.locale)
            lang = Banana.application.locale;
        if (lang.length > 2)
            lang = lang.substring(0, 2);
        return lang;
     }

    getUnknownFormatError(){
        let errId = "ID_ERR_FORMAT_UNKNOWN"; //error
        let lang = this.getLang();
        let msg = this.getErrorMessage(errId, lang);
        Banana.document.addMessage(msg, errId);
    }
}



/**
 * Parse the data and return the data to be imported as a tab separated file.
 */
function exec(inData, isTest) {
    if (!inData) return "";

    /** in the new version of format 3 (June 2024) we could have the situation where descriptions have
     * trhee pairs of quotes: """description text""". This situation cause problems
     * when the API Banana.Converter.csvToArray() read the content, the text is ignored. This happen
     * when the text description contains a semicolon ';'.
     * For the time being, we 'clean' the text of these quotes pairs by replacing them with normal quotes pairs.
     *  */
    inData = inData.replace(/"""/g, '"');

    var importUtilities = new ImportUtilities(Banana.document);

    if (isTest !== true && !importUtilities.verifyBananaAdvancedVersion())
        return "";

    convertionParam = defineConversionParam(inData);
    //Add the header if present
    if (convertionParam.header) {
        inData = convertionParam.header + inData;
    }
    let transactions = Banana.Converter.csvToArray(
        inData,
        convertionParam.separator,
        convertionParam.textDelim
    );

    // Format Credit Card
    var formatCc1Ubs = new UBSFormatCc1();
    if (formatCc1Ubs.match(transactions)) {
        transactions = formatCc1Ubs.convert(transactions);
        Banana.console.log(Banana.Converter.arrayToTsv(transactions));
        return Banana.Converter.arrayToTsv(transactions);
    }

    // Format 1
    var format1Ubs = new UBSFormat1();
    if (format1Ubs.match(transactions)) {
        transactions = format1Ubs.convert(transactions);
        Banana.console.log(Banana.Converter.arrayToTsv(transactions));
        return Banana.Converter.arrayToTsv(transactions);
    }

    // Format 2
    var format2Ubs = new UBSFormat2();
    if (format2Ubs.match(transactions)) {
        transactions = format2Ubs.convert(transactions, convertionParam);
        transactions = format2Ubs.postProcessIntermediaryData(transactions);
        Banana.console.log(Banana.Converter.arrayToTsv(transactions));
        return Banana.Converter.arrayToTsv(transactions);
    }

    // Format 3
    var format3Ubs = new UBSFormat3();
    if (format3Ubs.match(transactions)) {
        transactions = format3Ubs.convert(transactions, convertionParam);
        transactions = format3Ubs.postProcessIntermediaryData(transactions);
        Banana.console.log(Banana.Converter.arrayToTsv(transactions));
        return Banana.Converter.arrayToTsv(transactions);
    }

    importUtilities.getUnknownFormatError();

    return "";
}

/**
 * UBS Format 1
 *
 * Valuation date;Banking relationship;Portfolio;Product;IBAN;Ccy.;Date from;Date to;Description;Trade date;Booking date;Value date;Description 1;Description 2;Description 3;Transaction no.;Exchange rate in the original amount in settlement currency;Individual amount;Debit;Credit;Balance
 * 07.07.17;0240 00254061;;0240 00254061.01C;CH62 0024 4240 2340 6101 C;CHF;01.02.17;30.06.17;UBS Business Current Account;30.06.17;30.06.17;30.06.17;e-banking Order;BWS - CHENEVAL, BWS GERMANLINGUA,  DE DE DE 80331     MUECHEN,  INVOICE : M25252,  STD: ALICE CHENEVAL;;ZD81181TI0690091;;;3'416.82;;206'149.34
 * 07.07.17;0240 00254061;;0240 00254061.01C;CH62 0024 4240 2340 6101 C;CHF;01.02.17;30.06.17;UBS Business Current Account;30.06.17;30.06.17;30.06.17;Amount paid;;;ZD81181TI0690091;1.113334;3069;;;
 */
function UBSFormat1() {
    // Index of columns in csv file
    this.colCount = 21;

    this.colCurrency = 5;
    this.colDate = 10;
    this.colDateValuta = 11;
    this.colDescr1 = 12;
    this.colDescr2 = 13;
    this.colDescr3 = 14;
    this.colDescr4 = 15;
    this.colExchRate = -5;
    this.colDetails = -4;
    this.colDebit = -3;
    this.colCredit = -2;
    this.colBalance = -1;

    /** 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;
            /* array should have all columns */
            if (transaction.length >= this.colCount) formatMatched = true;
            else formatMatched = false;

            if (formatMatched &&
                transaction[this.colDate] &&
                transaction[this.colDate].match(/^[0-9]+\.[0-9]+\.[0-9]+$/))
                formatMatched = true;
            else formatMatched = false;

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

            if (formatMatched) return true;
        }

        return false;
    };

    /** Convert the transaction to the format to be imported */
    this.convert = function (transactions) {
        this.colCount =
            transactions.length > 1 ? transactions[0].length : this.colCount;

        transactions = this.convertTransactions(transactions);

        if (transactions.length > 1) {
            //Sort by date
            if (transactions[0][0] > transactions[transactions.length - 1][0]) {
                //Sort transactions
                transactions = transactions.reverse();
            }
        }

        var header = [
            [
                "Date",
                "DateValue",
                "Doc",
                "ExternalReference",
                "Description",
                "Income",
                "Expenses",
                "IsDetail",
            ],
        ];
        return header.concat(transactions);
    };

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

        /** Complete, filter and map rows */
        var lastCompleteTransaction = null;

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

            if (transaction.length <= this.colDate ||
                !transaction[this.colDate].match(/[0-9\.]+/g))
                continue;

            if (transaction.length >= this.colCount + this.colBalance &&
                (transaction[this.colCount + this.colDebit] && (transaction[this.colCount + this.colDebit].length > 0) ||
                    (transaction[this.colCount + this.colCredit] && transaction[this.colCount + this.colCredit].length > 0) ||
                    (transaction[this.colCount + this.colBalance] && transaction[this.colCount + this.colBalance].length > 0))) {
                // Is a complete row
                if (lastCompleteTransaction) {
                    mappedTransaction = this.mapTransaction(lastCompleteTransaction);
                    mappedTransaction.push("");
                    transactionsToImport.push(mappedTransaction);
                    lastCompleteTransaction = null;
                }
                lastCompleteTransaction = transaction;
            } else if (transaction.length >= this.colCount + this.colDetails &&
                transaction[this.colCount + this.colDetails].length > 0) {
                // Is a detail row
                if (transaction[this.colCount + this.colExchRate].match(/[0-9]+\.[0-9]+/g)) {
                    // Is a multicurrency detail row
                    if (lastCompleteTransaction) {
                        mappedTransaction = this.mapTransaction(lastCompleteTransaction);
                        mappedTransaction.push("");
                        transactionsToImport.push(mappedTransaction);
                        lastCompleteTransaction = null;
                    }
                } else {
                    // Is a normal detail row
                    if (transaction[this.colDescr1] === "Cashpayment charges deducted by post" &&
                        lastCompleteTransaction && lastCompleteTransaction[this.colDescr1] === "Incomings UBS BESR Quick") {
                        // Post charges are contabilised at the end of the period, skip this row
                        if (lastCompleteTransaction !== null) {
                            mappedTransaction = this.mapTransaction(lastCompleteTransaction);
                            mappedTransaction.push("");
                            transactionsToImport.push(mappedTransaction);
                            lastCompleteTransaction = null;
                        }
                    } else {
                        if (lastCompleteTransaction !== null) {
                            mappedTransaction = this.mapTransaction(lastCompleteTransaction);
                            mappedTransaction.push("S");
                            transactionsToImport.push(mappedTransaction);
                            lastCompleteTransaction = null;
                        }
                        mappedTransaction = this.mapDetailTransaction(transaction);
                        mappedTransaction.push("D");
                        transactionsToImport.push(mappedTransaction);
                    }
                }
            }
        }

        if (lastCompleteTransaction !== null) {
            transactionsToImport.push(this.mapTransaction(lastCompleteTransaction));
        }

        return transactionsToImport;
    };

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

        if (
            element[this.colDate] === null ||
            element[this.colDescr1] === null ||
            element[this.colDescr2] === null ||
            element[this.colDescr3] === null ||
            element[this.colDescr4] === null ||
            element[element.length + this.colCredit] === null ||
            element[element.length + this.colDebit] === null
        ) {
            mappedLine.push("");
            mappedLine.push("");
            mappedLine.push("Error importing data");
            mappedLine.push("");
            mappedLine.push("");
            return mappedLine;
        }

        mappedLine.push(
            Banana.Converter.toInternalDateFormat(element[this.colDate], "dd.mm.yyyy")
        );
        mappedLine.push(
            Banana.Converter.toInternalDateFormat(
                element[this.colDateValuta],
                "dd.mm.yyyy"
            )
        );
        mappedLine.push("");
        mappedLine.push(element[this.colDescr4]); //transaction number.
        mappedLine.push(this.mapDescription(element));
        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[element.length + this.colCredit],
                "."
            )
        );
        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[element.length + this.colDebit],
                "."
            )
        );

        return mappedLine;
    };

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

        if (
            element[this.colDate] === null ||
            element[this.colDescr1] === null ||
            element[this.colDescr2] === null ||
            element[this.colDescr3] === null ||
            element[this.colDescr4] === null ||
            element[element.length + this.colCredit] === null ||
            element[element.length + this.colDebit] === null
        ) {
            mappedLine.push("");
            mappedLine.push("");
            mappedLine.push("");
            mappedLine.push("Error importing data");
            mappedLine.push("");
            mappedLine.push("");
            return mappedLine;
        }

        mappedLine.push(
            Banana.Converter.toInternalDateFormat(element[this.colDate], "dd.mm.yyyy")
        );
        mappedLine.push(
            Banana.Converter.toInternalDateFormat(
                element[this.colDateValuta],
                "dd.mm.yyyy"
            )
        );
        mappedLine.push("");
        mappedLine.push(element[this.colDescr4]);
        mappedLine.push(this.mapDescription(element));
        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[this.colCount + this.colDetails].replace(",", ""),
                "."
            )
        );
        mappedLine.push("");

        return mappedLine;
    };

    /**
     * Return the descrition for the requested line.
     */
    this.mapDescription = function (line) {
        var descr = line[this.colDescr1];
        if (line[this.colDescr2].length) descr += ", " + line[this.colDescr2];
        if (line[this.colDescr3].length) descr += ", " + line[this.colDescr3];

        descr = Banana.Converter.stringToCamelCase(descr);
        descr = descr.replace(/"/g, '\\"');
        return '"' + descr + '"';
    };
}

/**
 * UBS Format 2
 *
 * This new format (09.2022) use the import utilities class.
 * This format has no detail rows.
 *
 * Numero di conto:;0234 00103914.40;
 * IBAN:;CH29 0023 4234 1039 1440 G;
 * Dal:;2022-09-15;
 * Al:;2022-09-19;
 * Saldo iniziale:;1719.34;
 * Saldo finale:;631.07;
 * Valutazione in:;CHF;
 * Numero di transazioni in questo periodo:;13;
 *
 * Data dell'operazione;Ora dell'operazione;Data di registrazione;Data di valuta;Moneta;Importo della transazione;Addebito/Accredito;Saldo;N. di transazione;Descrizione1;Descrizione2;Descrizione3;Note a piè di pagina;
 * 2022-09-19;;2022-09-19;2022-09-19;CHF;-150.00;Addebito;600;123456TI1234567;"Versamento";"Ordine di pagamento via e-banking";;;
 * 2022-09-19;;2022-09-19;2022-09-19;CHF;-92.00;Addebito;692;2345678TI2345678;"Versamento";"Ordine di pagamento via e-banking";;;
 * 2022-09-19;;2022-09-19;2022-09-19;CHF;-10.00;Addebito;702;3456789TI3456789;"Versamento";"Ordine di pagamento via e-banking";;;
 * 2022-09-19;;2022-09-19;2022-09-19;CHF;-40.00;Addebito;742;4567890TI4567890;"Versamento";"Ordine di pagamento via e-banking";;;
 *
 */
var UBSFormat2 = class UBSFormat2 extends ImportUtilities {
    // Index of columns in csv file

    constructor(banDocument) {
        super(banDocument);
        this.colCount = 12;

        this.colDateOperation = 0;
        this.colDateValuta = 3;
        this.colAmount = 5;
        this.colOpType = 6;
        this.colTransNr = 8;
        this.colDescr1 = 9;
        this.colDescr2 = 10;
        this.colDescr3 = 11;

        //Index of columns in import format.
        this.newColDate = 0;
        this.newColDescription = 2;
        this.newColExpenses = 4;
    }

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

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

            var formatMatched = false;
            /* array should have all columns */
            if (transaction.length >= this.colCount) formatMatched = true;
            else formatMatched = false;

            if (
                formatMatched &&
                transaction[this.colDateOperation] &&
                transaction[this.colDateOperation].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
            )
                formatMatched = true;
            else formatMatched = false;

            if (
                formatMatched &&
                transaction[this.colDateValuta] &&
                transaction[this.colDateValuta].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
            )
                if (
                    formatMatched &&
                    transaction[this.colOpType] &&
                    transaction[this.colOpType].match(/[a-zA-Z]/)
                )
                    formatMatched = true;
                else formatMatched = false;

            if (formatMatched) return true;
        }

        return false;
    }

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

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

        /**
         * Sort rows by date
         * SPECIFY THE COLUMN TO USE FOR SORTING
         * If sortColums is empty the data are not sorted
         * */
        convertionParam.sortColums = [0, 2]; //0 = "Date" field position, 2 = "Description" field position.
        convertionParam.sortDescending = false;
        transactionsToImport = sort(transactionsToImport, convertionParam);

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

    mapTransaction(element) {
        var mappedLine = [];

        let dateText = "";
        let dateValueText = "";

        dateText = element[this.colDateOperation].substring(0, 10);
        dateValueText = element[this.colDateValuta].substring(0, 10);

        mappedLine.push(
            Banana.Converter.toInternalDateFormat(dateText, "yyyy-mm-dd")
        );
        mappedLine.push(
            Banana.Converter.toInternalDateFormat(dateValueText, "yyyy-mm-dd")
        );
        // wrap descr to bypass TipoFileImporta::IndovinaSeparatore problem
        mappedLine.push(
            element[this.colDescr1] +
            " " +
            element[this.colDescr2] +
            " " +
            element[this.colDescr3]
        );
        mappedLine.push(element[this.colTransNr]);

        if (element[this.colAmount].indexOf("-") == -1) {
            mappedLine.push("");
            mappedLine.push(
                Banana.Converter.toInternalNumberFormat(
                    element[this.colAmount],
                    this.decimalSeparator
                )
            );
        } else {
            mappedLine.push(
                Banana.Converter.toInternalNumberFormat(
                    element[this.colAmount],
                    this.decimalSeparator
                )
            );
            mappedLine.push("");
        }
        return mappedLine;
    }

    //The purpose of this function is to let the user specify how to convert the categories
    postProcessIntermediaryData(transactions) {
        let processesData = [];

        if (transactions.length < 1) return processesData;

        processesData = transactions;
        /** INSERT HERE THE LIST OF ACCOUNTS NAME AND THE CONVERSION NUMBER
         *   If the content of "Account" is the same of the text
         *   it will be replaced by the account number given */
        //Accounts conversion
        var accounts = {
            //...
        };

        /** INSERT HERE THE LIST OF CATEGORIES NAME AND THE CONVERSION NUMBER
         *   If the content of "ContraAccount" is the same of the text
         *   it will be replaced by the account number given */

        //Categories conversion
        var categories = {
            //...
        };

        //Apply the conversions
        for (var i = 1; i < processesData.length; i++) {
            var convertedData = processesData[i];
            //Invert values
            if (convertedData[this.newColExpenses]) {
                convertedData[this.newColExpenses] = Banana.SDecimal.invert(
                    convertedData[this.newColExpenses]
                );
            }
        }

        return processesData;
    }
};

/**
 * UBS Format 3
 *
 * This new format (08.11.2022) use the import utilities class.
 * This format has no detail rows.
 *
 * Numero di conto:;0234 00103914.40;
 * IBAN:;CH29 0023 4234 1039 1440 G;
 * Dal:;2022-09-15;
 * Al:;2022-09-19;
 * Saldo iniziale:;1719.34;
 * Saldo finale:;631.07;
 * Valutazione in:;CHF;
 * Numero di transazioni in questo periodo:;13;
 *
 * Data dell'operazione;Ora dell'operazione;Data di registrazione;Data di valuta;Moneta;Debit amount;Credit amount;Individual amount;Saldo;N. di transazione;Descrizione1;Descrizione2;Descrizione3;Note a piè di pagina;
 * 2022-11-07;;2022-11-07;2022-11-07;TEM;-99.80;;;2215.89;1770311TO2672955;"Meniunis (Suractae) VI,Maxi Habyssidedertis 6, 3542 Aerna";"Cepate in consolest tam g-possica";"Decilausa vi. NUS: 35 47463 30382 81016 85544 75378, Experi in consolest: 2213 / Osit: 37.57.84 - 62.57.84 / Mendimus audio at: 75.64.4848, Sologit vi. CAPH: JA18 6457 3522 2051 7571 2, Suisi: B-Possica TEM Suractae, Offereganga vi. 6068584LN1841647";;
 * 2022-11-07;;2022-11-07;2022-11-07;TEM;-52.10;;;2315.69;1670311TO2672937;"Meniunis (Suractae) VI,Maxi Habyssidedertis 6, 3542 Aerna";"Cepate in consolest tam g-possica";"Decilausa vi. NUS: 35 47463 30382 27465 62135 38521, Experi in consolest: 2213 / Osit: 37.80.84 - 16.80.84 / Mendimus audio at: 81.57.4848, Sologit vi. CAPH: JA18 6457 3522 2051 7571 2, Suisi: B-Possica TEM Suractae, Offereganga vi. 7510665VI0356053";;
 * 2022-11-07;17:10:46;;2022-11-07;TEM;-4.35;;;2367.79;9999311BN1710030;"CERA SEPTEMPTO,SEPTEMPTO";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 7740420TJ8353344";;
 * 2022-11-07;07:55:57;;2022-11-07;TEM;-2.70;;;2372.14;9999311BN0755924;"CERA SEPTEMPTO,SEPTEMPTO";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 3275420YO4320201";;
 * 2022-11-07;17:21:52;;2022-11-07;TEM;-2.30;;;2374.84;9999311BN1721198;"POR SALL. SED. MANTUMN PARATE,PARATE";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 7518748DV2785407";;
 * 2022-11-06;15:31:05;2022-11-07;2022-11-06;TEM;-6.80;;;2377.14;9930811BN5353554;"Parescro Tratiantro VI,6010 Recto";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 8740610YF3026752";;
 *
 */
var UBSFormat3 = class UBSFormat3 extends ImportUtilities {
    // Index of columns in *.csv file

    constructor(banDocument) {
        super(banDocument);
        //original file columns
        this.colCount = 13;

        this.colDateOperation = 0;
        this.colDateValuta = 3;
        this.colDebitAmt = 5;
        this.colCreditAmt = 6;
        this.colTransNr = 9;
        this.colDescr1 = 10;
        this.colDescr2 = 11;
        this.colDescr3 = 12;

        this.decimalSeparator = ".";

        //Index of columns in import format.
        this.newColDate = 0;
        this.newColDescription = 2;
        this.newColExpenses = 4;
    }

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

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

            var formatMatched = false;
            /* array should have all columns */
            if (transaction.length >= this.colCount) formatMatched = true;
            else formatMatched = false;

            if (
                formatMatched &&
                transaction[this.colDateOperation] &&
                transaction[this.colDateOperation].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
            )
                formatMatched = true;
            else formatMatched = false;

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

            if (
                (formatMatched && transaction[this.colDebitAmt]) ||
                transaction[this.colCreditAmt]
            )
                formatMatched = true;
            else formatMatched = false;

            if (formatMatched) return true;
        }

        return false;
    }

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

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

        /**
         * Sort rows by date
         * SPECIFY THE COLUMN TO USE FOR SORTING
         * If sortColums is empty the data are not sorted
         * */
        convertionParam.sortColums = [this.newColDate, this.newColDescription]; //0 = "Date" field position, 2 = "Description" field position.
        convertionParam.sortDescending = false;
        transactionsToImport = sort(transactionsToImport, convertionParam);

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

    mapTransaction(element) {
        var mappedLine = [];

        let dateText = "";
        let dateValueText = "";

        dateText = element[this.colDateOperation].substring(0, 10);
        dateValueText = element[this.colDateValuta].substring(0, 10);

        mappedLine.push(
            Banana.Converter.toInternalDateFormat(dateText, "yyyy-mm-dd")
        );
        mappedLine.push(
            Banana.Converter.toInternalDateFormat(dateValueText, "yyyy-mm-dd")
        );
        // wrap descr to bypass TipoFileImporta::IndovinaSeparatore problem
        mappedLine.push(element[this.colDescr1] + " " + element[this.colDescr2]);
        mappedLine.push(element[this.colTransNr]);

        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[this.colDebitAmt],
                this.decimalSeparator
            )
        );
        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[this.colCreditAmt],
                this.decimalSeparator
            )
        );

        return mappedLine;
    }

    //The purpose of this function is to let the user specify how to convert the categories
    postProcessIntermediaryData(transactions) {
        let processesData = [];

        if (transactions.length < 1) return processesData;

        processesData = transactions;
        /** INSERT HERE THE LIST OF ACCOUNTS NAME AND THE CONVERSION NUMBER
         *   If the content of "Account" is the same of the text
         *   it will be replaced by the account number given */
        //Accounts conversion
        var accounts = {
            //...
        };

        /** INSERT HERE THE LIST OF CATEGORIES NAME AND THE CONVERSION NUMBER
         *   If the content of "ContraAccount" is the same of the text
         *   it will be replaced by the account number given */

        //Categories conversion
        var categories = {
            //...
        };

        //Apply the conversions
        for (var i = 1; i < processesData.length; i++) {
            var convertedData = processesData[i];
            //Invert values
            if (convertedData[this.newColExpenses]) {
                convertedData[this.newColExpenses] = Banana.SDecimal.invert(
                    convertedData[this.newColExpenses]
                );
            }
        }

        return processesData;
    }
};

/**
 * UBS Credit Card Format 1
 *
 * sep=;
 * Numéro de compte;Numéro de carte;Titulaire de compte/carte;Date d'achat;Texte comptable;Secteur;Montant;Monnaie originale;Cours;Monnaie;Débit;Crédit;Ecriture
 * ZZZZ ZZZZ ZZZZ;;SIMON JEAN;19.10.2017;Report de solde;;;;;CHF;;0.00;
 * ZZZZ ZZZZ ZZZZ;XXXX XXXX XXXX XXXX;JEAN SIMON;16.11.2017;Cafe de Paris Geneve CHE;Restaurants, Bar;119.40;CHF;;;;;
 * ZZZZ ZZZZ ZZZZ;XXXX XXXX XXXX XXXX;JEAN SIMON;13.11.2017;www.banana.ch            LUGANO       CHE;Magasin d ordinateurs;189.00;CHF;;CHF;189.00;;15.11.2017
 */
function UBSFormatCc1() {
    // Index of columns in csv file
    this.colCount = 13;

    this.colCardNo = 2;
    this.colDateDoc = 3;
    this.colDescr = 4;
    this.colCurrency = -4;
    this.colDebit = -3;
    this.colCredit = -2;
    this.colDate = -1;

    /** 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;
            /* array should have all columns */
            if (transaction.length >= this.colCount) formatMatched = true;
            else formatMatched = false;

            if (
                formatMatched &&
                transaction[this.colCount + this.colDate] &&
                transaction[this.colCount + this.colDate].match(
                    /^[0-9]+\.[0-9]+\.[0-9]+$/
                )
            )
                formatMatched = true;
            else formatMatched = false;

            if (formatMatched) return true;
        }

        return false;
    };

    /** Convert the transaction to the format to be imported */
    this.convert = function (transactions) {
        this.colCount =
            transactions.length > 1 ? transactions[0].length : this.colCount;

        transactions = this.convertTransactions(transactions);

        if (transactions.length > 1) {
            //Sort by date
            if (transactions[0][0] > transactions[transactions.length - 1][0]) {
                //Sort transactions
                transactions = transactions.reverse();
            }
        }

        var header = [
            ["Date", "DateDocument", "Doc", "Description", "Income", "Expenses"],
        ];
        return header.concat(transactions);
    };

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

        /** Complete, filter and map rows */
        var lastCompleteTransaction = null;

        for (
            var i = 0; i < transactions.length; i++ // First row contains the header
        ) {
            var transaction = transactions[i];
            if (
                transaction.length <= this.colCount + this.colDate ||
                !transaction[transaction.length + this.colDate].match(
                    /^[0-9]+\.[0-9]+\.[0-9]+$/
                )
            )
                continue;
            var mappedTransaction = this.mapTransaction(transaction);
            transactionsToImport.push(mappedTransaction);
        }

        return transactionsToImport;
    };

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

        if (
            element[element.length + this.colDate] === null ||
            element[this.colDescr] === null ||
            element[element.length + this.colCredit] === null ||
            element[element.length + this.colDebit] === null
        ) {
            mappedLine.push("");
            mappedLine.push("");
            mappedLine.push("Error importing data");
            mappedLine.push("");
            mappedLine.push("");
            return mappedLine;
        }

        var descr;

        mappedLine.push(
            Banana.Converter.toInternalDateFormat(
                element[element.length + this.colDate],
                "dd.mm.yyyy"
            )
        );
        mappedLine.push("");
        mappedLine.push("");
        mappedLine.push(this.convertDescription(element[this.colDescr]));
        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[element.length + this.colCredit],
                "."
            )
        );
        mappedLine.push(
            Banana.Converter.toInternalNumberFormat(
                element[element.length + this.colDebit],
                "."
            )
        );

        return mappedLine;
    };

    this.convertDescription = function (text) {
        var convertedDescr = text.replace(/  +/g, " ");
        convertedDescr = convertedDescr.replace(/"/g, '\\"');
        return '"' + convertedDescr + '"'; // Banana.Converter.stringToCamelCase(convertedDescr);
    };
}

function defineConversionParam(inData) {
    var csvData = Banana.Converter.csvToArray(inData);
    var header = String(csvData[0]);
    var convertionParam = {};
    /** SPECIFY THE SEPARATOR AND THE TEXT DELIMITER USED IN THE CSV FILE */
    convertionParam.format = "csv"; // available formats are "csv", "html"
    //get text delimiter
    convertionParam.textDelim = '"';
    // get separator
    convertionParam.separator = findSeparator(inData);

    return convertionParam;
}

/** Sort transactions by date and description */
function sort(transactions, convertionParam) {
    if (
        transactions.length <= 0 ||
        !convertionParam.sortColums ||
        convertionParam.sortColums.length <= 0
    )
        return transactions;

    transactions.sort(function (row1, row2) {
        for (var i = 0; i < convertionParam.sortColums.length; i++) {
            var columnIndex = convertionParam.sortColums[i];
            if (row1[columnIndex] > row2[columnIndex]) return 1;
            else if (row1[columnIndex] < row2[columnIndex]) return -1;
        }
        return 0;
    });

    if (convertionParam.sortDescending) transactions.reverse();

    return transactions;
}

function findSeparator(string) {

    var commaCount = 0;
    var semicolonCount = 0;
    var tabCount = 0;

    for (var i = 0; i < 1000 && i < string.length; i++) {
        var c = string[i];
        if (c === ',')
            commaCount++;
        else if (c === ';')
            semicolonCount++;
        else if (c === '\t')
            tabCount++;
    }

    if (tabCount > commaCount && tabCount > semicolonCount) {
        return '\t';
    }
    else if (semicolonCount > commaCount) {
        return ';';
    }

    return ',';
}

Input data - csv_ubs_example_format1_20230905_03.csv

Input text passed to the exec() function.


Valuation date,Banking relationship,Portfolio,Product,IBAN,Ccy.,Date from,Date to,Description,Trade date,Booking date,Value date,Description 1,Description 2,Description 3,Transaction no.,Exchange rate in the original amount in settlement currency,Individual amount,Debit,Credit,Balance
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,Secitque dedra,4-12.2023 BVG monthly pa,"ingit, GROBAE COLO, AN DUONE 1017, 600457761231762504643213385, 1561140RG4305540",9930743LK8430013,,,557.50,,4'371.73
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,R-Audideo diambile,,,9930743LK8430013,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,Viscus lis at minitudunto congerate parto,,,9930743LK8430013,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,Coniunt secitque ellura,,,9930743LP7352170,,,1'469.95,,4'929.23
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,DORPUPRA VOLOCULUVIT,,,9930743LP7352170,,-1327.500000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,PLEGENT COR INE,AN SECIBURIA (ALIQUID) 4045,,9930743LP7352170,,-75.000000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,DITA PATUMERE DE,,,9930743LP7352170,,-67.450000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,30.08.2023,30.08.2023,30.08.2023,d-audideo Volury,OPUGAVIT XXX,"8820 WAEDENSWIL, AAPARTMENT RENTAL FEE SEP 23, Credit / debit advice",9702242TO3295927,,,,3'700.00,6'399.18
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,25.08.2023,25.08.2023,25.08.2023,Creta d-audideo Dedra,TUM,"AN FICA 66 8840, 404800036845402515030151356",5730237TI4790761,,,165.00,,2'699.18
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,25.08.2023,25.08.2023,25.08.2023,R-Audideo diambile,,,5730237TI4790761,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,25.08.2023,25.08.2023,25.08.2023,Viscus lis at minitudunto congerate parto,,,5730237TI4790761,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,24.08.2023,24.08.2023,24.08.2023,Creta d-audideo Dedra,RES,"AN VIT 3534, 803023367811405547285483243",2530236TI4589949,,,1'674.15,,2'864.18
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,24.08.2023,24.08.2023,24.08.2023,R-Audideo diambile,,,2530236TI4589949,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,24.08.2023,24.08.2023,24.08.2023,Viscus lis at minitudunto congerate parto,,,2530236TI4589949,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,21.08.2023,21.08.2023,21.08.2023,Creta d-audideo Dedra,VOLONCENDE FRUM ET,"AN REGRUMEA 5037, 246531233028703100220735546",5530233TI3956784,,,705.30,,4'538.33
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,21.08.2023,21.08.2023,21.08.2023,R-Audideo diambile,,,5530233TI3956784,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,21.08.2023,21.08.2023,21.08.2023,Viscus lis at minitudunto congerate parto,,,5530233TI3956784,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,02.08.2023,02.08.2023,02.08.2023,d-audideo Dedra,"VOLUNDO MANGUN, FLUVIDENT 8,","6318 WALCHWIL, CH, COMPENSATION FROM KSG",9930212TI9569993,,,5'000.00,,5'243.63
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,02.08.2023,02.08.2023,02.08.2023,R-Audideo diambile,,,9930212TI9569993,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,02.08.2023,02.08.2023,02.08.2023,Viscus lis at minitudunto congerate parto,,,9930212TI9569993,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Coniunt secitque ellura,,,9930712LP4015960,,,1'469.95,,10'243.63
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,DORPUPRA VOLOCULUVIT,,,9930712LP4015960,,-1327.500000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,PLEGENT COR INE,AN SECIBURIA (ALIQUID) 4045,,9930712LP4015960,,-75.000000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,DITA PATUMERE DE,,,9930712LP4015960,,-67.450000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Secitque dedra,4-12.2023 BVG MONTHLY PA,"INGIT, GROBAE COLO, AN DUONE 1017, 600457761231762504643213385",9930712LK6453313,,,557.50,,11'713.58
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,R-Audideo diambile,,,9930712LK6453313,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,9930712LK6453313,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Cebitom at perange alurem,,,BJ31418NJ8204828,,,0.00,,12'271.08
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,01.07.2023 - 31.07.2023,,,BJ31418NJ8204828,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Centuide PER-mantumn,,,BJ31418NJ8204828,,-0.200000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,BJ31418NJ8204828,,0.200000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,R-Audideo diambile,,,BJ31418NJ8204828,,-0.300000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,BJ31418NJ8204828,,0.300000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Secitque dedra diambile,,,BJ31418NJ8204828,,-0.900000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,BJ31418NJ8204828,,0.900000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,d-audideo Volury,OPUGAVIT XXX,"8820 WAEDENSWIL, AAPARTMENT RENTAL FEE AUG 23, Credit / debit advice",9902211TO9260785,,,,3'700.00,12'271.08
,,,,,,,,,,,,,,,,,,,,
Flassus cebitom,Quonent cebitom,,,,,,,,,,,,,,,,,,,
1111.123333,34545.000000007,,,,,,,,,,,,,,,,,,,

Output data - csv_ubs_example_format1_20230905_03.tsv

Output text returned by the exec() function.


Date\tDateValue\tDoc\tExternalReference\tDescription\tIncome\tExpenses\tIsDetail
2023-07-31\t2023-07-31\t\t9902211TO9260785\t\"D-Audideo Volury, Opugavit Xxx, 8820 Waedenswil, Aapartment Rental Fee Aug 23, Credit / Debit Advice\"\t3700.00\t
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Viscus Lis At Minitudunto Congerate Parto\"\t0.900000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Secitque Dedra Diambile\"\t-0.900000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Viscus Lis At Minitudunto Congerate Parto\"\t0.300000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"R-Audideo Diambile\"\t-0.300000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Viscus Lis At Minitudunto Congerate Parto\"\t0.200000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Centuide Per-Mantumn\"\t-0.200000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Cebitom At Perange Alurem\"\t\t0.00\tS
2023-07-31\t2023-07-31\t\t9930712LK6453313\t\"Secitque Dedra, 4-12.2023 Bvg Monthly Pa, Ingit, Grobae Colo, An Duone 1017, 600457761231762504643213385\"\t\t557.50\t
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Dita Patumere De\"\t-67.450000000\t\tD
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Plegent Cor Ine, An Seciburia (Aliquid) 4045\"\t-75.000000000\t\tD
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Dorpupra Voloculuvit\"\t-1327.500000000\t\tD
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Coniunt Secitque Ellura\"\t\t1469.95\tS
2023-08-02\t2023-08-02\t\t9930212TI9569993\t\"D-Audideo Dedra, Volundo Mangun, Fluvident 8,, 6318 Walchwil, Ch, Compensation From Ksg\"\t\t5000.00\t
2023-08-21\t2023-08-21\t\t5530233TI3956784\t\"Creta D-Audideo Dedra, Voloncende Frum Et, An Regrumea 5037, 246531233028703100220735546\"\t\t705.30\t
2023-08-24\t2023-08-24\t\t2530236TI4589949\t\"Creta D-Audideo Dedra, Res, An Vit 3534, 803023367811405547285483243\"\t\t1674.15\t
2023-08-25\t2023-08-25\t\t5730237TI4790761\t\"Creta D-Audideo Dedra, Tum, An Fica 66 8840, 404800036845402515030151356\"\t\t165.00\t
2023-08-30\t2023-08-30\t\t9702242TO3295927\t\"D-Audideo Volury, Opugavit Xxx, 8820 Waedenswil, Aapartment Rental Fee Sep 23, Credit / Debit Advice\"\t3700.00\t\t
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Dita Patumere De\"\t-67.450000000\t\tD
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Plegent Cor Ine, An Seciburia (Aliquid) 4045\"\t-75.000000000\t\tD
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Dorpupra Voloculuvit\"\t-1327.500000000\t\tD
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Coniunt Secitque Ellura\"\t\t1469.95\tS
2023-08-31\t2023-08-31\t\t9930743LK8430013\t\"Secitque Dedra, 4-12.2023 Bvg Monthly Pa, Ingit, Grobae Colo, An Duone 1017, 600457761231762504643213385, 1561140RG4305540\"\t\t557.50\t

Input data - csv_ubs_example_format2_de_20220928.csv

Input text passed to the exec() function.


Kontonummer:;12345678910;
IBAN:;CH12345678910;
Von:;2022-09-26;
Bis:;2022-09-28;
Anfangssaldo:;1000.00;
Schlusssaldo:;1441.21;
Bewertet in:;CHF;
Anzahl Transaktionen in diesem Zeitraum:;10;

Abschlussdatum;Abschlusszeit;Buchungsdatum;Valutadatum;Währung;Transaktionsbetrag;Belastung/Gutschrift;Saldo;Transaktions-Nr.;Beschreibung1;Beschreibung2;Beschreibung3;Fussnoten;
2022-09-28;16:08:11;;2022-09-28;CHF;100.00;Gutschrift;1100.00;12345678910;"Gutschrift";"Einzahlung Bancomat";;;
2022-09-28;12:48:24;;2022-09-28;CHF;-20.00;Belastung;1000.00;12345678910;"FILIALE";"123456-0 07/24, Bezug Bancomat";;;
2022-09-27;13:32:48;2022-09-29;2022-09-27;CHF;-5.35;Belastung;1005.35;12345678910;"Lidl Viganello";"123456-0 07/24, Zahlung Debitkarte";;;
2022-09-27;16:39:42;2022-09-28;2022-09-27;CHF;-2.30;Belastung;1007.85;12345678910;"TPL Piaz. Mol. Billett L";"123456-0 07/24, Zahlung Debitkarte";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-135.00;Belastung;1142.85;12345678910;"Zahlung ";"Zahlung  e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-38.01;Belastung;1180.86;12345678910;"Zahlung ";"e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-35.00;Belastung;1215.86;12345678910;"Zahlung";"Zahlung";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-31.75;Belastung;1306.21;12345678910;"Zahlung ";"e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-58.60;Belastung;1364.81;12345678910;"Sunrise UPC Sagl";"e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-76.40;Belastung;1441.21;12345678910;"Salt Mobile SA";"e-banking-Vergutungsauftrag";;;

Output data - csv_ubs_example_format2_de_20220928.tsv

Output text returned by the exec() function.


Date\tDateValue\tDescription\tExternalReference\tExpenses\tIncome
2022-09-26\t2022-09-26\tSalt Mobile SA e-banking-Vergutungsauftrag \t12345678910\t76.40\t
2022-09-26\t2022-09-26\tSunrise UPC Sagl e-banking-Vergutungsauftrag \t12345678910\t58.60\t
2022-09-26\t2022-09-26\tZahlung  Zahlung  e-banking-Vergutungsauftrag \t12345678910\t135.00\t
2022-09-26\t2022-09-26\tZahlung  e-banking-Vergutungsauftrag \t12345678910\t31.75\t
2022-09-26\t2022-09-26\tZahlung  e-banking-Vergutungsauftrag \t12345678910\t38.01\t
2022-09-26\t2022-09-26\tZahlung Zahlung \t12345678910\t35.00\t
2022-09-27\t2022-09-27\tLidl Viganello 123456-0 07/24, Zahlung Debitkarte \t12345678910\t5.35\t
2022-09-27\t2022-09-27\tTPL Piaz. Mol. Billett L 123456-0 07/24, Zahlung Debitkarte \t12345678910\t2.30\t
2022-09-28\t2022-09-28\tFILIALE 123456-0 07/24, Bezug Bancomat \t12345678910\t20.00\t
2022-09-28\t2022-09-28\tGutschrift Einzahlung Bancomat \t12345678910\t\t100.00

Input data - csv_ubs_example_format3_it_triplequotes_20240621.csv

Input text passed to the exec() function.


Numero di conto:;0000 00000800.00;;;;;;;;;;;;;
IBAN:;CH00 0000 0000 0000 0000 N;;;;;;;;;;;;;
Dal:;;;;;;;;;;;;;;
Al:;;;;;;;;;;;;;;
Saldo iniziale:;1117.58;;;;;;;;;;;;;
Saldo finale:;22.97;;;;;;;;;;;;;
Valutazione in:;CHF;;;;;;;;;;;;;
Numero di transazioni in questo periodo:;6;;;;;;;;;;;;;
Data dell'operazione;Ora dell'operazione;Data di registrazione;Data di valuta;Moneta;Addebito;Accredito;Importo singolo;Saldo;N. di transazione;Descrizione1;Descrizione2;Descrizione3;Note a piè di pagina;
2024-06-19;12:48:15;;2024-06-19;CHF;-20.00;;;22.97;11111111;"""FILIALE;VIGANELLO""";"""11111111-0 07/24; Prelevamento Bancomat""";No di transazioni: 111111;;
2024-06-19;;2024-06-19;2024-06-19;CHF;-450.00;;;42.97;11111112;"""Descrizione1""";"""Descrizione2; Descrizione2""";"""Descrizione3""";;
2024-06-17;17:19:57;2024-06-19;2024-06-17;CHF;-15.40;;;492.97;11111113;"""Descrizione1;6962 Viganello""";"""11111111-0 07/24; Pagamento carta di debito""";No di transazioni: 11111112334;;
2024-06-17;17:31:13;2024-06-18;2024-06-17;CHF;-5.20;;;508.37;11111114;"""Descrizione1;6900 Lugano""";"""11111111-0 07/24; Pagamento carta di debito""";No di transazioni: 11234534553;;
2024-06-18;;2024-06-18;2024-06-18;CHF;;200.00;;513.57;11111115;"""Descrizione1;6900 Lugano""";Accredito;No di transazioni: 12678345643;;
2024-05-31;17:23:44;2024-06-03;2024-05-31;CHF;-20.00;;;1117.58;11111116;BR Lugano;"""111111111-0 07/24; Prelevamento Bancomat""";"""Spese: Prelevamento di contanti al Bancomat di banche terze/Postomat in Svizzera; No di transazioni: 1268923683""";;

Output data - csv_ubs_example_format3_it_triplequotes_20240621.tsv

Output text returned by the exec() function.


Date\tDateValue\tDescription\tExternalReference\tExpenses\tIncome
2024-05-31\t2024-05-31\tBR Lugano 111111111-0 07/24; Prelevamento Bancomat\t11111116\t20.00\t
2024-06-17\t2024-06-17\tDescrizione1;6900 Lugano 11111111-0 07/24; Pagamento carta di debito\t11111114\t5.20\t
2024-06-17\t2024-06-17\tDescrizione1;6962 Viganello 11111111-0 07/24; Pagamento carta di debito\t11111113\t15.40\t
2024-06-18\t2024-06-18\tDescrizione1;6900 Lugano Accredito\t11111115\t\t200.00
2024-06-19\t2024-06-19\tDescrizione1 Descrizione2; Descrizione2\t11111112\t450.00\t
2024-06-19\t2024-06-19\tFILIALE;VIGANELLO 11111111-0 07/24; Prelevamento Bancomat\t11111111\t20.00\t

Export

Export Tutorial3 javascript codes, csv and tsv to HTML


// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exporthtmljavascriptcodestutorial3.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes, csv and tsv of the Tutorial 3 examples in HTML
// @task = export.file
// @doctype = *.*
// @docproperties = 
// @timeout = -1
// @exportfiletype = html
// @visibility = never

//Main function
function exec() {

    //Check if we are on an opened document
    if (!Banana.document) {
        return;
    }

    //Take the table Documents
    var documents = Banana.document.table('Documents');

    //We check if the table Documents exists, then we can take all the codes 
    //If the table Documents doesn't exists, then we stop the script execution
    if (!documents) {
        return;
    } else {

        //Variable to save the html text
        var htmlText = '';

        htmlText += '<p>On this page we show some examples of transactions import extensions.</p>\n';
        htmlText += '<p>Each example consists of:</p>\n';
        htmlText += '<ul>\n';
        htmlText += '<li>The complete <strong>JavaScript code of the import extension</strong>.</li>\n';
        htmlText += '<li>The <strong>.CSV</strong> files in different formats with the transactions data to be imported.<br>Some examples contains <strong>anonymized data</strong>, the description text has no meanings. </li>\n';
        htmlText += '<li>The output in <strong>.TSV</strong> format (Tabulator Separated Values) of the import extension, which contains the transformed csv data in the “transactions.simple” format used to import the transactions in Banana Accounting.</li>\n';
        htmlText += '</ul>\n';

        //Get all the tutorial javascript codes
        htmlText = getJavascriptCode(htmlText, documents);
    }

    return htmlText;
}

//Function that gets the javascript code of each tutorial example and save it into the htmlText.
function getJavascriptCode(htmlText, documents) {

    //Read row by row the table Documents
    var len = documents.rowCount;
    for (var i = 0; i < len; i++) {

        //Create a variable for the row
        var tRow = documents.row(i);

        //We get some values
        var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
        var section = tRow.value("Section"); // the section column used to define the title type
        var id = tRow.value("RowId"); //the id of the example
        var description = tRow.value("Description"); //the description of the example
        var attachments = tRow.value("Attachments"); //the javascript code of the example
        attachments = attachments.replace(/</g,'<'); // replace the < symbol
        attachments = attachments.replace(/>/g,'>'); // replace the > symbol

        //For tilte h2 we consider only rows with section h2
        if (section && section === "h2") {
            htmlText += '<h2>' + description + '</h2>\n';
        }
        
        //For js codes we consider only rows with section h3, id, description and attachment
        else if (section && section === "h3" && id && description && attachments) {
            htmlText += '<h3>' + description + '</h3>\n';
            htmlText += '<pre><code class="language-javascript">\n';
            htmlText += '// Banana Accounting Extension Javascript' + '\n';
            htmlText += attachments;
            htmlText += '\n';
            htmlText += '</code></pre>\n';
            htmlText += getCsvAndTsv(id, documents); // adds csv and tsv
        }
    }

    //Returns the html text which contains titles, javascript codes and csv examples
    return htmlText;
}

//Function that gets the Csv with the bank movements and the Tsv (tabulator separated values).
//The Tsv contains the Csv data after being processed by the import extension.
//The Tsv has a specific format used by Banana Accounting to import transactions.
function getCsvAndTsv(jsCodeId, documents) {
    
    //Variable to save the csv and tsv (tabulator separated values)
    var text = '';

    //Remove '.js' from the jsCodeId string, we use the id number to get the tsv
    jsCodeId = jsCodeId.replace(/\.js$/, "");

    //Read row by row the table Documents
    var len = documents.rowCount;
    for (var i = 0; i < len; i++) {

        //Create a variable for the row
        var tRow = documents.row(i);

        //We get some values
        var section = tRow.value("Section"); // the section column used to define the title type
        var id = tRow.value("RowId"); //the id of the example
        var description = tRow.value("Description"); //the description of the example
        var attachments = tRow.value("Attachments"); //the csv/tsv text of the example

        //For the csv/tsv text we consider only rows with section h4, id that starts with the same id of the import extension, description and attachment
        if (section === "h4" && id.startsWith(jsCodeId) && description && attachments) {

            var strtype = '';
            var strIntro = '';
            if (id.indexOf(".csv") > -1) {
               strtype = "Input data - ";
               strIntro ="Input text passed to the exec() function.";
            } else if (id.indexOf(".tsv") > -1) {
               strtype = "Output data - ";
               strIntro ="Output text returned by the exec() function.";
            }

            text += '<h4>' + strtype + description + '</h4>\n';
            text += '<p>'+ strIntro + '</p>\n';
            text += '<pre><code class="language-plaintext">\n';
            text += attachments;
            text += '\n';
            text += '</code></pre>\n';
        }
    }
    return text;
}

Help us improve the documentation

We welcome feedback on how to improve this page.

Tell us what theme needs a better explanation or how to clarify a topic.

Share this article: Twitter | Facebook | LinkedIn | Email