Introduction
There are two different options available in the Chart of Accounts importer:
Import Chart of Accounts
- to completely replace the existing Chart of Accounts, deleting all data and transactions
Add to Chart of Accounts
- to add a set of new codes to the existing Chart of Accounts, keeping all data and transactions
Import Chart of Accounts
1. Download the template and prepare the data
Go to Setup>Data Importer>Chart of Accounts>Import Chart of Accounts
Click on Next and download either the:
- Prepopulated file
To download a copy of your existing chart of accounts to use as a basis for your changes
- Blank file
Open the downloaded file, check and update all sheets, ensuring that all mandatory fields are populated.
Using the Import Chart of Accounts option will completely remove all existing data, including system accounts and defaults, so make sure you check and fill in all of the sheets in the template including:- GL Categories
- GL Sub Categories
- GL Accounts
- Tax Codes
- System Accounts Check
- Use this sheet to set all of the system accounts that must be set up before you can use the system, eg control and tax accounts
- System Defaults Check
- Use this sheet to set all of the default settings for customers, suppliers, items and general ledger accounts.
Mandatory fields are marked with an asterisk (*) on the column header.
Do not delete or change any of the sheets or headings in the template.
Detailed instructions for filling out each sheet can be found below.
2. Import the completed template
Go to Setup>Data Importer>Chart of Accounts>Import Chart of Accounts, click on Next
Click on Browse option to find your saved template
Click Import
The import file will be validated, if any errors are found, an error message will be displayed, and an error file can be downloaded which will include details of all errors and a copy of the import file.
All errors must be corrected, and the file should be re-imported to proceed. See section 3 for help with correcting errors
If no errors are found with the imported file, the system will give you a final warning that all data is about to be deleted. This means all data will be permanently deleted, you should only proceed with this option if you are happy to delete all of the following:
- Transactions
- Customer and Supplier Accounts
- Items
- Categories
- Sub-Categories
- GL Accounts
- Tax Codes
- Dimensions and Elements
- BI Codes
- System Accounts
- Default Settings
Enter your login details to confirm the step including the company ID, your username and password.
3. Review and correct errors in your imported file
If any validation errors are found when you import your completed file, download the error file and make any corrections (you can make corrections directly in the error file).
A new sheet, Import Errors, will be included at the front of the error file. This shows a summary of all errors found during validation, together with a link to the fields causing the error and the actual error message.
Click on each link to see details of the error, cells causing the error will be highlighted in red with a note showing further details (hover over the highlighted cell to see the detailed message).
Fix all of the errors in the file, then follow the steps in section 2 above to re-import the corrected file.
4. Check the Imported Data in the system
You will receive a confirmation message when the import has successfully completed. Close the importer and check the imported data in the system, including system accounts and defaults.
It is really important to check that you are happy with your imported data before starting to use the system. Currently there is no way to import changes to existing codes via the data importer. If you need to make changes to a small number of fields, you can do this directly in the system. However if you need to make changes to all accounts, it maybe better to re-import the whole Chart of Accounts, but you can only do this if you have not started to use the system as all data will be deleted when you choose this option.
Add to Chart of Accounts
1. Download the template and prepare the data
Go to Setup>Data Importer>Chart of Accounts>Add to Chart of Accounts
Click on Next to download a blank template
Open the downloaded file, add the new accounts that you want to import, ensuring that all mandatory fields are populated.
You can import additional codes to one or more sheets within the template, do not delete or change any of the sheets or headings in the template, just leave any unused sheets blank.
Mandatory fields are marked with an asterisk (*) on the column header.
Detailed instructions for filling out each sheet can be found below.
2. Import the completed template
Go to Setup>Data Importer>Chart of Accounts>Add to Chart of Accounts, click on Next
Click on Browse option to find your saved template
Click Import
The import file will be validated, if any errors are found, an error message will be displayed, and an error file can be downloaded which will include details of all errors and a copy of the import file.
All errors must be corrected, and the file should be re-imported to proceed. See section 3 for help with correcting errors
No Data is deleted when using the Add to Chart of Accounts Option, if you include any existing data in the template, the template will not be imported and duplicated accounts will be highlighted in the error file.
3. Review and correct errors in your imported file
If any validation errors are found when you import your completed file, download the error file and make any corrections (you can make corrections directly in the error file).
A new sheet, Import Errors, will be included at the front of the error file. This shows a summary of all errors found during validation, a link to the fields causing the error and details of the error message.
Click on each link to see details of the error, cells causing the error will be highlighted in red with a note showing further details (hover over the highlighted cell to see the detailed message).
Fix all of the errors in the file, then follow the steps in section 2 above to re-import the corrected file.
4. Check the Imported Data in the system
You will receive a confirmation message when the import has successfully completed. Close the importer and check the imported Chart of Accounts in the system, including system accounts and defaults.
It is really important to check that you are happy with your imported data before starting to use the system. Currently there is no way to import changes to existing codes via the data importer. If you need to make changes to a small number of fields, you can do this directly in the system. However if you need to make changes to all accounts, it maybe better to re-import the whole Chart of Accounts, but you can only do this if you have not started to use the system as all data will be deleted when you choose that option.
GL Category Sheet - detailed help
Mandatory fields are marked with an asterisk (*) on the column header.
In the Add to Chart of Accounts template, the reporting sort order will be defined on import - any new categories will be added sequentially after the existing categories already in the system.
| Field | Notes |
|---|---|
| Category Code* | Use numbers and letters, up to 50 characters. You can use dash (-), underscore (_) or full stop (.) as separator within the code but not at the start of the code. |
| Category Description* | Use numbers and letters, up to 50 characters. Some special characters are allowed including the € and £ currency signs. |
| Reporting Sort Order* |
[Only used in the Import Chart of Accounts template, not the Add to Chart of Accounts Template] Use only numbers to indicate in what order categories should appear in your reports (Profit and Loss/ Income and Expenditure Statement or Balance Sheet) |
| Category Type* |
Select one of the types from the list:
The category type determines where the accounts linked to this Category will appear in the financial reports, i.e. in Profit & Loss or in Balance Sheet. Do not edit values or add any new entries to the list. |
GL Sub-Category Sheet - detailed help
Mandatory fields are marked with an asterisk (*) on the column header.
In the Add to Chart of Accounts template, the reporting sort order will be defined on import - any new sub-categories will be added sequentially after the existing sub-categories already in the system.
| Field | Notes |
|---|---|
| Sub-Category Code* | Use numbers and letters, up to 50 characters. You can use dash (-), underscore (_) or full stop (.) as separator within the code but not at the start of the code. |
| Sub-Category Description* | Use numbers and letters, up to 50 characters. Some special characters are allowed including the € and £ currency signs. |
| Reporting Sort Order* |
[Only used in the Import Chart of Accounts template, not the Add to Chart of Accounts Template] Use only numbers to indicate in what order categories should appear in your reports (Profit and Loss/ Income and Expenditure Statement or Balance Sheet) |
GL Accounts - detailed help
Mandatory fields are marked with an asterisk (*) on the column header.
| Field | Notes |
|---|---|
| Category Description* | Enter the GL Category Description |
| Sub-Category Description* | Enter the GL Sub-Category Description |
| GL Acc Code* | Use numbers and letters, up to 50 characters. You can use dash (-), underscore (_) or full stop (.) as separator within the code but not at the start of the code. |
| GL Acc Name* | Use numbers and letters, up to 50 characters. You can use dash (-), underscore (_) or full stop (.) as separator within the code but not at the start of the code. |
| System Account* | Import Chart of Accounts Do Not Edit this field. This field will be populated automatically from the System Account Check Sheet. All accounts will be created as posting accounts, unless otherwise specified in the System Accounts Check |
| Add to Chart of Accounts Select Account type from the list (do not add or edit entries in the list)
|
VAT Codes - detailed help
Mandatory fields are marked with an asterisk (*) on the column header.
| Field | Notes |
|---|---|
| Tax Code* | Use numbers and letters, up to 50 characters. You can use dash (-), underscore (_) or full stop (.) as separator within the code but not at the start of the code. |
| Tax Code Description* | Use numbers and letters, up to 50 characters. You can use dash (-), underscore (_) or full stop (.) as separator within the code but not at the start of the code. |
| Rate* | Use numbers only. Up to one decimal place is allowed |
| Goods or Services | Select ‘Goods’ or ‘Services’ option from the drop-down, this will default to services if left blank |
| Standard |
[Only used in the Import Chart of Accounts template, not the Add to Chart of Accounts Template] Enter ‘Yes’ if the VAT code should be flagged as Standard. Only one VAT code can be flagged as Standard. This will default to "No" if left blank. |
| Exclude From VAT Return | Enter ‘Yes’ to exclude transactions with this VAT code from the VAT Return. This will default to "No" if left blank. |
| EU Trade | Enter ‘Yes’ if the VAT code should be set to "EU Trade & Reverse Charge". This will default to "No" if left blank. |
| Partial Reclaim Rate | Use numbers only. Enter Partial Reclaim Rate for a VAT code. |
System Accounts Check - detailed help
This sheet is only used in the Import Chart of Accounts template, it is not included in the add to Chart of Accounts template.
System and Posting Default accounts are used for a number of automated posting routines in the system.
- Accounts marked as "Required" must be linked to a GL code in order to complete your import
- Any errors or missing mandatory accounts will be highlighted as "Invalid" in the "Check" column, link a valid GL code to any "Invalid" accounts before importing your data.
- You can set up multiple GL codes for accounts marked as "Multiple GL Allowed", to do this, copy the existing line from the template and insert copied cells into a new row below.
- Do not edit any fields other than "Link to GL Code"
- Enter the GL Account Codes exactly as they appear in the GL Accounts Sheet
System Defaults Check - detailed help
This sheet is only used in the Import Chart of Accounts template, it is not included in the add to Chart of Accounts template.
System Defaults are used to populate mandatory fields when creating new ledger accounts, i.e. customer, supplier, item and GL accounts.
- The system defaults check sheet shows if you have mapped these accounts correctly - errors will show if you have not.
- System defaults marked as "Required" must be linked to a valid code (GL Account or VAT Code) in order to complete your import
- Any errors or missing mandatory accounts will be highlighted as "Invalid" in the "Check" column, link a valid GL code to any "Invalid" accounts before importing your data.
- Do not edit any fields other than "Link to Code"
- Enter the Code exactly as they appear in either the GL Accounts or VAT Codes Sheets
- GL Codes linked to the default bank accounts (customer and/or supplier defaults) will be created as bank accounts automatically in the system. If Additional Bank Accounts are required, these can be imported using the Bank Importer, or created in the system Banks>Add New Bank
FAQs
I can't see the notes in the error file
You can control the way that notes are displayed in excel - go to File>Options>Advanced (in excel) and scroll down to the display settings. We recommend you select the following option which means that you will see detailed notes on errors when you hover over highlighted cells.
How do I make changes to existing accounts?
Currently there is no way to import changes to existing codes via the data importer. If you need to make changes to a small number of fields, you can do this directly in the system. However if you need to make changes to all accounts, it maybe better to re-import the whole Chart of Accounts (bearing in mind that all data, including any transactions entered will be deleted at this point).
Why can't I see the Chart of Accounts option in the Data Importer?
All of the new onboarding tools are only available for users with the role of Admin. This can be checked by clicking on Actions>Manage Users
