ERPNext.com Frappe Cloud Support Partners Foundation Frappe School

Trying to import Sales Invoices data from excel sheets

Dear All,

I have been trying to import old historical data with different naming series or Invoice numbers and currently I am facing an error as below.
For simplicity sake I have not introduced and tax info the data template is having plain Item,Qty and customer information. Based on mandatory columns required I downloaded the csv template and tried to fill in the data as much and tried to import. Since its sensitive data I can’t share the data template I am using.

Would request your help with this I would really appreciate it. Also needed to know optimal ways of doing this activity in general in ERPNext

Please! may I get some help.

Hello @cpardeshi

Not knowing the exact nature of your data, I assume the following links will help:

Thanks Eugene, issue here to simple import old invoices in the system.

I’ve passed this error, currently am facing issue that ERPNext assings Sales invoice serially, I wanted it have same serial number as old invoices have. so that if somone want to query history data they can do so via the old invoice number.

Regards,
Chetan

If you leave the ID / Name column empty, then the system will automatically assign an ID / Name according to the Naming Series for that DocType. However, you can assign any text string as the ID / Name (aka Primary Key) upon import, even if it does not conform to the Naming Series. Then once imported, any new documents will be assigned an ID / Name according to the DocType’s Naming Series.

2 Likes

Did you manage to do the import?

An alternative is to create a custom field which can store the historical number. Then export the DocType import template containing this new custom field. In this field you can then add the historical record number and import the records without providing an ID / Name, forcing the system to automatically assign it based on the Naming Series. Your users will then have to query the new field to find historical records.

2 Likes

Sounds good! let me try that

@EugeneP : Thanks for your suggestion I was able to import the sales invoices. Also I added a custom field to store old invoice number and added that field to the global search list and was able to search invoice as well…

Things are progressing, appreciate your help! :slight_smile:

Ah… I’m glad I could help. In general, if any of the posts solved your request for assistance then please remember to mark it as Solved / Like.

@EugeneP
I had one question, was able to import the one item invoices.
Any idea how should my template look like for importing invoices with multiple items and will the Grad total be automatically calculated ? :thinking:

The best way to see how to structure your spreadsheet / csv file for importing data is to

  1. Create an invoice with multiple line items on it
  2. Export the import template xlsx / csv format, selecting to contain the data from the multi-line invoice, and selecting the columns of interest (I normally select all)
  3. Investigate how the data looks like in the xlsx / csv
1 Like

Yup it seems i have to manually calculate the Grand Total. its taking each row as a new invoice.

I did create a test invoice with multiple items and trying to import with the same format.

@EugeneP :

Will I have to manually calculate this ?

Getting same error :expressionless: