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;
}