Odoo Help


11 Answers

Ray Carnes United States

--Ray Carnes--
| 10 7 11
Greater Los Angeles, United States
--Ray Carnes--

Business Analyst

OpenERP 6.1, 7.0; Odoo 8.0, 9.0, 10.0, 11.0 and 12.0.

Completed Odoo Functional and Technical Training.

Functional Areas:

  • CRM/Sales

  • Inventory

  • Manufacturing

  • Accounting

  • Purchasing

Major Skills:

  • Business Process Reengineering

  • Efficiency Consulting

  • User Needs and GAP Analysis;

  • Functional and Technical Design;

  • Prototyping and Proof of Concepts;

  • Requirements Specifications;

  • Agile Implementation;

  • Data Migration;

  • Configuration & Customization (UI and code);

  • Integration - Odoo and non Odoo Applications and Services;

  • Training and Knowledge Transfer;

  • Go Live Support;

  • Helpdesk;

  • Version Upgrades and Migration.

  • Accounting Expert.

I have over 20 years of experience empowering and enabling users with enterprise information systems that make a real and measurable difference in their ability to proactively manage their businesses and organizations. 

I am a skilled Communicator and I only take the serious things seriously!

Ray Carnes United States
2/21/13, 8:17 PM

Review the online documentation topic "Importing and Exporting Data" at http://doc.openerp.com/v6.1/book/8/8_20_Config/8_20_Config_import_export.html

Review the help in the software on the Import form (there are also links to sample files on this form.):

Need to import data from an other application? In order to re-create relationships between different records, you should use the unique identifier from the original application and map it to the ID column in OpenERP. When you import an other record that links to the first one, use XXX/ID to the original unique identifier.

The ID will also be used to update the original import if you need to re-import modified data later, it's thus good practice to specify it whenever possible

What can I do when the Import preview table isn't displayed correctly? By default the Import preview is set on commas as field separators and quotation marks as text delimiters. If your csv file does not have these settings, you can modify the File Format Options (displayed under the Browse CSV file bar after you select your file).

Note that if your CSV file has a tabulation as separator, OpenERP will not detect the separations. You will need to change the file format options in your spreadsheet application. See the following question.

How can I change the CSV file format options when saving in my spreadsheet application? If you edit and save CSV files in speadsheet applications, your computer's regional settings will be applied for the separator and delimiter. We suggest you use OpenOffice or LibreOffice Calc as they will allow you to modify all three options (in 'Save As' dialog box > Check the box 'Edit filter settings' > Save).

Microsoft Excel will allow you to modify only the encoding when saving (in 'Save As' dialog box > click 'Tools' dropdown list > Encoding tab).

What's the difference between Database ID and External ID? Some fields define a relationship with another object. For example, the country of a contact is a link to a record of the 'Country' object. When you want to import such fields, OpenERP will have to recreate links between the different records. To help you import such fields, OpenERP provides 3 mechanisms. You must use one and only one mechanism per field you want to import.

For example, to reference the country of a contact, OpenERP proposes you 3 different fields to import:

Country: the name or code of the country Country/Database ID: the unique OpenERP 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, according to your need:

Use Country: This is the easiest way when your data come 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 it's 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 for Products and their Categories.

CSV file for categories CSV file for Products

What can I do if I have multiple matches for a field? If for example you have two product categories with the child name "Sellable" (ie. "Misc. Products/Sellable" & "Other Products/Sellable"), your validation is halted but you may still import your data. However, we recommend you do not import the data because they will all be linked to the first 'Sellable' category found in the Product Category list ("Misc. Products/Sellable"). We recommend you modify one of the duplicates' values or your product category hierarchy. However if you do not wish to change your configuration of product categories, we recommend you use make use of the external ID for this field 'Category'.

How can I import a many2many relationship field (e.g. a customer that has multiple tags)? The tags should be separated by a comma without any spacing. For example, if you want you customer to be lined to both tags 'Manufacturer' and 'Retailer' then you will encode it as follows "Manufacturer, Retailer" in the same column of your CSV file.

How can I import a one2many relationship (e.g. several Order Lines of a Sales Order)? If you want to import sales order 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 lines will need an addtional row that does not have any information in the fields relative to the order.

Can I import several times the same record? If you import a file that contains one of the column "External ID" or "Database ID", records that have already been imported will be modified instead of being created. This is very usefull as it allows you to import several times the same CSV file while having made some changes in between two imports. OpenERP will take care of creating or modifying each record depending if it's new or not.

This feature allows you to use the Import/Export tool of OpenERP to modify a batch of records in your favorite spreadsheet application.

What happens if I do not provide a value for a specific field? If you do not set all fields in your CSV file, OpenERP will assign the default value for every non defined fields. But if you set fields with empty values in your CSV file, OpenERP will set the EMPTY value in the field, instead of assigning the default value.

How to export/import different tables from an SQL application to OpenERP? If you need to import data from different tables, you will have to recreate relations between records belonging to different tables. (e.g. if you import companies and persons, you will have to recreate the link between each person and the company they work for).

To manage relations between tables, you can use the "External ID" facilities of OpenERP. The "External ID" of a record is the unique identifier of this record in another application. This "External ID" must be unique accoss all the records of all objects, so it's a good practice to prefix this "External ID" with the name of the application or table. (like 'company_1', 'person_1' instead of '1')

As an example, suppose you have a SQL database with two tables you want to import: companies and persons. Each person belong to one company, so you will have to recreate the link between a person and the company he work for. (If you want to test this example, here is a dump of such a PostgreSQL database).

