Import chart of accounts issue

bynicolas · October 6, 2020, 4:33am

Hi,

I am trying to import a complete chart of accounts using the importer, but for some reason I always get errors and cannot complete the process.

So I’m trying to insert new records, I’ve tried of course downloading the template and modifying it accordingly.

I tried importing 1 account at a time to see if it works. Nope

I’ve tried CSV and XLS files, same outcome.

I’ve tried updating existing records from an exported dataset… nope.

I’ve even tried exporting and reimporting the same data with no success.

I get errors like COLUMN 6 (Parent Account) The following values do not exist for Account: Actifs non-courants, Actif, Banque, Dépenses, Actif à court terme, Passif, Encaisse, Immobilisations

Which seems to point out that the importer can’t find the parent account, but in my import list, I am creating the parent before importing the child. And I’m checking for typos and other obvious errors.

I am using account numbers, so I checked the database and tried to rewrite the parent_account using the syntax Acc# - AccName - CieAbbr and it’s not working.

So I’m pretty sure I am missing something but I can’t find anything in the docs that I’m doing wrong or in the forums related to my issue and I’m pretty much out of ideas as to what to look for next to make this work!

Thanks for your suggestions/explanations

Joseph_Marie_Alba1 · October 6, 2020, 4:44am

Are you using Chart of Accounts Importer or Data Import of Accounts DocType?

bynicolas · October 6, 2020, 4:46am

I’ve tried both

Joseph_Marie_Alba1 · October 6, 2020, 4:56am

With Chart of Accounts Importer, you use the Account Name (without Number and company initials) as parent. The tricky part is you need to specify some account types of Cash, Receivables, etc.

I find using Data Import easier to do. But you cannot do it all at one because the Parent Account needs to be defined in the chart of Accounts before you upload Child Accounts.

So, it may require several Data Imports, but you eventually get there.

Import the 5 root Accounts (Asset, Liability, Equity, Income, Revenue)
Download the Root Accounts and copy the Name (Number - Account Name - Company Initials) and use it on Parent Column, Import Child Accounts of the Root Accounts.
Repeat process until you imported all accounts

bynicolas · October 6, 2020, 5:03am

Ok thanks, I’ll try that and report back!

bynicolas · October 6, 2020, 5:43am

Ok, when importing root accounts, what should be the value of the parent_account attribute? I’ve tried the company name but it doesn’t work.

**If I put nothing, it complains that parent_account is a mandatory field! isn’t it a catch 22?

EDIT: Ok, I just realized that the company name goes into the parent field for root accounts, but not sure if I would be able to import root accounts since parent_account is still a mandatory field… can we import root accounts or they should be manually created before import?

bynicolas · October 6, 2020, 6:06am

All right!

I creating the root accounts directly from the web interface, then the import succeeded using Data Imports and like you suggested, 1 level at a time.

As a side note, is this going to be fixed in the future, seems like importing this way is counter intuitive and time consuming… Just wondering!

In any case, I’m glad I can at least make it work for now!

Thanks for your help

Joseph_Marie_Alba1 · October 6, 2020, 12:54pm

I’m glad. Thanks.

Ysedky · May 4, 2021, 4:26pm

Hi All,
I went through the whole process as discussed and always getting

Parent Account Missing
Please make sure the file you are using has ‘Parent Account’ column present in the header.
Alternatively, you can download the template and fill your data in.

I have a Parent Account column and I used the 7 columns as defined by the importer template. Account Name,Parent Account,Account Number,Parent Account Number, Is Group,Account Type, Root Type

I’m using ERPNext: v13.2.0 / Frappe: v13.2.0

Any Ideas please?

Joseph_Marie_Alba1 · May 4, 2021, 4:32pm

Can you show a screenshot of the worksheet you are trying to import into chart of accounts? I think I know what is causing the problem - it is your parent account name.

bynicolas · May 4, 2021, 8:18pm

Also make sure that your CSV file column header are properly mapped to the field in the DB… on the import page, you can manually edit that if need be, I know it caused me some hiccups, since even if the name was correct, it wasn’t properly mapped so ERPNext couldn’t find the parent in the DB

Ysedky · May 5, 2021, 10:11am

Joseph_Marie_Alba1 · May 5, 2021, 11:17am

You cannot do it this way. First, there is no Parent Account Number. You see, ERPNext does some magic on the Account, which I think is quite confusing. The final account name is a combination of Account Number - Account Name - Company Abbreviations.

