English Chart of Accounts

Here is a way to get English Chart of Accounts as Dolibarr does not include one.
The way I’m creating an English COA is to translate the PCG99-BASE - the base french accountancy plan’s French labels to English. The rest of this plan remains the same.
WARNING: BEFORE ATTEMPTING any of the steps below please BACKUP Dolibarr or backup your Dolibarr database from your control panel. I will not be responsible for any system malfunctions.
So here are the steps you need to take. Make sure you carry out the procedure step by step, do not miss any step or it wont work.
STEP 1:
Run the following SQL Queries using phpMyAdmin which can be accessed depending on how you are running Dolibarr:
If running locally (from local computer) then in your browser type: localhost/phpmyadmin or from the localhost main page look for phpMyAdmin.
If running from a hosted environment then in the control panel under Database section look for phpMyAdmin.
On the left hand side make sure the Dolibarr database is selected then look for SQL Tab.
Copy and paste the following query in the box where you clicked on SQL Tab. Make sure it has: Run SQL query/queries on database name_of_dolibarr_db:
CREATE TABLE your_temp_table LIKE llx_accounting_account;
Click on Go button to run the query. If everything ran succesfully you should have this message : MySQL returned an empty result set (i.e. zero rows). (Query took 0.1491 seconds.) with a green tick

STEP 2:
Download the attached file English Chart of Accounts.csv
English Chart of Accounts
making sure the database selected is the Dolibarr database, run the query below:
SELECT * FROM your_temp_table click on Go button
Then look for Import tab. Once you click on Import Tab make sure it says: Importing into the table “your_temp_table”
Click on choose file then select the downloaded English Chart of Accounts.csv file. Under Format choose CSV. In the Columns separated with box type: ;
Click on Go button. If its successful you will get a tick and a number of rows will be inserted in the your_temp_table created earlier.

STEP 3:
Copy and paste the following query then click on Go button:

UPDATE llx_accounting_account SET fk_pcg_version = ‘ENG-BASE’ WHERE fk_pcg_version = ‘PCG99-BASE’

The above query renames PCG99-BASE to ENG-BASE
Then run the following query making sure to Uncheck -> ‘Enable foreign key checks’
UPDATE llx_accounting_system SET pcg_version=‘ENG-BASE’,label = ‘The base accountancy plan in English’ WHERE pcg_version = ‘PCG99-BASE’

STEP 4:
Copy and paste the following query:

UPDATE llx_accounting_account
INNER JOIN your_temp_table on your_temp_table.rowid = llx_accounting_account.rowid
SET llx_accounting_account.label = your_temp_table.label;

Click on Go button
If its successful you will get something like this: 340 rows affected. (Query took 0.1315 seconds.)
Now check on your Accountancy setup if all is done properly you should now see in the drop down list box: ENG-BASE - The base accountancy plan in English
Voila!
Phew! this was quite long!
Give a big thank you if I made your DoliLife easier!! :happy:

2 Likes

Following error occurred in response of 3rd Step
"SQL query:

UPDATE llx_accounting_account SET fk_pcg_version = ‘ENG-BASE’ WHERE fk_pcg_version = ‘PCG99-BASE’
MySQL said: Documentation

#1452 - Cannot add or update a child row: a foreign key constraint fails (dolibarr/llx_accounting_account, CONSTRAINT fk_accounting_account_fk_pcg_version FOREIGN KEY (fk_pcg_version) REFERENCES llx_accounting_system (pcg_version)) "

Mudassar,
You are getting that error because you missed this:
In Step 3:
Then run the following query making sure to Uncheck -> ‘Enable foreign key checks’
There is a check box below the SQL query box called ‘Enable foreign key checks’ you have to uncheck it then the query will run.

You have to run the query making sure to Uncheck -> ‘Enable foreign key checks’.

From the directions it isn’t clear that you do that when you run
UPDATE llx_accounting_account SET fk_pcg_version = ‘ENG-BASE’ WHERE fk_pcg_version = ‘PCG99-BASE’;

I questioned it when I read through this the first time thinking it was in the next query that it needed to be unchecked but the error
#1452 - Cannot add or update a child row: a foreign key constraint fails (dolibarr/llx_accounting_account, CONSTRAINT fk_accounting_account_fk_pcg_version FOREIGN KEY (fk_pcg_version) REFERENCES llx_accounting_system (pcg_version)) "
kind of tells you.

Hi Tek,
Yes it was an oversight in that part. I shall correct it with another post.

Here is a way to get English Chart of Accounts as Dolibarr does not include one. This is a revision on the earlier post which had a small mistake.

The way I’m creating an English COA is to translate the PCG99-BASE - the base french accountancy plan’s French labels to English. The rest of this plan remains the same.
WARNING: BEFORE ATTEMPTING any of the steps below please BACKUP Dolibarr or backup your Dolibarr database from your control panel. Although the procedures below will not do any serious damage to your system, you’d rather be safe than sorry. I will not be responsible for any system malfunctions.
So here are the steps you need to take. Make sure you carry out the procedure step by step, do not miss any step or it wont work.

