Import Double-entry transactions in CSV format

Documentazione •
In questo articolo

For what is concerning the specifics of the import of Double-entry see the explanations for Import Double-entry accounting transactions.

Menu ActionsImport into accounting

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

File format and Main columns for import

  • You can use any column that is available in the Transactions table
    (See: Transactions Table double entry) and you can see in the Column Setup list of columns.
  • Main columns for double entry accounting
    • Date of the transaction (2014-12-31).
    • 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.
  • Fields header in the first line of the file.
    Fields names are case sensitive and must correspond to the NameXml (English) that you find in the Setting Tab of the Columns setup.
  • Fields header and field data must use the tab character as separator "\t"
  • Each line (after a "\n") is a new record
  • The format for the Date fields is yyyy-mm-dd
  • Character code preferably UTF8 or else the local one.

Example file Double-entry format

In the example the values are written in an excel document.

Double entry trans. excel example

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).
  • DocInvoice the invoice number.
  • DocOriginal the document number for example the invoice number.
  • DocLink the address of the file that links to a scanned document (pdf, jpg, ..).
    You can use a relative path name to the accounting file.
  • 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.

Dicci come possiamo aiutarti meglio
Se le informazioni contenute in questa pagina non sono quelle che cerchi, non sono sufficientemente chiare o non sono aggiornate, facci sapere.

Condividi questo articolo: Twitter | Facebook | LinkedIn | Email