Import data

Cette documentation est dépassée

La documentation la plus complète et actualisée est celle de Banana Comptabilité Plus : Essayez-le maintenant

In this article

See also:

  • End user genaral import documentation.
  • End user documentation for importing accounting data
  • If you are a developper and do want to test if the import file is correct. Download and install the Banana Software. In Starter Edition mode you can cCreate a new accounting file, use the import in to account function and make all tests you need. 

ImportApp

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

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

  • Appling an account number based on the content .
    For example set the ContraAccount to "3000" if the amount is positive and the "Description" start 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 text and the corresponding account to use.
    • Assume you invoice data  use a customers 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 retrive 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 and complete the data imported in Banana

Once the data is imported user can undo the operation or complete or modify the data.
By this way you can delegate to the user to check if the import is correct, if there are changes that need to be done.

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
  • Charachter 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 a 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 command for import

  1. Throught the Command in Menu Account1->Import in Accounting.
    This is a specialized import for accounting data, with postprocessing of that suitable for the accounting data.
    • Import Accounts, VatCode, Exchange rate, Invoice number
      It use the column name as in the transactions table
    • Transactions
      You have two option:
      • Double entry account format  (same as in the double entry transactions table)
      • Income/Expense account (for bank statements)
  2. Throught 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 specific of the import of double entry see the explanations the 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 account

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 this 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 charachter 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 multicurrency double entry accounting").
  • Unicode (utf-8) The content of the file is in Unicode utf-8 (it support any character set). 

Importing other transaction's columns

You can import any other field that is defined in the transactions table.
There are other alues 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.
  • DocPayed the document number that has been payed.
  • DocLink the address of the file that link to a scanned document (pdf, jpg, ..).
  • DateExpiration due date of the invoice.
  • ExternalReference a 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 a externaReference generated by the software that create the transactions to be imported.
    We suggest to use a name of the program and a number tha 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 transactions 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 o 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 way to do so:

  1. Let your invoice software generate a file for Banana a indicated in the "Import double entry transaction 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 in Banana you need to create a Javascript BananaApp  that translate the data in the format accetable for Banana.
    The script program take as input the content of a file and create an output that is a tab separeted text file with columns headers.
    See also repository on github.

Invoices on more lines

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

  • The first line you have the
    • AccountDebit the customer account number
    • AccountCredit is void.
    • Amount the total amount of the invoice. The amount due from Customer.
    • VatCode is void
  • For each item with different revenue accounts or Vat percentage you should have an additional lines
    • 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 apply 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

Il the imported data contains the "DocInvoice" columns, when Banana display you a sedond DialogBox, you can choose to have Banana group the transactions by DocInvoice.
In this case Banana automatically create if necessary a transaction for rounding differences.

Use Cost center instead for customer account

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

Import receipts/payment transactions in CSV format (Bank statements)

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

Menu Account1->Import into account

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

  • You can use any column name existing on the table
  • Column with special meaning are
    • Date  of the transaction (2014-12-31)
    • Description a brief text
    • Income: The amount in credit (can also be negative a mount)
    • Expenses: The amount in debit
    • ContraAccount: the account nember (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" identify a counterpart transaction and a "D" a detail transactions
  • Fields header in the first line o the file Fields names a 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 charachter 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 support any character set).

Automated data importing

The import of transaction can be automated using the command.

For further information see "Command line"

Partager cet article: Twitter | Facebook | LinkedIn | Email