OpenERP - Importing and Exporting Data in CSV Format

Vishnu Devaraj's picture

Data migration has always been a key issue when people wish to switch their operations into a new system. In OpenERP there are sevaral methods to address the issue. If you are able to collect the data in a spread sheet without much efforts, 'CSV Import', will be a beter solution for data migration. It doesn't require much technical expertise and can be achieved from the client side. Similarly data can also be exported easily as a CSV files. The CSV file format is a text format compatible with most spreadsheet programs (such as OpenOffice Calc and Microsoft Excel), and is easily editable as a worksheet. The first line contains the name of the fields in the form. All the subsequent lines are data, aligned in their respective columns.

 In GTK Client you can find two options from the top menu 'Form ‣ Import data' and 'Form ‣ Export data', for import and export of data

Exporting Data

Go to Sales ‣ Address Book ‣ Customers for a list of partners, and select the records to export, Go to Form ‣ Export data. A pop-up window appaeras as shown in the below figure.

 

In the left column it dislpalays a lsit of available fields. Here we are going to select just 3 filelds only

  • Name (character filed)

  • Company (many2one field)

  • Contacts/Contact Name ('Contacts' is a one2many field and 'Contact Name' is a character field inside the one2many). Label : Partner Contacts

 

From options choose 'save as CSV' . Press OK and save the file with '.csv' extention. The below figure shows the csv file we generated.

 

 

If you do not wish to export your data just yet, or would like to use the same fields for future exports, you have the option to save these settings. To do that, expand the 'Predefined Exports' at the top of the pop-up window and click Save List and give your export a name.

 

Importing Data

Lets do the import in Customers itself. Prepare a csv file to import. Use our exported file as a template for the new file.

 

 

The new file contains some details of 2 new partners. The second partner has two contacts. Now start the import. Go to Sales ‣ Address Book ‣ Customers. Select Form ‣ Import data. A pop-up window appaeras. Select the file to import. Press the 'Auto-Detect' button. If the field names in the csv (first row) are same as the field names in the OpenERP form, 'Auto-Detect' will work. Alternatively we may add fields to be imported, manually from the list of “All fileds” in the left column of the pop-up window. You must include every field that is colored blue because those fields are required (unless you know that they get filled by default with an appropriate value)

 

 

Once fileds are selected press the Ok buttton to complete the import. You will get a dialog box showing that you have imported 2 objects, and you can see the new partners and partner addresses when you refresh the list on-screen.

 

Successful Import

 

Imported Partners

 

Dealing with Complex Database Structures

If you are importing values into a 'many2one' field, those particular values must already be present there in the corresponding relational model. For example, in our import, 'Company' is a many2one field. The value imported was 'OpenERP S.A.' , which was already present in the company list. Otherwise import won't be success and terminate with an error message. So, in practical, you have to prepare separate csv files for 'companies' and other many2one fields in customer and import them before importing customer details. Similar is the situation if the the field type is many2many'

'one2many' fields are treated differently here. In our import, we added 'Partner contacts' in Customers using the column 'Contacts/Contact Name' . Here 'Contacts' is the one2many filed in Customers. The second part 'Contact Name' is a field inside the one2many. While creating a csv file we specify values of fields inside the one2many as separte columns(like 'Contacts/E-Mail', Contacts/City', etc..). If there is more than one entry in a one2many field(Like 2 contacts for a partner), they will be added down by down in corresponding columns against a 'blank' value in all other columns(except if there are values in anyother one2many field) . That is what we did for 'Contacts/Contact Name' in our import

Anonymous's picture

Great thanks a lot!

Great thanks a lot!

Anonymous's picture

What if we want to import

What if we want to import partners with their total receivable and total payable and what if want to import quantity available when importing products. I tried a lot and could not do the same yet. Also I used both import compatible export and export all data export types. If anyone has done RnD on this please reply.

Anonymous's picture

I get the following error

I get the following error whenever I try to import partners and products
ValueError: No such external ID currently defined in the system: __import__.res_partner
ValueError: No such external ID currently defined in the system: 20__export__.product_product_456.
I successfully imported my taxes.

Anonymous's picture

I customize a asset

I customize a asset management functions. However, when I perform import from csv file into the system, the system does not display the data import.
steps are as follows:
Choose imoprt, select file to import, and then select File format option, then click validate. The notification system "Everything Seems valid.". After that I click the Import button. And then the system turn to asset management.
However, the system does not display the data import.
Help me!

pierrecogitae's picture

Another important missing

Another important missing point: how do you read the import wizard from the web interface?

Anonymous's picture

This is great tutorial but

This is great tutorial but it's missing one thing: how to add predefined export as a scheduler job. I know where scheduler is, but I have no idea what should i put into 'Action to trigger' object/function. Any help is welcome :)

Douglas's picture

I am now not positive where

I am now not positive where you are getting your information, however great topic. I must spend a while learning much more or understanding more. Thanks for wonderful information I used to be searching for this information for my mission.

Girish Joshi's picture

Hi, Can anyhelp help in

Hi, Can anyhelp help in Importing Products from CSV in Version 6.1 ? I am not able to import New Product from the List of CSV in 6.1 Version. Also, many2one relationship is set to weird when Updating records via csv in 6.1