Import data

Banana Accounting is a very suitable companion for business solutions that do not provide an integrated accounting solution. Banana Accounting software offers different advantages, therefore many software houses, independent or in house solutions developers offer an integration mechanism and market their solution with Banana:

• Solutions developers can provide a complete business solution, without the need to develop a new accounting module. 
• Banana Accounting is very versatile, easy to use, provides many functionalities and it is also affordable. 
• In Banana Accounting Importing is a primary function, that is very much advanced.
  • It supports different formats. 
  • Once the data imported has been imported it can still be verified and changed by the users.
    Implementing the export is much easier, special cases or errors due to incorrect configurations, can be modified by the users. 
    Users can fix the problem directly and the developer can later provide a new patch release. This approach makes supporting the export to Banana much easier and less stressful.
  • Import can be undone and repeated, later.
    During the import one sometimes happens to see error in the original data. The user undoes the import, fixes the data and repeats the export and import. 
• Banana Accounting includes a Web server and an API that can be used to retrieve information. Prior to exporting the data it is possible to verify the status of the accounting.

Currently, the only way to integrate data from other solutions is to use the import function. Due to the flexibility and possibility for users to check and modify the imported data, this has been proven to be a better solution.
Therefore, we did not make available an API for integrating data. Kindly let us know user cases where this would be a better solution.

For the development needs you can download the Banana Accounting software version available on our web site. You can use all functionalities and save up to 70 transactions. This version is normally sufficient for the development and testing process.

User cases

Generally the integration is related to: 

• CRM with invoicing or Online Web shop.
  Customer sales are exported in TXT format and imported in Banana Accounting.
  • Customer information is exported as an accounts data and imported into the Accounts table
  • Invoice information or payments are exported as transactions and imported into the Transactions table.
• Payroll and Salary
  End of month's summary data is exported as transactions and imported into Banana Accounting.
• Accounting data from another accounting software is integrated in Banana Accounting.

Other documentation

• End user general import documentation.
• End user documentation for importing accounting data. 

How to proceed

If you are a developer and do want to test if the import file is correct:

• Download and install the Banana Software. In Starter Edition mode you can Create a new accounting file, use the import into accounting function and operate all tests you need. 
• In case you are an independent software developer and need full functionalities we can provide a time-limited full license.
  Request must come with a link to the developer web site.

How to for

• How to read the balance for an account / customer / supplier
  Use the Web API.
  Replace the "1000" with the account number.
  "/v1/doc/accounting.ac2/table/Accounts/row/Account=1000/column/Balance"
• How to get about Accounts, VatCodes
  Use the Web API.
  Replace the "Accounts" with the table you need to query.
  "/v1/doc/accounting.ac2/table/Accounts?format=json"
• How to add a customer or supplier
  • Create and export file in format TXT for the Accounts table, with the customers and suppliers.

 

ImportApp

An importApp is a javascript program that is executed internally by Banana (when the user wants to import data) and usually converts data from a proprietary format to a Banana "Text file with columns header".

You can create an ImportApp that does more sophisticated things like:

• Applying an account number based on the content .
  For example set the ContraAccount to "3000" if the amount is positive and the "Description" starts with "Revenue from "
• Use data contained in another Banana table to complete the transactions.
  • Create a table with (Tools->Add new functionalities->Add simple table) where you list text to look for in the transactions texts and the corresponding account to use.
  • Assume you invoice data using a customer number that is different from the account number in the Account table.
    Add a new column in the Account table and then use the content to retrieve the appropriate account number.
  • Adding a prefix to the supplier invoice number, so that you can easily distinguish incoming and outgoing invoices.

For more information  see creating an ImportApp

Import "Text file with columns header"

The Banana import format is of type "Text file with column headers".

• Fields and column name separator is to be tab separated "\t"
• Each line (after a "\n") is a new record
• Character code preferably UTF8 or else the local one.
• The first line contains the columns header name
  • You can use any column name existing on the table
  • Names are case sensitive and must correspond to the Banana NameXml (English) of the column.
• Starting from line 2 it contains the data to be imported
  • The format for the Date fields is yyyy-mm-dd
  • The decimal separator is the decimal point "."
  • Amount should not have any thousand separator

Two commands for import

1. Through the Command in Menu Account1->Import in Accounting.
   This is a specialized import for accounting data, with post-processing of that suitable for the accounting data.
   • Import Accounts, VatCodes, Exchange rates, Invoice numbers
     It uses the column name as in the transactions table
   • Transactions
     You have two options:
     • Double-entry accounting format  (same as in the double entry transactions table)
     • Income/Expense accounting (for bank statements)
2. Through the command Data import. Import from txt.
   You can import data in any table. For accounting data, prefer option 1.

Import Double-entry transactions in CSV format

For what is concerning the specifics of the import of Double-entry see the explanations for Import Double-entry accounting transactions. The only difference is that the Complete transactions with section is active and allows the user to enter the initial document number and the destination account number. Once the import is done, the counterparty account will have to be entered manually.

Menu Account1 → Import into accounting

The type of file to be used is a "Text file with column headers".

Main columns for import

For importing invoices the tab separated import file or translated file usually contains these columns:

• Date of the transaction (2014-12-31).
• DateDocument with the date of the invoice.
• DocInvoice the invoice number.
• Description a brief text.
• AccountDebit the account number of the customer or the general account for customers.
• AccountCredit the account number of the revenue account.
• Amount the amount of the accounting currency.
• VatCode the vat code that should be used.
• AmountCurrency if multi-currency the amount of the invoice in original currency and currency of the AccountDebit.

Example file Double-entry format

Fields are separated by the tab char (ASCII decimal 11, C language "\t").
In the example the tab character is not visible.

