Custom template for Data Import Tool?

Hi!! This is my first time using ERPNext as a businessperson so I am very new to the software and customization aspects. As per my requirement I would like to use the data import tool to import my BOMs and upload them to my ERP since I find it cumbersome to commit manual data entries from OpenOffice Calc to ERPNext especially when we have many BOMs to register.

I have viewed the Data Import tool GitHub page and have seen the default template available for download and subsequent upload. Although this is nice, I would ideally like to create a custom template that any of our employees can download, fill in and upload (as CSV) to ERPNExt. The advantage of having this custom import template would be that we get a spreadsheet template which is familiar to the user so there would be minimal wastage of time when any user chooses to import the template and export data as opposed to manually feeding data into form fields within the ERP. Is this possible? Any workarounds if not? What are my best options?

TL,DR: Need custom Data import/export template

Thanks!

Unfortunately custom templates are not allowed for now, you will have to download the template. But you don’t have to download it everytime. Just download it once and distribute to your team, they can add records and upload.

I see. I wish we could set up a custom template that could be used for import/export. My problem is not necessarily the import/export part of the data but the “format” while importing/exporting.

It would have been great if I could:
Import template --> Add data --> Export data --> Use code or scripting of some kind to autofill fields in ERPNext with values from spreadsheet.

The only option now is to either manually feed in data or use the available default (both of which would lead to logistical hassles because our employees are so used to OUR default templates). Is there any suggestion on what my options are and what I can do? Can this feature be requested for the next version of ERPNext?

Thanks!

Hello,

I understand your problem. But ERPNext has a certain technology internally when importing stuff. I worked very much with it while designing data transformation jobs with Talend DI.

At first I gathered my required fields and exported a template with experimental data from ERPNext. Then I designed the transformation from the source data to the CSV data.

Then I concateneated the Header with the CSV data and imported successfully to ERPNext.

So you could do it by yourself. You make a convenient Excel sheet for your employees. This sheet may have a function to generate the import file for ERPNext. So your employees have the comfort and the dirty work can be done by Excel.

With a little bit of hacking may be you can automate the import via REST-API.

1 Like

Thanks!! That gives me a little bit of clarity on what my options are.

Hi…

If the custom templates does not meet my requirements… or for that matter if the mandatory fields are such that i do not have in my data then what shall i do…

This is a very old thread but your have two options

  1. Edit the doctype and not select the mandatory checkbox. However, if this is a core doctype, then this is not recommended as it may cause issues in the future upgrades
  2. Populate dummy data into the fields.

What specific doctype and field is mandatory but you do not have any data for?

I did the same…was trying to import my customer bill register into ERP so that I can bulk upload the sale invoices. However it gave an error that due date is mandatory field. Also fields like exchange rate etc are not of use to me.

Again we are in service industry and I wish to add customer ID to every customer so that a unique ID of customer is created. Also I wish to add details like job card number, service type etc to the data base for more effective analysis when so needed…

kindly support

Right.

So ERPNext is essentially a relational database so for each customer you have to almost following the standard process. That is create item for selling), selling price, add a customer, raise sales invoice etc…

My suggestion is before importing any data is to following the process through for a single customer so you know what your requirements will be, i.e. you will need customer, items setup, price list setup etc.

In terms of analysis… how do you want to do your analysis? Is it easier for you to have specific items which contain all the details (for example, service type etc) or do you want to have these on a per invoice level.

If its via items, then set these up.

If its by invoice, then setup extra custom fields for Sales invoice doctype.

Now, if you decided on using items, then you will need to rearrange your data import data so it has item code and quantity etc before importing it in.

If you are going via just invoice, then you will need to populate the excel upload with the extra information.

In terms of currency conversion, simply use your base currency and use rate as 1.

If you go into the Setup>>DocType, you can look at each form and what is mandatory.

Hope it makes sense?

probably i am not able to explain my specific requirements…

can i speak to you over phone anytime…?

I take your advise…i will go through creating a sale invoice for a single customer…

again We are in service industry and we do not have any price lists etc. Neither we are looking for maintaining inventory in ERP. We are doing all invoicing from DMS and All MRNs are again generated from DMS. ERP is used only for the purpose of financial transactions.

will be glad if we can speak sometime

For ERPNext, Item is mandatory so create a single item called service and disable “maintain stock”.

Then add a couple of extra fields for the details you want and then reimport.

If you get stuck, send me some dummy data and I will knock up some specific instructions for you.

I am in the UK and am pretty busy during the days so dont really have time for a lengthy chat. But i can knock up some notes…

Thanks.