Toggle navigation

Hoe gegevens importeren in Odoo

Hoe starten

You can import data on any Odoo's business object using either Excel (.xlsx) or CSV (.csv) formats: contacts, products, bank statements, journal entries and even orders!

Open de weergave van het object dat u wilt opvullen en klik op Importeer.

There you are provided with templates you can easily populate with your own data. Such templates can be imported in one click; The data mapping is already done.

Hoe de template wijzigen

  • Voeg kolommen toe, verwijder ze en soort ze zodat ze zo goed mogelijk bij uw datastructuur passen.

  • We adviseren u om het veld ID niet weg te halen (waarom ziet u in de volgende sectie).

  • Stel een unieke ID in voor elke record door de ID sequentie naar beneden te slepen.

  • When you add a new column, Odoo might not be able to map it automatically if its label doesn't fit any field in Odoo. Don't worry! You can map new columns manually when you test the import. Search the list for the corresponding field.

    Gebruik vervolgens dit veld zijn label in uw bestand om het de volgende keer direct te doen werken.

Hoe importeren vanuit een andere applicatie

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 (External ID) column in Odoo. When you import another record that links to the first one, use XXX/ID (XXX/External ID) to the original unique identifier. You can also find this record using its name but you will be stuck if at least 2 records have the same name.

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.

Ik kan het veld niet vinden dat ik wens te koppelen aan mijn kolom

Odoo tries to find with some heuristic, based on the first ten lines of the files, the type of field for each column inside your file. For example if you have a column only containing numbers, only the fields that are of type Integer will be displayed for you to choose from. While this behavior might be good and easy for most cases, it is also possible that it goes wrong or that you want to map your column to a field that is not proposed by default.

If that happens, you just have to check the ** Show fields of relation fields (advanced)** option, you will then be able to choose from the complete list of fields for each column.

Waar kan ik het datum invoerformaat wijzigen?

Odoo can automatically detect if a column is a date and it will try to guess the date format from a set of most used date format. While this process can work for a lot of simple date format, some exotic date format will not be recognize and it is also possible to have some confusion (day and month inverted as example) as it is difficult to guess correctly which part is the day and which one is the month in a date like '01-03-2016'.

To view which date format Odoo has found from your file you can check the Date Format that is shown when clicking on Options under the file selector. If this format is incorrect you can change it to your liking using the ISO 8601 to define the format.

Kan ik nummers importeren met een valuta-teken (bvb.: $ 32,00)?

Yes, we fully support numbers with parenthesis to represent negative sign as well as numbers with currency sign attached to them. Odoo also automatically detect which thousand/decimal separator you use (you can change those under options). If you use a currency symbol that is not known to Odoo, it might not be recognized as a number though and it will crash.

Voorbeelden van ondersteunde cijfers (gebruikt 32000 als voorbeeld):

  • 32.000,00
  • 32000,00
  • 32,000.00
  • -32000.00
  • (32000.00)
  • € 32.000,00

  • (32000.00 €)

Voorbeeld dat niet werkt:

  • ABC 32.000,00
  • € (32.000,00)

Wat kan ik doen als de tabel Importeervoorbeeld niet correct wordt weergegeven?

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, Odoo will not detect the separations. You will need to change the file format options in your spreadsheet application. See the following question.

Hoe kan ik het CSV bestandsformaat wijzigen wanneer ik deze bewaar in de spreadsheet applicatie?

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).

Wat is het verschil tussen Database ID en Extern 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, Odoo will have to recreate links between the different records. To help you import such fields, Odoo provides 3 mechanisms. You must use one and only one mechanism per field you want to import.

Bijvoorbeeld, om te verwijzen naar het land van een contactpersoon, stelt Odoo u 3 verschillende en importeerbare velden voor:

  • Land: de naam of code van het land

  • 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)

Het land België kunt op één van deze drie manieren importeren:

  • Land: België

  • Land/Database ID: 21

  • Land/Externe ID:

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)
  • Gebruik Land/Externe ID: Gebruik externe ID wanneer u data importeert vanuit een externe applicatie.

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 bestand voor categorieën.

CSV bestand voor producten.

Wat moet ik doen als ik verschillende matches heb voor een veld?

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'.

Hoe kan ik een many2many relatieveld importeren (bijvoorbeeld een klant met meerdere labels)?

The tags should be separated by a comma without any spacing. For example, if you want your customer to be linked to both tags 'Manufacturer' and 'Retailer' then you will encode "Manufacturer,Retailer" in the same column of your CSV file.

CSV file for Manufacturer, Retailer.

Hoe kan ik een one2many relatie importeren (bijvoorbeeld verkooporderlijnen op een verkooporder)?

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. As an example, here is purchase.order_functional_error_line_cant_adpat.CSV file of some quotations you can import, based on demo data.

Bestand voor offertes.

Het volgende CSV bestand toont u hoe u aankooporders met hun respectievelijke aankooporderlijnen importeert:

Purchase orders with their respective purchase order lines.

The following CSV file shows how to import customers and their respective contacts:

Klanten en hun respectievelijke contacten.

Kan ik meerdere malen hetzelfde record importeren?

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. Odoo will take care of creating or modifying each record depending if it's new or not.

Deze optie geeft u de mogelijkheid om de importeer en exporteer functie van Odoo te gebruiken voor het bewerken van een hele reeks record in uw favoriete spreadsheet applicatie.

Wat gebeurd er als ik geen waarde voor een specifiek veld ingeef?

If you do not set all fields in your CSV file, Odoo will assign the default value for every non defined fields. But if you set fields with empty values in your CSV file, Odoo will set the EMPTY value in the field, instead of assigning the default value.

Hoe verschillende tabellen exporteren/importeren vanuit een SQL applicatie naar Odoo?

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 Odoo. 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 <a href="/base_import/static/csv/database_import_test.sql">dump of such a PostgreSQL database</a>)

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;

Dit SQL commando zal het volgende CSV bestand genereren:

External ID,Name,Is a Company

Om het CSV bestand aan te maken voor contacten, gekoppeld aan bedrijven, gebruiken we het volgende SQL commando 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

Het genereert het volgende CSV bestand:

External ID,Name,Is a Company,Related Company/External ID

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 Odoo 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.