Parameterizable import CSV extension

Documentation •
In this article

This guide explains how to create your own 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 CSV Import Extension

There are two methods to create an import extension:

  • As an Embedded Extension.
  • As a File Based Extension.

Embedded Extension

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

Follow these steps to create and run the extension:

  1. Creating the Extension
  2. Customizing the Extension
    • Adjust the parameters within the getConversionParamUser() function.
    • Open the CSV file to inspect its content and edit the parameter values based on the CSV structure.
  3. Running the Extension
    • Save the accounting file.
    • Run the extension from the Documents table by clicking the run icon 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.
    • The transactions will be imported and displayed in the Transactions table.

File-Based Extension

For detailed instructions on creating and running file based extensions, refer to the First File Based Extension documentation.

Follow these steps to create and run the extension:

  1. Creating the Extension
  2. Customizing the Extension
    • Adjust the parameters within the getConversionParamUser() function.
    • Open the CSV file to inspect its content and edit the parameter values based on the CSV structure.
  3. Installing the Extension
    • Install the extension from the menu: Extensions > Manage Extensions > Add from file.
  4. Running the Extension
    • Run the extension from the menu: Actions > Import to accounting > Import: Transactions.
    • In the Import to accounting dialog, select the "Parameterizable Import CSV" extension and the CSV file to import.
    • In the Import Transactions dialog, choose the destination account and confirm with OK.
    • The transactions will be imported and displayed in the Transactions table.

 

Help us improve the documentation

We welcome feedback on how to improve this page.

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

Share this article: Twitter | Facebook | LinkedIn | Email