Date Doc Description AccountDebit AccountCredit Amount VatCode VatPercentNonDeductible

2018-01-03 Bank to Cash 1000 1020 350.00
2018-01-05 Office Supplies 6500 1000 32.50 E76

Options

• Import using clipboard data will use the content of the clipboard instead of the file
• Autocomplete values: Some fields of the transactions are automatically completed (see "Importing transactions for multi-currency Double-entry accounting").
• Unicode (utf-8) The content of the file is in Unicode utf-8 (it supports any character set). 

Importing other transaction's columns

You can import any other field that is defined in the Transactions table.
There are other values that we suggest to import if available:

• DateDocument the date of the original document (for example the date of the invoice).
• DocOriginal the document number for example the invoice number.
• DocPaid the document number that has been paid.
• DocLink the address of the file that links to a scanned document (pdf, jpg, ..).
• DateExpiration due date of the invoice.
• ExternalReference an information that help to identify the transactions as unique.
  It will be used in future implementation of Banana (in conjunction with the date) to warn a user that the transaction has already been imported.
  This should be an external Reference generated by the software that creates the transactions to be imported.
  We suggest to use a name of the program and a number that is unique for the accounting period.
  For example "invoice-2013-00001.001" with year, invoice number and a progressive number that is relative to the invoice in case it will be necessary to have more transaction lines for the same invoice.

Importing transactions for multicurrency Double-entry accounting

By importing multicurrency data there can be rounding or calculation differences due to different development tools used. To avoid such differences you should provide only certain fields and while importing the program will calculate the field values that are missing (with the option "Autocomplete values") .

• If you provide only "AmountCurrency" the program will use the default exchange rate and will calculate the "Amount".
• In order to avoid error provide always the "ExchangeCurrency"
• If you provide the "AmountCurrency"  and the "ExchangeRate" and the "Amount" are 0 or not present the program will calculate the exchange rate based on the column "Amount" and "AmountCurrency".

Importing Invoice data

The data of your invoice software can be imported in Banana.
There are two ways to do so:

1. Let your invoice software generate a file for Banana as indicated in the "Import Double-entry transactions in txt format".
2. Use the data of the export format of your existing invoicing software .
   In order to import this data from a proprietary format into Banana you need to create a Javascript Banana Extension  that translates the data into a format acceptable for Banana.
   The script program takes as input the content of a file and creates an output that is a tab separated text file with columns headers.
   See also repository on Github.

Invoices on multiple rows

Most invoices have different items that need to be registered in different revenue accounts or that have different VAT percentages.
In this case, for each invoice you need to have many import rows.
Date, DateDocument, DocInvoice have always the same values.

• The first row you have the
  • AccountDebit the customer account number
  • AccountCredit is void.
  • Amount the total amount of the invoice. The amount due from the Customer.
  • VatCode is void
• For each item with a different revenue account or Vat percentage you should have an additional row
  • AccountDebit is void
  • AccountCredit the revenue account for this item
  • Amount the total amount to be registered on this account.
    If you have a VatCode it could be convenient to use the amount without VAT.
  • VatCode the VatCode that applies to this item.
    If the Amount is Net of VAT you should define a VAT Code that is calculated on the net Value.

Group transactions by invoice number

If the imported data contains the "DocInvoice" columns, when Banana displays you a second DialogBox, you can choose to have Banana group the transactions by DocInvoice.
In this case Banana automatically creates, if necessary, a transaction for rounding differences.

Use Cost center instead for customer account

If you want to keep track of the invoices registered but do not want them to be recorded on individual accounts you can use the Cost center (CC3).  See also Client/Suppliers register.

Import Income & Expenses transactions in CSV format

This format is suitable to import Bank statements in electronic format.

• For what is concerning the specifics of the import of income/expenses transactions see the explanations Import Income & Expenses accounting transactions.
• The information about the Double-entry import is also partially applicable.

Menu Account1->Import into accounting

The type of file to be used is a "Receipt/Payment transactions".

• You can use any column name existing on the table
• Columns with special meaning are
  • Date  of the transaction (2014-12-31)
  • Description a brief text
  • Income: The amount in credit (can also be a negative amount)
  • Expenses: The amount in debit
  • ContraAccount: the account number (debit/credit) or category
  • Account: If the file contains the movements of multiple accounts, the account of the transaction
  • VatCode the vat code that should be used
  • IsDetail for composed transactions a "S" identifies a counterpart transaction and a "D" a detail transactions
• Fields header in the first line of the file. Fields names are case sensitive and must correspond to the NameXml (English)
• Fields and column name separator is to be tab separated "\t"
• Each line (after a "\n") is a new record
• The format for the Date fields is yyyy-mm-dd

Example file Income/Expenses format

Fields are separated by the tab char (ASCII decimal 11, C language "\t").
In the example the tab character is not visible.

Date Description Income Expenses ContraAccount Account
2007-01-02 Paper  30.00
2007-01-06 material for photographic competition  259.2  3000

Options

• Import using clipboard data will use the content of the clipboard instead of the file
• Autocomplete values: Some fields of the transactions are automatically completed (see "Importing transactions for multicurrency Double-entry accounting").
  Once the import is done, the contra account will have to be entered manually.
• Unicode (utf-8) The content of the file is in Unicode utf-8 (it supports any character set).

Import Accounts

For creating new accounts, customers, suppliers, cost centers. See Chart of accounts documentation for the list of columns available.

Menu Account1->Import into accounting->Accounts

The type of file to be used is a "TXT with headers.

• You can use any column name existing on the table
  • Account.
    The account number.
  • Description
    A brief text, organization or customer name
  • BClass
    Required (1,2,3,4).
  • Gr1
    Obligatory. It is also used to order the data when it is imported.
  • Address fields.

When importing the user can choose to import only the new lines.