Parameterizable embedded import CSV extension

This guide explains how to create your own embedded import CSV extension, starting from the Banana Accounting parametrizable import template.

For more information see the import as "transactions.simple" .

Requirements

To create your import extension, you need:

• A CSV file: Ensure you can open the file to review its contents.
• The template code: Use import.csvstatement.parametrizable.template.js as your base.
• Adaptable parameters: Update the parameter values in the JavaScript code based on your CSV file structure.

Extension parameters

At the beginning of the JavaScript code, locate the getConversionParamUser() function. This is where you change parameter values based on your CSV file.

//This function defines the parameters specific for the CSV Transactions file to be imported
function getConversionParamUser(convertionParam) {
    
    // The following variables need to be set according to the specific 
    // CSV file that will be imported
    
    // Column separator character 
    // Use '\t' for tab separated columns. 
    // Use the '\t' when processing CSV copied from Excel to the clipboard
    convertionParam.column_separator = ';';

    // Text delimiter character for string
    convertionParam.text_delimiter = '"';

    // Decimal separator charachter used for amounts
    convertionParam.amounts_decimal_separator = '.';

    // Line number where the column header starts (with the columns name)
    // First line is 0
    convertionParam.header_line_start = 0;

    // Line number where data starts 
    // Usually header_line_start + 1
    convertionParam.data_line_start = 1;

    // Column name header for the date transaction 
    convertionParam.column_date_name = '';

    // Date format for column containing dates 
    // For example 'dd.mm.yyyy', 'mm/dd/yyyy', 'dd.mm.yy', 'yyyy-mm-dd'
    convertionParam.date_format = '';

    // Column name for the column description 
    convertionParam.column_description_name = '';

    // Column name for the income amount 
    convertionParam.column_income_name = '';

    // Column name for the expenses/outcome amounts 
    convertionParam.column_expenses_name = '';
    
    // Column name for the external reference identification number of the transaction
    convertionParam.column_external_reference_name = '';
}

Parameter Descriptions

The parameters are in an object called convertionParam and are as follows:

• convertionParam.column_separator
  Parameter used to specify the separator character used in the CSV file to separate the columns (e.g., ;).
• convertionParam.text_delimiter
  Parameter used to specify the text delimiter character used in the CSV file to delimit the text (e.g., ").
• convertionParam.amounts_decimal_separator
  Parameter used to specify the amounts decimal separator used in the CSV file to indicate the decimals of the amounts (e.g, .).
• convertionParam.header_line_start
  Parameter used to specify at which row of the CSV file is the header with the column titles.
  Zero-based index (start counting from 0) of the row containing column titles (e.g., 0 for the first row).
• convertionParam.data_line_start
  Parameter used to specify at which row of the CSV file start the transactions rows.
  Zero-based index (start counting from 0) of the first row containing transaction data (e.g., 1 if it follows the header).
• convertionParam.column_date_name
  Parameter used to specify the name for the column with the date of the transaction used in the CSV file.
• convertionParam.date_format
  Parameter used to specify the format of the date used in the CSV file (e.g., dd.mm.yyyy for 31.12.2024).
• convertionParam.column_description_name
  Parameter used to specify the name for the column with the description of the transaction used in the CSV file.
• convertionParam.column_income_name
  Parameter used to specify the name for the column with the income amount of the transaction used in the CSV file.
• convertionParam.column_expenses_name
  Parameter used to specify the name for the column with the expenses amount of the transaction used in the CSV file (only if using two-column format).
• convertionParam.column_external_reference_name
  Parameter used to specify the name for the column with the identification number of the transaction used in the CSV file.

CSV amount columns format

The CSV file can have two format for amount columns:

• one-column amounts: income is positive, expenses are negative.
  In this case use only the convertionParam.column_income_name to define the column name used for the amounts.
• two-column amounts: income and expense amounts have separate columns.
  In this case use both convertionParam.column_income_name for income amounts and convertionParam.column_expenses_name for expenses amounts.

Example

Sample CSV file contents:

xxxx;;;;;
;;;;;
;;;;;
Datum;Buchungstext;Betrag;Saldo;Valuta;Id
03.01.2024;"aaa";127.2;42282.99;03.01.2024;1
04.01.2024;"bbb";-165.75;42117.24;04.01.2024;2
05.01.2024;"ccc";90.05;42207.29;05.01.2024;3

CSV file structure:

• ; is used as column separator
• " is used as text delimiter (e.g., "aaa")
• . is used as amounts decimal separator (e.g., 127.2)
• 3 is the row where the header line starts (start counting from 0)
• 4 is the row where the data start (start counting from 0)
• Datum is used as column name for the date
• dd.mm.yyyy is used as date format (e.g., 03.01.2024)
• Buchungstext is used as column name for the description
• Betrag is used as column name for the amounts (one-column format: income amounts are positive, expenses amounts are negative)
• Id is used as column name for the external reference identification numbers (e.g., 1,2,3)

Updated parameters:

// example with setting for the CSV file above
function getConversionParamUser(convertionParam) {
    convertionParam.column_separator = ';';
    convertionParam.text_delimiter = '"';
    convertionParam.amounts_decimal_separator = '.';
    convertionParam.header_line_start = 3;
    convertionParam.data_line_start = 4;
    convertionParam.column_date_name = 'Datum';
    convertionParam.date_format = 'dd.mm.yyyy';
    convertionParam.column_description_name = 'Buchungstext';
    convertionParam.column_income_name = 'Betrag';
    convertionParam.column_expenses_name = '';
    convertionParam.column_external_reference_name = 'Id';
}

How to Create and Run the parameterizable Embedded CSV Import Extension

For detailed instructions on creating and running embedded extensions, refer to the First Embedded Extension documentation.

1. Open an accounting file in Banana Accounting Plus.
2. Add the extension in the Documents table.
   Copy and paste the template code from import.csvstatement.parametrizable.template.js.
3. Adjust the parameters within the getConversionParamUser() function.
   Open the CSV file to view its content and edit the parameter values based on the CSV structure.
4. Confirm and save the accounting file.
5. Run the extension from the Documents table.
   Click the run symbol in the Attachments cell containing your code.
   Select the CSV file to import.
   In the Import Transactions dialog, choose the destination account and confirm with OK.
6. Transactions are imported and appear in the Transactions table.