Hi, team,
I would like to import chart of account created in xls file by my auditors so that its a match.
Is there any provision to do that?
If Yes can some1 walk me through.
Thanks.
Hi Nazeer,
Have you tried Tools-> Import assistant
There is a section where you can import chart of accounts
Over three years and still couldn’t find a good guide. After a headache of trying to figure it out with the limited guidance, I finally got it working. Here’s the English unofficial guide on how to import a nonexistent chart of accounts with along with a working template.
If you want to import an entirely new chart of accounts, the first thing you have to create the “Chart of accounts” by going to Accounting > Setup > Chart of accounts models > and add your model there. Example: Chart of accounts model: “MY-COA-2022”, Label: “This is my COA that I created”, Country: “United States (US)”.
Then, make sure you have enabled the “DATA IMPORTS” module. If you have it enabled, go to Tools > New Import > Chart of accounts > CSV (or Excel, but I only tested CSV) > Choose and Add your file, e.g., “mycoa.csv” > leave everything as is or modify as required and Next Step > Run Import Simulation > and if this is a brand new COA, it should be a success! > Import Data > Enjoy!
Below is a working template
“START OF mycoa.csv”
Chart of accounts* (aa.fk_pcg_version),Accounting account* (aa.account_number),Label* (aa.label),Parent account (aa.account_parent),Custom group (aa.fk_accounting_category),Group of account* (aa.pcg_type),Status* (aa.active),Creation date (aa.datec)
MY-COA-2022,7,Product sales,,,PROD,1,2017-04-28
MY-COA-2022,707,Product sales,ref:7,,PROD,1,2017-04-28
“END OF mycoa.csv file”
Note that if you are making a parent, you leave blank that entry. And here, you only use “ref:” since the ID is not visible to us. The “ref:” should be followed the parent’s “accounting account (aa.account_number)”.
Edit: For some reason if the parent account is larger than ref:99, it does not work. I’m not sure why. I think it might be a bug, since if I manually create an entry with a larger value than 99 (by Accounting > Setup > Chart of Accounts > “+”) , and then I try to import the csv with a ref:>99, the import works.
Edit 2: Not sure if the error mentioned in the original edit is a feature or a bug, but the way to “solve” it is by going to Accounting > Setup > General > Enable “Allow to manage different number of zeros at the end of an accounting account.” After enabling this, I was able to import ref:>99
How to correct import own account chart - including subaccounts (level 2 and 3) ______________________________________________________________________
After 2 weeks of hard work i final got it.. the userguides i have read until now is not 100 correct and will fail - cause it dont tell the whole truth
My installation: Fresh dollibar 22.04 provided by Softaculous (softwareprovided often included in hosting services)
In this guide all modules including [Accounting (double entry)]
A) Generate a csv file (dont forget correct chartset due special characters)
the import file exmaple include wring or misleading information !!!
- The trick is to use correct rowid in column Parent account (aa.account_parent)
Tree structure
L1 - Eiendeler (acc 1) No ref.
L2 - |----Immaterielle eiendeler (acc 10) Ref to level 1 (row id in database)
L3 - |— Forskning og utvikling (acc 1000) Ref to level 2 (row id in database)
| Rowid # | Chart of accounts* (aa.fk_pcg_version) | Accounting account* (aa.account_number) |
| 462 | NS-4102 | 1 |
| 463 | NS-4102 | 2 |
| 464 | NS-4102 | 3 |
| 465 | NS-4102 | 4 |
| 466 | NS-4102 | 5 |
| 467 | NS-4102 | 6 |
| 468 | NS-4102 | 7 |
| 469 | NS-4102 | 8 |
| 470 | NS-4102 | 10 |
| 471 | NS-4102 | 11 |
| 472 | NS-4102 | 12 |
| 473 | NS-4102 | 13 |
If your account chart include 461 rows/lines - the first rowid which will be importert is 462 - since the first dry run (part of import process) allocate the first 461 lines and therefor the first real import will be at line 462.
… Accounting > Setup > Chart of accounts
then first row id will be 1 by default BEFORE any other account charts has been “activated”. Everytime yout “click” [change and load] the rowid will change. so dont “load” any “chart og accounts” before you have imported your own account chart first !!
The latest allocated rowid can be found her
… Home > Admin Tools > About Database > Expand “tables … list”
Take a look at [llx_accounting_account] - column [Increment]. This will show you what next rowid would be. It starts by default at 1
B) Add new Chart of accounts models
… Accounting > Setup > Chart of accounts models
Add missing Account chart model - It has be equal to the account chart model you are using in csv file. In my example NS-4102
C) Ensure account numbers with more than 2 characters can be used
… Accounting > Setup > general (Configuration of the module accounting (double entry))
“Activate” [Allow to manage different number of zeros at the end of an accounting account instead of a fixed length account.]
D) Follow import process and import your new account chart
… Tools > Imports / Exports > New import > Accounting (double entry) … Chart of accounts
Map accounts > ensure correct divider > Click “next” for “dryrun” and if no errors continue the real import ..
This worked for me.. after a lot of testing ……
Please enjoy
A complete “table” as an example - Columns construction
| 1 | Chart of accounts* (aa.fk_pcg_version) |
|---|---|
| 2 | Accounting account* (aa.account_number) |
| 3 | Label* (aa.label) |
| 4 | Parent account (aa.account_parent) |
| 5 | Custom group of accounts (aa.fk_accounting_category) |
| 6 | Group of account* (aa.pcg_type) |
| 7 | Status* (aa.active) |
| 8 | Creation date (aa.datec) |
| Rowid # | Custom group of accounts (aa.fk_accounting_category) | Group of account* (aa.pcg_type) |
| 462 | EIER | |
| 463 | KAPITAL | |
| 464 | INNTEKT | |
| 465 | VAREK | |
| 466 | LONNK | |
| 467 | DRIFTK | |
| 468 | ANDREK | |
| 469 | FINANK | |
| 470 | EIER | |
| 471 | EIER | |
| 472 | EIER | |
| 473 | EIER |
| Rowid # | Label* (aa.label) | Parent account (aa.account_parent) |
| 462 | Eiendeler (balanse) | |
| 463 | Egenkapital og gjeld (balanse) | |
| 464 | Salgs og driftsinntekter (resultat) | |
| 465 | Varekostnader (resultat) | |
| 466 | Lønnskostnader (resultat) | |
| 467 | Annen driftskostnad, av og nedskrivning (resultat) | |
| 468 | Annen driftskostnad, av og nedskrivning (resultat) | |
| 469 | Finansinntekt og kostnader (resultat) | |
| 470 | Immaterielle eiendeler | 462 |
| 471 | Tomter, bygninger og annen fast eiendom | 462 |
| 472 | Transportmidler, inventar og maskiner o.l. | 462 |
| 473 | Finansielle anleggsmidler | 462 |
| Rowid # |
| 462 |
| 463 |
| 464 |
| 465 |
| 466 |
| 467 |
| 468 |
| 469 |
| 470 |
| 471 |
| 472 |
| 473 |