STEP 1:
Run the following SQL Queries using phpMyAdmin which can be accessed depending on how you are running Dolibarr:

- If running locally (from local computer) then in your browser type: localhost/phpmyadmin or from the localhost main page look for phpMyAdmin.
- If running from a hosted environment then in the control panel under Database section look for phpMyAdmin.
Once you are in PhpMyAdmin:
On the left hand side make sure the Dolibarr database is selected then look for SQL Tab.
Copy and paste the following query in the box where you clicked on SQL Tab. Make sure it has: Run SQL query/queries on database name_of_dolibarr_db:

CREATE TABLE your_temp_table LIKE llx_accounting_account;
This creates a table called your_temp_table
Click on Go button to run the query. If everything ran successfully you should have this message : MySQL returned an empty result set (i.e. zero rows). (Query took 0.1491 seconds.) with a green tick

STEP 2:
Download the attached file English Chart of Accounts.csv
English Chart of Accounts
Making sure the database selected is the Dolibarr database, run the query below:

SELECT * FROM your_temp_table;
click on Go button
Then look for Import tab. Once you click on Import Tab make sure it says: Importing into the table “your_temp_table”
Click on choose file then select the downloaded English Chart of Accounts.csv file. Under Format choose CSV.
In the Columns separated with box type: ;
Click on Go button. If its successful you will get a tick and a number of rows will be inserted in the your_temp_table created earlier.

STEP 3:
Copy and paste the following query making sure to Uncheck -> ‘Enable foreign key checks’ then click on Go button:

UPDATE `llx_accounting_account` SET `fk_pcg_version` = 'ENG-BASE' WHERE `fk_pcg_version` = 'PCG99-BASE'

The above query renames PCG99-BASE to ENG-BASE
Then run the following query making sure to Uncheck -> ‘Enable foreign key checks’

UPDATE `llx_accounting_system` SET `pcg_version`='ENG-BASE',`label` = 'The base accountancy plan in English' WHERE `pcg_version` = 'PCG99-BASE';

STEP 4:
Copy and paste the following query:

UPDATE llx_accounting_account INNER JOIN your_temp_table on your_temp_table.rowid = llx_accounting_account.rowid SET llx_accounting_account.label = your_temp_table.label;

Click on Go button
If its successful you will get something like this: 340 rows affected. (Query took 0.1315 seconds.)
Now check on your Accountancy setup if all is done properly you should now see in the drop down list box: ENG-BASE - The base accountancy plan in English
Voila!
Phew! this was quite long!
Give a big thank you if I made your DoliLife easier!! :happy:

1 Like

Hi ariffidaali
I am getting below error in step2:
Error

SQL query:

INSERT INTO your_temp_table VALUES (‘rowid’, ‘entity’, ‘datec’, ‘tms’, ‘fk_pcg_version’, ‘pcg_type’, ‘pcg_subtype’, ‘account_number’, ‘account_parent’, ‘label’, ‘fk_accounting_category’, ‘fk_user_author’, ‘fk_user_modif’, ‘active’)

MySQL said: Documentation
#1366 - Incorrect integer value: ‘rowid’ for column ‘rowid’ at row 1

Could you support me.
Thanks

Hi,
Are you on localhost (local computer) or a hosted environment?
I think the problem your getting is because of how MySQL is configured. It is in strict mode so if you have access to mysql ini file then:
my.ini file(Mysql)
Uncomment the following line
Code:
#sql-mode=“STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

Yes dear, I am on local host.

I am weak on mysql db, I am using phpmyadmin.

Thank you very much, I have solved the issue by checking the box ( Do not abort on INSERT error) and deleted the first row after import.

2 Likes

I did the same thing as DLITS. Forced the import, then went to “Browse” and deleted the first line where it shows the column values a second time.

I don’t know much about importing data. When copying llx_accounting_account, are the column labels copied too?Is it possible to remove the first line of the imported .CSV because the different table column labels are already there?

Thanks, Jonathan

Hi RSEinc,
Yes column labels are also copied so no need to have that row when importing. Its there in the beginning so you know what data your dealing with.

Dolibarr Version 7.0 has the English Chart of Accounts by default :happy: They used the llx_accounting_account that was created in this tutorial :happy:

Hi, please can you upload the CSV file again, the previous file was deleted from the upload site.
Thanks in advance

Hi,

Dolibarr Version 7.0 or above has the English Chart of Accounts by default

1 Like

Hello,
Although Dolibarr 7.0 and above has English COA, there could be users still using older versions therefore I have attached the CSV file as zip

Pièces jointes :

Hi! I am new to Dolibarr and currently setting up the accounting module.

I am using Dolibarr version 12.0, I found there’s only one chart of accounts that uses English, it’s GB (Great Britain). However, after selecting that COA, the defaults are empty except the account for users third party.

Could you please help me filling the default accounts by using this English chart of accounts?