There is an option in Xentral to set up a bank account via CSV import. To do this, first, navigate to the "Business accounts" module (Administration → Settings → Accounting → Business accounts) and click on "New" → "Custom".
Below you will find field explanations for each of the fields that are present in the Bank Account CSV module.
-
Name → Internal name for the account. This designation can be chosen arbitrarily and is displayed in various places in the interface
-
Type → Type of account (Bank account: Account (CSV import), PayPal account, credit card provider, cashier, etc.).
-
active → this account is active (in use) or inactive (closed)
-
No email → the customer will not receive an automatic email notification about the receipt of their payment.
-
Allow changes → changes may be made to the account statements (not recommended, only needed if the statements are entered manually).
Bank data (required for SEPA payment transactions and direct debit are mandatory fields for this purpose)
-
Holder → Account holder for this account (no special characters if Sepa payment transactions via XML are used)
-
BIC → BIC for this account (mandatory for bank accounts for direct debit and Sepa collective transfer)
-
IBAN → IBAN for this account (for bank accounts mandatory for direct debit and Sepa collective transfer)
-
BLZ → bank code for this account
-
Account → Account number for this account
-
Creditor ID → Creditor ID for bank accounts (for bank accounts mandatory information for direct debit)
-
Direct debit → Check to activate direct debit collection.
-
(HBCI → only still available in old versions)
-
(HBCI ID → only still available in old versions)
-
Chart of accounts → Number of the chart of accounts for the bank account (for exporting the statements); depending on the chart of accounts, e.g. 1800, 1700 etc.
-
CSV import → input mask for CSV import (example see CSV import settings).
-
First data line → first line number containing real data (e.g. for data from line 1: "1"). If there is a label line, the first real data line is usually line 2 (to be specified then "2").
-
Encoding → Set to UTF8 Encode by default to transfer the umlauts correctly.
-
Separator → semicolon or comma. The separator is visible in the CSV file via a text editor.
-
Masking → quotation marks or no masking. The masking is visible in the CSV file via a text editor.
-
Ignore last lines → enter a "0" in the standard, unless the account statement contains additional information lines at the end that no longer belong to the individual postings. To do this, enter as a number how many lines are to be ignored at the end.
-
Date → Column in which the date is located.
-
Input format → Format as the date is specified in your CSV file (Mostly date dd.mm.yyyy then: %1.%2.%3)Example for more complicated format: 01/07/2020 10:07:18 GMT+00:00 => %1.%2.%3 %4:%5:%6 GMT+00:00
-
Output format → Format as it is output for Xentral (Must always be output as follows: %3-%2-%1).
-
-->>>> Example for input and output: 12/24/2016 in CSV corresponds to input format %1.%2.%3 and output format %3-%2-%1 (output must always be brought to this format YYYY-MM-DD). Another example: If the input format is as follows: 08/11/2016 11:12:45 this will be displayed with variables like this: %1/%2/%3 %4:%5:%6. The output format remains %3-%2-%1 (the time will be truncated in the output).
-
Amount → column where the amount is (only this field if the amount is in a column).
-
Extra credit and debit → is needed (check and specify column for credit and debit), if the amount is divided into 2 columns by credit and debit
-
Credit → column in which the amount for HABEN is located (prerequisite: check mark for "Extra credit u. debit")
-
Debit → column in which the amount is for DEBT (prerequisite: checkmark for "Extra credit and debit")
-
Posting text → Column(s) containing the posting text. Special feature: several columns can be combined for the posting text. Example: 5+11+3+8+12+18+14. Please note: put the most relevant information for the accounting export at the front, because too long texts are often cut off here. In order to import as much relevant information as possible into a separate accounting software (e.g. Datev), it is recommended to use e.g. the following order: Sender name + Booking text + Reference number + Debit information + Other fields + ... + ...
-
Currency → Column containing the currency
-
Credit/Debit Identifier → Separate column that defines whether the amount is credit or debit (e.g. an extra column where the information "H" or "S" can be found)
-
Marker input → character for "Credit" e.g. "H" or "+" (Prerequisite: column specification for "Credit/Debit identifier")
-
Marker output → character for "debit" e.g. "S" or "-" (prerequisite: column specification for "credit/debit identifier")
Settings (CSV Import):
Call example bank account:
If the bank account is to be called up, navigate to Accounting → Incoming payments and select the respective account via the editor button .
Settings Deutsche_Bank.csv (as of 10/2019):
-
Field “Booking text" field: 4+5+8+9+10+6+7+3
Note
Please note that the bank may change the settings minimally from time to time.
Example of CSV settings and a CSV file:
In this simple example you can see the CSV settings of the created business account with the corresponding CSV file.
Example file
When importing CSV files, it is important to ensure that the original file from the bank or payment provider is imported and that it has not been opened and (automatically) saved via other systems, otherwise the original file will no longer be importable or will provide incorrect values. For Amazon Pay, the "account numbers" are the corresponding account number from the account frame.
Show duplicate payments as import errors in incoming payments: In some cases, payments are very similar and are incorrectly suspected to be duplicates by Xentral. If this check box is selected, you can manually import payments that are suspected duplicates, which will be marked as import errors for your attention.
Einzelbuchungen aktivieren (Enable individual bookings): Collective debits can be created in Xentral in the Payment transactions module. In this case, several payments are combined into a single payment. If Einzelbuchungen aktivieren is checked, these payments can be broken down into single items again by Xentral. This is visible in the account statements in Xentral and facilitates the assignment of payments to orders, credit notes and invoices.
pain.001.001.03: Select this check box if you want to create a SEPA XML file from this account in pain.001.001.03 format. You can upload that file to your bank to initiate payment of liabilities and credit notes.