The accuracy of Analysis and Reports generated by an ERP lies in the abundant amount of data it consumes from the user. ERP will store and process the complete database of an organization to arrive at its conclusions. But sometimes we have to import a new set of data into the ERP to update older data sets or for some other purposes. In Odoo, the user can import any kind of data in different formats. By default, Odoo allows data import on Odoo's any business object using Excel (.xlsx) or CSV (.csv) formats. The user can easily import details like contacts, products, bank statements, journal entries, sales orders etc. to Odoo in any of the above formats.
Here I will explain the easiest way to follow for the successful importing of your valuable data. Before we move on to the importing we need to know about data exporting from Odoo. Don’t worry we already have a blog on exporting in Odoo. You can refer to our previous blogs.
To import a set of data into Odoo, you just need to select the respective data file in .csv or .xlsx format and upload it. But the process may invite errors while mapping the fields of the file and Odoo database. To avoid such errors, the safest way is to export a sample data set of the model (eg: sale order) you wish to import data. It will give you the template with correct data fields. Populate the exported file with new data and import as usual. With this approach, you can easily map the fields and the possibility of data errors are minimized.
Here I am going to explain this with an example data of Sale orders.
Steps to follow :
1. Export a Sample Sale order
- Sales > Sale order > Action > Export
- Export the important details you need.
- Pick all the mandatory fields.
- Export the contents in .csv format
2. Edit the contents of the exported ‘.csv’ file and populate it with new data.
This will be the format of the exported data file. Add the new data to the corresponding column.
If you want to import sales orders having several order lines; for each order line, you need to reserve a specific row in the CSV file. The first order line will be imported on the same row as the information relative to order. Any additional order lines will need an additional row that does not have any information in the fields relative to the order.
3.Importing of Data
You can skip the previous two steps because it is not important. You can avoid the chances of errors if you follow those steps.Instead of these steps, you can directly import your data file onto odoo and map to its corresponding columns.
Note: All the relational field such as customers, products, users etc.. need to be imported before importing Sale orders. This is the same for all objects.
Sales > Sale Orders > Import
When we import our data, Odoo might not be able to map it automatically if the label doesn't fit any of the fields in Odoo database. Don't worry! You can map new columns manually when you test the import. Search the list for the corresponding field. If we don’t map correctly, there will be the chance of getting errors.
Database ID and External ID
Some fields define a relationship with another object. For example, suppose the country of a contact has a link to a record of the 'Country' object. So when you import such fields, Odoo will have to recreate links between the different records. To avoid complexities in mapping, Odoo provides 3 mechanisms. You must use one mechanism per field you want to import.
For example, to reference the country of a contact, Odoo proposes you 3 different
fields to import:
- Country: The name or code of the country
- Country/Database ID: The unique Odoo ID for a record, defined by the ID PostgreSQL column
- Country/External ID: the ID of this record referenced in another application (or the .XML file that imported it)
For the country Belgium, you can use one of these 3 ways to import:
- Country: Belgium
- Country/Database ID: 21
- Country/External ID: base.be
According to your need, you should use one of these 3 ways to reference records in relations. Here is when you should use one or the other
- Use Country: This is the easiest way when your data comes from CSV files that have been created manually.
- Use Country/Database ID: You should rarely use this notation. It's mostly used by developers as its main advantage is to never have conflicts (you may have several records with the same name, but they always have a unique Database ID)
- Use Country/External ID: Use External ID when you import data from a third-party application.
When you use External IDs, you can import CSV files with the "External ID" column to define the External ID of each record you import. Then, you will be able to make a reference to that record with columns like "Field/External ID". The following two CSV files give you an example of Products and their Categories.
When the Mapping is complete, you can validate the data. You can import the data if there are no errors.
Now, you can go with the new data