Dolibarr Create Indian COA
Notes:
(1) Use the uploaded file here
(2) Open csv file in Excel
(3) Use Ctrl+H to find MS and replace with “Indian COA” or whatever
Credit to afiifidali
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;
TryIT
CREATE TEMPORARY 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
// done
STEP 2:
Download the attached file English Chart of Accounts.csv
English Chart of Accounts
(i) Change file name to Malaysia Chart of Accounts
(ii) Ctl+H in Excel and change ENG-BASE to MS
(iii) Making sure the database selected is the Dolibarr database, run the query below:
(iv) copy/paste in SQL the statement: SELECT * FROM your_temp_table;
(v) click on Go button // it should display 343 rows
(vi) Then look for Import tab. Once you click on Import Tab make sure it says: Importing into the table “your_temp_table”
(vii) Click on choose file then select the downloaded English Chart of Accounts.csv file. Under Format choose CSV using Load Data.
(viii) In the Columns separated with box type: ;
(ix) 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.
// done
STEP 3:
(i) 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 = ‘MS’ WHERE fk_pcg_version = ‘ENG-BASE’;
The above query renames PCG99-BASE to MS-BASE
Then run the following query making sure to Uncheck → ‘Enable foreign key checks’
UPDATE llx_accounting_system SET pcg_version=‘MS’,label = ‘Malaysian COA’ WHERE pcg_version = ‘ENG-BASE’;
// done
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;
// Uncheck ‘Enable foreign key checks’ → also results in 0 rows but
// in dolibarr all 343 rows shown correctly
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: MS-BASE - The base accountancy plan in English
// error 0 rows created
Voila!
Phew! this was quite long!
Give a big thank you if I made your DoliLife easier!!
Download the file below:
CSV file