We will first export all companies and their "External ID". In PSQL, write the following command:

copy (select 'company_'||id as "External ID",company_name as "Name",'True' as "Is a Company" from companies) TO '/tmp/company.csv' with CSV HEADER;

This SQL command will create the following CSV file: External ID,Name,Is a Company company_1,Bigees,True company_2,Organi,True company_3,Boum,True

To create the CSV file for persons, linked to companies, we will use the following SQL command in PSQL:

copy (select 'person_'||id as "External ID",person_name as "Name",'False' as "Is a Company",'company_'||company_id as "Related Company/External ID" from persons) TO '/tmp/person.csv' with CSV

It will produce the following CSV file: External ID,Name,Is a Company,Related Company/External ID person_1,Fabien,False,company_1 person_2,Laurence,False,company_1 person_3,Eric,False,company_2 person_4,Ramsy,False,company_3

As you can see in this file, Fabien and Laurence are working for the Bigees company (company_1) and Eric is working for the Organi company. The relation between persons and companies is done using the External ID of the companies. We had to prefix the "External ID" by the name of the table to avoid a conflict of ID between persons and companies (person_1 and company_1 who shared the same ID 1 in the orignial database).

The two files produced are ready to be imported in OpenERP without any modifications. After having imported these two CSV files, you will have 4 contacts and 3 companies. (the firsts two contacts are linked to the first company). You must first import the companies and then the persons.


Daniel Reis Portugal

--Daniel Reis--
| 8 9 11
Sintra, Portugal
--Daniel Reis--

Author of the "Odoo Development Essentials" book.

Managing Director at Open Source Integrators

Github: https://github.com/dreispt

Twitter; @reis_pt

Daniel Reis Portugal
2/22/13, 1:43 PM

I am doing this regularly in my production environment. I tried several methods, but the one I'm using now relies exclusively on Pentaho PDI/Kettle with the OpenERP ETL Steps.

Some pointers to get you started:

  • Install Kettle on your machine.
  • Learn a little more about ETL and Kettle.
  • Install the OpenERP steps in the stable version (4.4.0), only available in the development builds at time of writing.

Here's an example of a Transformation loading Contract data SQL Server database and importing it into OpenERP Partners and Projects:

image description

Martin Canada
3/10/13, 11:35 AM

Is this for OpenERP V7?

Daniel Reis Portugal
3/11/13, 5:52 AM

I'm working with v6.1, but there's not reason why it wouldn't work with v7.

Martin Canada
3/11/13, 7:30 AM

I'm setting up a VM with Pentaho etc. right now to give it a try. If I understand it "Kitchen" is for command line execution of ETL operations you define graphically with "Spoon". Is that correct? Have you experience with it?

Daniel Reis Portugal
3/11/13, 7:43 AM
Martin Canada
3/12/13, 8:13 AM

I so much wanted this to work. But so far I hit one fatal exception after another. java.lang.NoClassDefFoundError: com/debortoliwines/openerp/api/RowCollection HUH? deBortoliWines? I've added a comment to this page : http://wiki.pentaho.com/display/EAI/OpenERP+Object+Output

Daniel Reis Portugal
3/12/13, 11:17 AM

If you you're using latest stable (4.4.0) and find the problem in the the Database Connection Dialog test button, you can ignore it. See this trouble ticket for more info. You shouldn't have the problem if using the latest successful development build.

Martin Canada
3/12/13, 4:54 PM

I redid everything just to be sure there was no mistake. I found the problem source code here : http://source.pentaho.org/svnkettleroot/Kettle/trunk/plugins/kettle-openerp-plugin/src/org/pentaho/di/trans/steps/openerp/objectoutput/OpenERPObjectOutputData.java - - - That code explicitly refers to com.debortoliwines.openerp.api.RowCollection but I have not been able to find jar containing such a path.

Martin Canada
3/13/13, 11:08 AM

Ok. I tracked down all the getting started problems, documented them here ( http://openerpmanagementsystem.blogspot.pt/2012/12/openerp-steps-for-pentaho-kettlepdi.html ) ... and hit a brick wall. I have no idea what is causing "Unknown database identifier '0' Row :{}" nor know any way to debug it.


Fernando La Chica Mera Spain

--Fernando La Chica Mera--
| 6 2 6
San José de La Rinconada, Spain
--Fernando La Chica Mera--

Soy programador con más de 15 años de experiencia. Casi toda mi carrera profesional ha girado entorno al mundo de los ERP. Conozco el modelo de desarrollo de Openbravo, de openXpertya y Libertya pero el método que propone Odoo es el que más me gusta.

I am a programmer with over 15 years of experience where most of my professional career revolved around the world of ERP . I know the Openbravo development model of openXpertya and Libertya but that Odoo method proposed is the one I like.

Fernando La Chica Mera Spain
10/7/15, 11:05 AM

You keep using Kettle for imports ?. I have several problems. When importing dates, I translated a timestamp and gives me the error I describe in https://www.odoo.com/es_ES/forum/help-1/question/importing-data-with-kettle-91844

And now after running the Job several times the system says "Row: 0 field: .id ERROR: Unknown database identifier '3155'"

It can not seem to edit a record. If I delete the contents of the table and run if it works.

Ask a Question
Keep Informed
2 follower(s)
About This Community

This platform is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

Odoo Training Center

Access to our E-learning platform and experience all Odoo Apps through learning videos, exercises and Quizz.

Test it now