OpenERP - Importing and Exporting Data in CSV Format

OpenERP - Importing and Exporting Data in CSV Format

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