Special formats for importing invoices

Default importing options are described here in the video. The guide for importing standard invoices can be found here.

This tutorial speaks about how to import invoices with custom fields and XLS files.

Default CSV file alignment

Column	Field name	Compulsory	Default value	How it is used?	Column name
A	Invoice no	Yes	–	If you have two or more lines with the same invoice number it will combine them together and create one invoice from that. Meaning one line is meant for one invoice line	number
B	Customer Name	Yes	–	If customer ID is not available then searches contacts with this name. If missing then creates new customer/supplier	customerName
C	Customer ID	No	–	This is Books contact ID	customerId
D	Description	Yes	–	Invoice row description	description
E	Datetime	Yes	–		date
F	Deadline Date	Yes	–		deadline
G	Subtotal	Yes/No	–	If blank then calculated from Total and Tax Sum	price
H	Total	Yes/No	–	If blank then calculated from Subtotal and Tax Sum	total
I	Tax Sum	Yes/No	–	If blank then calculated from Subtotal and Total	taxSum
J	Tax Rate Name	No	–	If blank then takes the first tax where percent matches from total and subtotal	taxName
K	Rounded sum	No	0		rounding
L	Link	No	–		link
M	Ref Number	No	–		referenceNumber
N	Expense/Revenue Account Number	No	–		accountNumber
O	project name	No	–		projects
P	partnerDocId	No	–		partnerDocumentId
Q	EAN	No	–		ean
R	amount	No	1	This is quantity – necessary if you want to see quantity in Books. This field will not recalculate total values. Meaning if you want to use quantities it is wise to add tax rate name and subtotal and leave total and tax field empty	amount
S	currency	No	Company currency		currencyCode
T	currencyRate	No	1		currencyRate

You have the following options:

If you don’t want to separate projects with project names and instead want to use projects separated by project groups then you can add project group columns
- If you want to use project groups then add them into separate columns after currencyRate – leave one empty column after currency rate and then every project group into separate column
- Into the header of project group column add project group id. Id values you can get from report generator
You can change the format of the columns
- If you want to use your own alignment not the default one then add Global parameter: GLOBAL_USE_INVOICE_IMPORT_ORDERING
- With that global in invoice import you have ordering field where you can add your own ordering alignment. It should have previously described column names separated by “|”, “,” or “;” and entered in the Value column
Example of the alignment: projects|partnerDocumentId|actualAddress|customerName|registrationCode|other|other|customerNameIfNotBlank|registrationCodeIfNotBlank|legalAddress|contactPersonName|contactPersonPhone|contactPersonEmail|actualAddress|date|referringIdentifier|other|other|partnerArticleId|amount|unit|paymentAccount=11000|paymentAccount=11020|other|other|other|total|other|other|other|taxName|other|other|number|other|other|info|other|printedInfo|description

Users can map CSV column headers directly to system values when importing invoices. This means that if the CSV structure changes in the future, the import will continue to work because the system identifies the columns by their headers.
- To enable this, add the GLOBAL_INVOICE_IMPORT_LET_SYSTEM_DO_MAPPING global attribute. In the Value field, enter the mapping rule using the format column header=value, with each mapping separated by the “|” character.
- Asukoht=projects|Tehingu ID=number|Kliendi nimi=customerName|Kliendi isikukood=customerId|Arve edastatud e-postile=contactPersonEmail|Registrikood=registrationCode|Juriidilise isiku aadress=legalAddress|Kontaktisiku nimi=contactPersonName|Ohtlike jäätmete kontaktisiku telefon=contactPersonPhone|Kontaktisiku email=contactPersonEmail|Jäätmete tekkimise koht=other|Tehingu kp/kellaaeg=date|Auto number=other|Kategooria=other|Jäätmeliik=description|Jäätmeliigi kood=other|Kogus=amount|Tasutud kaardiga=other|Tasutud ülekandega=other|Tasutud saatelehega=other|Summa (€)=total|Summa korrigeerimine (kokku, €)=other|Summa tasumiseks (kokku, €)=other|Käibemaksuta (€)=price|Käibemaksu määr (%)=taxName|Käibemaks (€)=taxSum|Arve number=other

It is also possible to configure the system to skip rows that are not required during the import.
- To do this, add the GLOBAL_INVOICE_IMPORT_SKIP_WHEN global attribute and enter the value to be skipped in the Value field (for example, “Cash Total”).
- The content of the Value field must exactly match the corresponding text on the invoice. If the values differ, the rule will not work. For example, if the Value field contains “Cash Total”, but the invoice contains “Cash”, the attribute will not be applied.