What you can do is Enter the Root Account first - Asset, and download and see what happens to the Account Name. You will be surpised that the Assets Account Name, based on your configuration is 1 - Assets - CMP (where CMP is the Company Abbreviation. Then you use this Account Name as the Parent Account. Etc.

Review the posts from the beginning of this topic. If you need more help, I will try my best.

ponyooooo · May 24, 2021, 5:48am

We are also experiencing this issue on

ERPNext: v13.3.0 (version-12)
Frappe Framework: v13.3.0 (version-12)

do you know what can be the reason that the importer tool is not again working on the new version of the erpnext? and are there any work around so we can import the chart of accounts?

A.Mas · June 2, 2021, 5:51pm

Remove these 5 lines from your code and it will work

https://github.com/frappe/erpnext/commit/5541aeaa82f520881ad10eee4eed1df5042ed3d8#diff-5b59f3a16e0b23d23b478ff4a9e6fd1157018e8cadf4a385974e39508f13c558

szufisher · June 3, 2021, 2:00am

fixed by this PR fix: chart of accounts importer always error by szufisher · Pull Request #25882 · frappe/erpnext · GitHub

ponyooooo · June 7, 2021, 10:37pm

@szufisher I already updated my erpnext to the version

ERPNext: v13.4.1 (version-12)
Frappe Framework: v13.4.1 (version-12)

but I still get a pop up message “Parent Account Missing”.

I also tried uploading the sample format and it also gets that message.

Do I need to enable the developer mode so that this works? Also, upon checking, the parent account field is in mandatory so I am not sure if it also add ups on the causes of why the import button is not showing.

szufisher · June 8, 2021, 1:14am

only merged to v13-hot-fix yet, wait till merged to released version or adapt the code per the PR in your local codebase.

ponyooooo · June 8, 2021, 8:35am

@szufisher we tried commenting out this lines

if not hasattr(account, "parent_account"):
		msg = _("Please make sure the file you are using has 'Parent Account' column present in the header.")
		msg += "<br><br>"
		msg += _("Alternatively, you can download the template and fill your data in.")
		frappe.throw(msg, title=_("Parent Account Missing"))

but nothing happens.

Then if we remove those lines, another message appears, regarding

account_types_for_group = ["Bank", "Cash", "Stock"]

but this account types are already included in my csv file. I tried using the same file for the v12 erpnext, and it accepts it.

Do you have any work around I can do?

Thank you.

Ysedky · June 8, 2021, 9:32pm

I’ve tried many solutions and here is the one that let me pass this barrier finally (thanks to Joseph_Marie_Alba1 :

I made the following

1- Stored the COA that I want to upload in an Excel sheet (say “original” sheet) as per the following format: (Note that the column “ID” is left blank as we will “add” new records.
PS: Make sure you add to the Parent Account the account number "NN - " and the Company Abbreviation "CC - ". Example: The account “Fixed Assets” (number 11) has a parent account “Assets” has a number (1) and the company abbreviation is “CC”; Then the Parent Account for “Fixed Assets” will be equal to: " 11 - Fixed Assets - CC "

Account Name	Parent Account	Account Number	Is Group	Root Type	Account Type
Assets		1	1	Asset	Fixed Asset
Fixed Assets	1 - Assets - CC	11	1	Asset	Fixed Asset
Furniture and Equipment	11 - Fixed Assets - CC	1101	1	Asset	Fixed Asset
Furniture	1101 - Furniture and Equipment - CC	11010101	0	Asset	Fixed Asset

2- Created the five Root Accounts manually using the User Interface.

3- Added new sheets to the Excel workbook, for this example let’s use the following names: “sorted”, “ROOT”,“level1”,“level2”,“level3”,etc… according to how many sub-levels exists under the “root” level.

4- Made a “Copy” and “Paste - Link” from the original sheet (7 columns) to the sheet named “sorted”. The Paste-Link will assure that the “Account Names” in the created sheets will be updated if you change in the “original” sheet.

5- Performed an “Ascending Sort” in the “sorted” sheet based on the field “Account Number”.

6- Made a Copy / Paste of the “Level 1” Accounts (the ones with 2 digits in Account Number) to the sheet “level1”, Copy / Paste of the “Level 2” Accounts (the ones with 4 digits in Account Number) to the sheet “level2”, and so.

7- Exported each sheet “level1”, “level2”, level3", etc… to separate Excel sheets and named the files Level1-To-Import.xlsx, Level2-To-Import.xlsx, etc…

8- I used the import-data tool (not the CoA importer tool) and started to import each file starting from Level1-To-Import.xlsx till the last file.

9- The import-data tool will verify the data, I had to change some old accounts numbers that were used in Default Accounts to be able to upload some accounts (like Cash).

10- All accounts got imported correctly.

11- Next step is to set the Default Accounts in “Company” then delete the old “Standard Accounts”.

This setup worked fine with the following environment:

Variable	Actual Value Used
ERPNEXT Version	v13.2.0 (version 13)
Frapper Framework	v13.2.0 (version 13)
Company Name	hidden***
Company Abbreviation in Setup Wizard	CC
Country	UAE
Default Currency	AED
Chart of Account used in Setup Wizard	Standard Template

I hope this helps.