Import Invoices and Credit Notes
Importing Invoices and Credit Notes is an efficient alternative to manually creating transactions. It can be used to simplify the process of transferring data from your previous accounting system or to assist with importing your organisation's regular Sales and Purchases activities. To get started, you will need to download the template file provided. The template contains column headings which correspond to the fields maintained when manually creating Invoices and Credit Notes, and you should not alter these headings.
Navigate to the Sales or Purchases tab and click on the Import button. The appropriate Import Sales Transactions or Import Purchase Transactions pop-up will appear.
Step 1: Download the template file
Click on the link to download the template file. It is advisable to open the file using a spreadsheet program like Excel. This will automatically recognise the commas in the CSV file and split the fields into columns. Enter or copy and paste your input data into the relevant fields.
The following general rules and tips should be considered:
- Save the file as CSV (comma delimited) with a .csv file extension as only CSV files are permitted for upload.
- Field names and field order cannot be changed or deleted.
- Maximimum lines per upload file is 250.
- Duplicates are NOT checked. Take care not to upload the same file twice.
- Commas within a field require the contents of the field to be Text delimited with quotation marks eg "xxx, xxx" (your spreadhseet program should do this for you automatically).
The following specific rules apply to each field. They are the same fields that would be used for manual entry of the transactions and are explained in greater detail in Sales Invoice, Sales Credit Note, Purchase Invoice, Purchase Credit Note.
| Template Field |
Rules and Explanation |
|
InvoiceNumber |
Mandatory field. This value does NOT become the Transaction ID for the transaction created. It is only used during the import to identify when a new transaction begins. The value in this field is not saved. AccountsPortal will automatically assign Invoice Numbers based on your organisation's Transaction Numbering. Multi-line transactions must all have the same InvoiceNumber value on consecutive rows as in the example above for INV208. Multi-line transactions only need values in the first row for the Customer/Supplier, Reference, InvoiceDate, DueDate and Memo fields. Values entered for these fields on subsequent lines of a multi-line transaction will be ignored Values in the InvoiceNumber field can only repeat when used to enter Multi-line transactions. If the same value appears further down in the file, it will be considered a duplicate and rejected. |
|
Customer/Supplier |
Mandatory field If the value in this field corresponds exactly with the Name of an existing Customer/Supplier, then that Custome/Supplier will be used. Any deviation will result in a new Customer/Supplier being automatically created. |
|
Reference |
Optional field. We recommend inserting the original Invoice or Credit Note number in this field. |
|
InvoiceDate |
Mandatory field. Must follow the format DD/MM/YYYY or DD-MM-YYYY. |
|
DueDate |
Mandatory field. Must follow the format DD/MM/YYYY or DD-MM-YYYY. |
|
VatTreatmentId |
Mandatory field. A numeric value indicating the underlying nature of the transaction for VAT/GST purposes. If you are not VAT/GST Registered, then use 99. UK Vat Registered Organisations: Sales Invoices and Credit Notes 1 - Goods or Services to UK Customer
Purchase Invoices and Credit Notes 21 - Goods or Services from UK Supplier
See VAT UK - Box by Box for more information on how the different options flow through to the VAT Report
International VAT/GST Registered Organisations: Sales Invoices and Credit Notes 1 - Sale of Goods or Services
Purchase Invoices and Credit Notes 21 - Purchase of Goods or Services
If you are not registered for VAT on the date in the InvoiceDate field, the entry in VatTreatmentId will be replaced by 99 automatically. |
|
Memo |
Optional field. Any text relevant to the individual transaction. If you usually request your customers to pay you via PayPal, then you can insert [paypal_link] on your Sales Invoices. See the section on Organisation Settings for more information. |
|
ItemQuantity |
Mandatory field. If Quantity is not relevant to your invoice, use a value of 1. |
|
ItemUnitAmount |
Mandatory field. The Amount of the line item net (i.e. excluding) any VAT. Use a -ve sign, eg. -200.00 to indicate that the transaction is a Credit Note Multi-line transactions will use the sign of the first row to determine if the transaction is an Invoice or Credit Note. The sign on the remaining lines is ignored. The total value of a line is ItemQuantity x ItemUnitAmount + any VAT (if applicable). |
|
ItemAccountCode |
Mandatory field. The Account number defined in the Chart of Accounts. |
|
ItemDescription |
Optional field. Any relevant text to describe the line item. |
|
ItemCategoryCode |
Optional field. If specified, this needs to exactly match the Category Code as defined in Categories. |
|
ItemVatCode |
Mandatory field. The VAT Code applicable to the line item. This needs to have the exact same description as the VAT Code field in a manually created transaction. The default descriptions are listed below. Note that these may no longer apply if you have added or edited VAT Rates since the creation of your Organisation. Default descriptions for Sales Invoices and Credit Notes: 20% Income Default descriptions for Purchase Invoices and Credit Notes: 20% Expense If you are not registered for VAT on the date in the InvoiceDate field, the entry in ItemVatCode will be replaced by 'No Vat' automatically. |
|
ItemVatAmount |
Optional field. If this field is left blank, the VAT Amount will be calculated based on the ItemVatCode field above. Only insert a value into this field if you require the VAT Amount for a line item to differ from the amount that would be calculated by using the ItemVatCode. If you are not registered for VAT on the date in the InvoiceDate field, the value in ItemVatAmount will be ignored. |
The above file viewed in a text editor such as Notepad would appear as follows:
Step2: Import the file
Click on the Browse button and select the saved CSV file. Click the Upload button. A pop-up will show a summary and status of incorrect file records. The import has not yet taken place and can be cancelled in order to amend the file.
Correct the errors in your file and resave the CSV. Click on the Go Back button and try to upload the file again. Once all errors have been resolved, the Complete Import button will become active.
The default setting is for all transactions to be saved as Draft. The recommended approach when importing transactions is to save them as Draft and then to review each item thoroughly before saving as actual invoices and Credit Notes. This approach will result in less errors.
Click the Complete Import button to import the transactions. A pop-up will confirm the process has been successful.
WARNING:
IT IS ALWAYS RECOMMENDED THAT YOU VERIFY THE RESULTS OF YOUR IMPORT. DO NOT EMAIL INVOICES OR STATEMENTS UNTIL YOU HAVE CHECKED THEIR VALIDITY. MANIPULATING DATA IN FILE FORMATS CAN RESULT IN ERRORS WHICH IS WHY WE ADVISE YOU TO SAVE TRANSACTIONS INITIALLY AS 'DRAFT'.
| Template Field | Rule |
| InvoiceNumber | - Mandatory field. - This value does NOT become the Transaction ID for created transaction. It is used during the import to identify when a new transaction begins. For more information see Mulit-line transactions below. |
| Customer Supplier |
- Mandatory field - Must use the exact Name from the Customer/Supplier list. Any deviation will result in a new Customer/Supplier being created. |
| Reference | - Optional field. - Advisable to insert original Invoice or Credit Note number. |
| InvoiceDate | - Mandatory field. - Must be use format of DD/MM/YYYY or DD-MM-YYYY |
| DueDate | - Mandatory field. - Must be use format of DD/MM/YYYY or DD-MM-YYYY |
| Memo | - Optional field. |
| ItemQuantity | - Mandatory field. - If Quantity is not very relevant to your invoice, use a value of 1 |
| ItemUnitAmount | - Mandatory field. - The Amount of the line item net of any VAT. |
| ItemAccountCode | - Mandatory field. - The Account number defined in the Chart of Accounts. |
| ItemDescription | - Optional field. - Any relevant text to describe the nature of the line item |
| ItemVatType | ######################################################################################## |
| ItemVatAmount | - Optional field. - The VAT value applicable to a line item can be entered here. Please read warnings in this regard. - If the field is left blank, the VAT Amount will be calculated based on the ItemVATType field above. |