Data Import Tool

The Data Import Tool lets you import records from a CSV/Excel file.

Data Import Tool is an easy way to upload (or edit) bulk data (especially master data) into the system.

To begin importing data, go to:

Home > Data Import and Settings > Import Data

Before using Data Import ensure that you have all of your data ready.

1.Insert New Records

Let's say you want to import the list of Customers from your old system into ERPNext. The first step is to download a template in which we can enter our data.

1.1 Download the template

1. Go to Import Data and Add Data Import on the top right 

2. Select the Document Type. ( Ex: If you want to import data about your Customer list => Select " Customer".

3. Import Type : Select " Insert New Record"

4.Save. After Saving the system will show you as below : and please Click on "Download Template"

5. Select File Type : Excel / CSV > Check files want to Insert > Export.

1.2 Entering Data in the Template

1. ID (Optional)
2. Fill all information as Customer Name, Customer Type, Customer Group, Company, Territory....
3. Save.

When you import this template, each row will make a Customer record in the system.

1.3 Importing the Template

1. After updating your template file, go back to the Data Import form and attach the file by clicking on the Attach button.
2. Choose Source. ( Ex : If your template file is store in your Computer -> Click on "My Device"
3. Select the template file and click on Upload.
4. After the upload is successful, click on Start Import.

If there are any errors in your template, they will be shown in the Warnings section. The warnings will be categorized by Row or Column with their number so that you can easily track them down in the template and resolve them. You must resolve all the warnings before you can import the data.

After you have resolved the warnings, click on Start Import again to import the data. On successful import of the data, you'll see a log of each record that was created in the Import Log section.

2.Updating Existing Records

Let's say you want to update Customer data in bulk in your system. The first step is to download the template with the data.

2.1 Download the Template

1. Go to Import Data and Add Data Import on the top right 

2. Select the Document Type. ( Ex: If you want to update about your Customer => Select " Customer".

3. Import Type : Select " Update Existing Records "

4.Save. After Saving the system will show you as below : and please Click on "Download Template".

5. Select File Type : Excel / CSV > Check files want to Import > Export.

2.2 Updating Data in the Template

Your downloaded template will look something like this :

While exporting records for updating them, ensure that the ID column is exported and is untouched. The values in the ID column are used to identify the records in the system. You can update the values in other columns but not in the ID column. If you remove some child table row, the system will consider that the row is supposed to be deleted.

2.3 Importing the Template

Go back to point "1.3 Importing the Template" in this Document.

3.Importing Child Records

Data in ERPNext is stored in tables like a spreadsheet with columns and rows of data. Each form likes Sales Order has multiple fields like Customer, Company, etc. It also has tables like the item table, tax table, etc. In Data Import, the set of fields in the Sales Order are treated as the main table and the rows inside the child table (item table) are treated as the child table for data import.

Each form in ERPNext can have multiple child tables associated with it. The child tables are linked to the parent tables and are implemented where there are multiple values for any property. For example, an Item can have multiple prices, a Sales Invoice has multiple Items, Taxes, and so on.

When you export a document with child tables, for e.g., each child row will appear on a separate row but it is associated with a single parent row. The subsequent values in the parent columns will remain blank. You must ensure that this order is not broken when you are importing them via Data Import.

4.Import Options

4.1 Import from Google Sheets

You can also import data from Google Sheets. Import your template in Google Sheets and enter the data. Make sure the Google Sheet is public. You can test this by opening the Google Sheets URL in an incognito browser window.

4.2 Submit After Import

In ERPNext document types are mainly of two types - masters and transactions. The masters are records like Customer and Task which can only be saved not submitted. Transactions like Sales Orders, Purchase Invoices are submittable documents and can be submitted.

When you select a submittable document type for Import, you can tick Submit After Import to submit the document after it is imported.

4.3 Don't Send Emails

Let's say you have created a Notification in your system and whenever a Lead is created an email is sent. Now, if you are bulk importing Leads then a lot of emails will be sent, which may not be desired. You can disable this option to avoid sending emails.

5.Addional Notes

5.1 Upload Limit

There is no hard limit on the number of records that can be imported. But you must try and upload only a few thousand records at a time. Importing a large number of records might slow down the system considerably for the users that are using the system.

5.2 CSV Files

A CSV (Comma Separated Value) file is a data file that you can upload into ERPNext to update various data. Any spreadsheet file from popular spreadsheet applications like MS Excel or Open Office Spreadsheet can be saved as a CSV file.

If you are using Microsoft Excel and using non-English characters, make sure to save your file encoded as UTF-8.

For older versions of Excel, there is no clear way of saving as UTF-8. So save your file as CSV, then open it in Notepad, then save as “UTF-8”. (Or upgrade your Excel!)

6.Related Topics

1. Charts Of Accounts Importer
2. Data Export