Special formats for importing invoices

Special formats for importing invoices

Default importing options are described here in the video.

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

Default CSV file alignment

ColumnField nameCompulsory
Default value
How it is used?Column name
AInvoice noYesIf 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 linenumber
BCustomer NameYesIf customer ID is not available then searches contacts with this name. If missing then creates new customer/suppliercustomerName
CCustomer IDNo This is Books contact IDcustomerId
DDescriptionYesInvoice row descriptiondescription
FDeadline DateYesdeadline
GSubtotal Yes/No If blank then calculated from Total and Tax Sumprice
HTotal Yes/No If blank then calculated from Subtotal and Tax Sumtotal
ITax SumYes/NoIf blank then calculated from Subtotal and TotaltaxSum
JTax Rate NameNoIf blank then takes the first tax where percent matches from total and subtotaltaxName
KRounded sumNo0rounding
MRef NumberNoreferenceNumber
Expense/Revenue Account Number
Oproject nameNoprojects
RamountNo1 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 emptyamount
ScurrencyNoCompany currencycurrencyCode

Additional options with invoice import

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 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 “;”. 
  • You can add custom fields. 
    • If column alignment is not overridden then additional fields start from column U (the same as project groups). 
    • For example if you want to add customer registration code into column U then make sure U1=registrationCode. 
    • All the additional customer columns are described here: 
      • bankAccountNumber
      • bankIban
      • bankIdentifier
      • bankName
      • bankSwift
      • code
      • contactPersonEmail
      • contactPersonName
      • contactPersonPhone
      • email
      • entityTypeCode
      • fax
      • info
      • invoiceSendingAddress
      • invoiceSendingIdentifier
      • legalAddress
      • legalCity
      • legalCountryCode
      • legalPostcode
      • phone1
      • phone2
      • referenceNumber
      • registrationCode
      • supplierReferenceNumber
      • vatNumber
      • Website
    • All other additional columns: 
      • paymentDate
      • paymentSum
      • paymentAccountNumber
      • paymentAccountId
      • paymentArchivingId
      • parentDocumentNumber – requires GLOBAL_INVOICE_IMPORT_PARENT_TYPE