Import data from a txt file

该文档是已过时的旧文档

The most complete and up-to-date documentation is the one of Banana Accounting Plus: Try it now

In this article

Import with columns header

This allows the import of the data of a file in text format.

The first line of the txt file should be the columns header.

The file columns to be imported must have the same names used in the program for the Xml fields' headers.

Automated data importing

The import of transaction can be automated using the command.

For further information see "Command line"

Importing Invoice data

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

  1. Your invoice software already has the ability to export the invoice data.
    In order to import this data in Banana you need to create a Javascript filter  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.
     
  2. Let your invoice software generate a file for Banana a indicated in the "Import double entry transaction in txt format".

Columns for import

For importing invoices the tab separated import file  or translated file shoud have 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

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
    • AccountCredii 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 double entry transactions in txt format

We will describe here the import using the import of transactions in txt 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".

  • You can use any column name existing on the table
  • 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 (here without tab stop)

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 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 other transaction's fields

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, ..)
  • DocInvoice the invoice number
  • 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.

Import receipts/payment transactions in txt format

We will describe here the import using the import of receipt/payment transactions in txt format.

For what is concerning the specific of the import of income/expenses transactions see the explanations Import Income & Expenses 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 "Receipt/Payment transactions".

  • This format is very useful to convert a bank account format in Banana
  • You can use any column name existing on the table
  • Column with special meaning are
    • Income: can also be negative
    • ContraAccount: the account nember (debit/credit) or category
    • Account: If the file contains the movements of multiple accounts, the account of the transaction
  • 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 (here without tab stop)

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