Export and import data¶

In Odoo, it is sometimes necessary to export or import data for running reports, or for data modification. This document covers the export and import of data into and out of Odoo.

Export data from Odoo¶

When working with a database, it is sometimes necessary to export data in a distinct file. Doing so can aid in reporting on activities, although, Odoo provides a precise and easy reporting tool with each available application.

With Odoo, the values can be exported from any field in any record. To do so, activate the list view ( (list) icon), on the items that need to be exported, then select the records that should be exported. To select a record, tick the checkbox next to the corresponding record. Finally, click on Actions, then Export.

When clicking on Export, an Export Data pop-over window appears, with several options for the data to export:

1. เมื่อเลือกตัวเลือก ฉันต้องการอัปเดตข้อมูล (นำเข้า-ส่งออกได้) ระบบจะแสดงเฉพาะฟิลด์ที่สามารถนำเข้าได้เท่านั้น ซึ่งมีประโยชน์ในกรณีที่ บันทึกที่มีอยู่ต้องได้รับการอัปเดต ซึ่งทำงานเหมือนตัวกรอง หากไม่เลือกตัวเลือกนี้ ระบบจะแสดงตัวเลือกฟิลด์เพิ่มเติมมากมาย เนื่องจากจะแสดงฟิลด์ทั้งหมด ไม่ใช่เฉพาะฟิลด์ที่สามารถนำเข้าได้

2. When exporting, there is the option to export in two formats: .csv and .xls. With .csv, items are separated by a comma, while .xls holds information about all the worksheets in a file, including both content and formatting.

3. These are the items that can be exported. Use the > (right arrow) icon to display more sub-field options. Use the Search bar to find specific fields. To use the Search option more efficiently, click on all the > (right arrows) to display all fields.

4. The + (plus sign) icon button is present to add fields to the Fields to export list.

5. The ↕️ (up-down arrow) to the left of the selected fields can be used to move the fields up and down, to change the order in which they are displayed in the exported file. Drag-and-drop using the ↕️ (up-down arrow) icon.

6. The 🗑️ (trash can) icon is used to remove fields. Click on the 🗑️ (trash can) icon to remove the field.

7. สำหรับรายงานที่เกิดซ้ำ บันทึกการตั้งค่าล่วงหน้าสำหรับการส่งออกจะเป็นประโยชน์ เลือกช่องที่จำเป็นทั้งหมด แล้วคลิกเมนูแบบเลื่อนลงเทมเพลต เมื่อไปถึงแล้ว ให้คลิก เทมเพลตใหม่ และตั้งชื่อเฉพาะให้กับการส่งออกที่เพิ่งสร้างขึ้น คลิกที่ไอคอน 💾 (ฟล็อปปี้ดิสก์ไดรฟ์) เพื่อบันทึกการกำหนดค่า ในครั้งถัดไปที่ต้องส่งออกรายการเดียวกัน ให้เลือกเทมเพลตที่เกี่ยวข้องซึ่งบันทึกไว้ก่อนหน้านี้จากเมนูแบบเลื่อนลง

Import data into Odoo¶

Importing data into Odoo is extremely helpful during implementation, or in times where data needs to be updated in bulk. The following documentation covers how to import data into an Odoo database.

Tip

Activating developer mode changes the visible import settings in the left menu. Doing so reveals an Advanced menu. Included in this advanced menu are two options: Track history during import and Allow matching with subfields.

หากโมเดลใช้ openchatter ตัวเลือก ติดตามประวัติระหว่างการนำเข้า จะตั้งค่าการสมัครสมาชิกและส่งการแจ้งเตือนระหว่างการนำเข้า แต่จะทำให้การนำเข้านั้นช้าลง

Should the Allow matching with subfields option be selected, then all subfields within a field are used to match under the Odoo Field while importing.

เริ่มต้น¶

Data can be imported on any Odoo business object using either Excel (.xlsx) or CSV (.csv) formats. This includes: contacts, products, bank statements, journal entries, and orders.

Open the view of the object to which the data should be imported/populated, and click on ⚙️ (Action) ‣ Import records.

After clicking Import records, Odoo reveals a separate page with templates that can be downloaded and populated with the company's own data. Such templates can be imported in one click, since the data mapping is already done. To download a template click Import Template for Customers at the center of the page.

Important

When importing a CSV file, Odoo provides Formatting options. These options do not appear when importing the proprietary Excel file type (.xls, .xlsx).

Make necessary adjustments to the Formatting options, and ensure all columns in the Odoo field and File Column are free of errors. Finally, click Import to import the data.

Adapt a template¶

Import templates are provided in the import tool of the most common data to import (contacts, products, bank statements, etc.). Open them with any spreadsheet software (Microsoft Office, OpenOffice, Google Drive, etc.).

Once the template is downloaded, proceed to follow these steps:

• Add, remove, and sort columns to best fit the data structure.

• It is strongly advised to not remove the External ID (ID) column (see why in the next section).

• Set a unique ID to every record by dragging down the ID sequencing in the External ID (ID) column.

Note

When a new column is added, Odoo may not be able to map it automatically, if its label does not fit any field within Odoo. However, new columns can be mapped manually when the import is tested. Search the drop-down menu for the corresponding field.

Then, use this field's label in the import file to ensure future imports are successful.

Import from another application¶

The External ID (ID) is a unique identifier for the line item. Feel free to use one from previous software to facilitate the transition to Odoo.

Setting an ID is not mandatory when importing, but it helps in many cases:

• Update imports: import the same file several times without creating duplicates.

• Import relation fields.

To recreate relationships between different records, the unique identifier from the original application should be used to map it to the External ID (ID) column in Odoo.

When another record is imported that links to the first one, use XXX/ID (XXX/External ID) for the original unique identifier. This record can also be found using its name.

Field missing to map column¶

Odoo heuristically tries to find the type of field for each column inside the imported file, based on the first ten lines of the files.

For example, if there is a column only containing numbers, only the fields with the integer type are presented as options.

While this behavior might be beneficial in most cases, it is also possible that it could fail, or the column may be mapped to a field that is not proposed by default.

If this happens, check the Show fields of relation fields (advanced) option, then a complete list of fields becomes available for each column.

Change data import format¶

When importing a CSV file, Odoo provides Formatting options.

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

Import numbers with currency signs¶

Odoo fully supports numbers with parenthesis to represent negative signs, as well as numbers with currency signs attached to them. Odoo also automatically detects which thousand/decimal separator is used. If a currency symbol unknown to Odoo is used, it might not be recognized as a number, and the import crashes.

Examples of supported numbers (using 'thirty-two thousand' as the figure):

• 32.000,00

• 32000,00

• 32,000.00

• -32000.00

• (32000.00)

• $ 32.000,00

• (32000.00 €)

Example that will not work:

• ABC 32.000,00

• $ (32.000,00)

Import preview table not displayed correctly¶

By default, the import preview is set on commas as field separators, and quotation marks as text delimiters. If the CSV file does not have these settings, modify the Formatting options (displayed under the Import CSV file bar after selecting the CSV file).

Change CSV file format in spreadsheet application¶

When editing and saving CSV files in spreadsheet applications, the computer's regional settings are applied for the separator and delimiter. Odoo suggests using OpenOffice or LibreOffice, as both applications allow modifications of all three options (from LibreOffice application, go to 'Save As' dialog box ‣ Check the box 'Edit filter settings' ‣ Save).

Microsoft Excel can modify the encoding when saving ('Save As' dialog box ‣ 'Tools' drop-down menu ‣ Encoding tab).

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 such fields are imported, Odoo has to recreate links between the different records. To help import such fields, Odoo provides three mechanisms.

For example, to reference the country of a contact, Odoo proposes three 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 of Belgium, for example, use one of these three ways to import:

• Country: Belgium

• Country/Database ID: 21

• Country/External ID: base.be

According to the company's need, use one of these three ways to reference records in relations. Here is an example when one or the other should be used, according to the need:

• Use Country: this is the easiest way when data comes from CSV files that have been created manually.

• Use Country/Database ID: this should rarely be used. It is mostly used by developers as the main advantage is to never have conflicts (there may be several records with the same name, but they always have a unique Database ID)

• Use Country/External ID: use External ID when importing data from a third-party application.

When External IDs are used, import CSV files with the External ID (ID) column defining the External ID of each record that is imported. Then, a reference can be made to that record with columns, like Field/External ID. The following two CSV files provide an example for products and their categories.

• CSV file for categories

• CSV file for Products

Import relation fields¶

An Odoo object is always related to many other objects (e.g. a product is linked to product categories, attributes, vendors, etc.). To import those relations, the records of the related object need to be imported first, from their own list menu.

This can be achieved by using either the name of the related record, or its ID, depending on the circumstances. The ID is expected when two records have the same name. In such a case add / ID at the end of the column title (e.g. for product attributes: Product Attributes / Attribute / ID).

Options for multiple matches on fields¶

ตัวอย่างเช่น หากมีหมวดหมู่ผลิตภัณฑ์สองหมวดหมู่ที่มีชื่อย่อยว่า ขายได้ (เช่น ผลิตภัณฑ์เบ็ดเตล็ด/ขายได้ และ ผลิตภัณฑ์อื่นๆ/ขายได้) การตรวจสอบความถูกต้องจะหยุดลง แต่ข้อมูลอาจยังคงนำเข้าอยู่ อย่างไรก็ตาม Odoo ขอแนะนำว่าอย่านำเข้าข้อมูล เนื่องจากข้อมูลทั้งหมดจะเชื่อมโยงกับหมวดหมู่ ขายได้ แรกที่พบในรายการ หมวดหมู่ผลิตภัณฑ์ (ผลิตภัณฑ์เบ็ดเตล็ด/ขายได้) Odoo แนะนำให้แก้ไขค่าใดค่าหนึ่งที่ซ้ำกัน หรือลำดับชั้นหมวดหมู่ผลิตภัณฑ์แทน

However, if the company does not wish to change the configuration of product categories, Odoo recommends making use of the External ID for this field, 'Category'.

Import many2many relationship fields¶

The tags should be separated by a comma, without any spacing. For example, if a customer needs to be linked to both tags: Manufacturer and Retailer then 'Manufacturer,Retailer' needs to be encoded in the same column of the CSV file.

• CSV file for Manufacturer, Retailer

Import one2many relationships¶

หากบริษัทต้องการนำเข้าใบสั่งขายที่มีบรรทัดคำสั่งซื้อหลายรายการ แถวเฉพาะจะ ต้อง ถูกจองไว้ในไฟล์ CSV สำหรับแต่ละบรรทัดคำสั่งซื้อ รายการคำสั่งซื้อแรกจะถูกนำเข้าในแถวเดียวกันกับข้อมูลที่สัมพันธ์กับคำสั่งซื้อ บรรทัดเพิ่มเติมจำเป็นต้องมีแถวเพิ่มเติมที่ไม่มีข้อมูลใดๆ ในฟิลด์ที่เกี่ยวข้องกับคำสั่งซื้อ

As an example, here is a CSV file of some quotations that can be imported, based on demo data:

• File for some Quotations

The following CSV file shows how to import purchase orders with their respective purchase order lines:

• Purchase orders with their respective purchase order lines

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

• Customers and their respective contacts

Import records several times¶

If an imported file contains one of the columns: External ID or Database ID, records that have already been imported are modified, instead of being created. This is extremely useful as it allows users to import the same CSV file several times, while having made some changes in between two imports.

Odoo takes care of creating or modifying each record, depending if it is new or not.

This feature allows a company to use the Import/Export tool in Odoo to modify a batch of records in a spreadsheet application.

Value not provided for a specific field¶

If all fields are not set in the CSV file, Odoo assigns the default value for every non-defined field. But, if fields are set with empty values in the CSV file, Odoo sets the empty value in the field, instead of assigning the default value.

Export/import different tables from an SQL application to Odoo¶

If data needs to be imported from different tables, relations need to be recreated between records belonging to different tables. For instance, if companies and people are imported, the link between each person and the company they work for needs to be recreated.

หากต้องการจัดการความสัมพันธ์ระหว่างตาราง ให้ใช้เครื่องมือ รหัสภายนอก ของ Odoo รหัสภายนอก ของบันทึกคือตัวระบุเฉพาะของบันทึกนี้ในแอปพลิเคชันอื่น รหัสภายนอก จะต้องไม่ซ้ำกันในบันทึกทั้งหมดของออบเจ็กต์ทั้งหมด แนวทางปฏิบัติที่ดีคือเติมคำนำหน้า รหัสภายนอก ด้วยชื่อของแอปพลิเคชันหรือตาราง (เช่น 'company_1', 'person_1' - แทนที่จะเป็น '1')

As an example, suppose there is an SQL database with two tables that are to be imported: companies and people. Each person belongs to one company, so the link between a person and the company they work for must be recreated.

Test this example, with a sample of a PostgreSQL database.

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 creates 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 people linked to companies, 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 produces 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

ในไฟล์นี้ Fabien และ Laurence กำลังทำงานให้กับบริษัท Bigees (company_1) และ Eric กำลังทำงานให้กับบริษัท Organi ความสัมพันธ์ระหว่างบุคคลและบริษัทเสร็จสิ้นโดยใช้ ID ภายนอก ของบริษัท รหัสภายนอก นำหน้าด้วยชื่อของตารางเพื่อหลีกเลี่ยงความขัดแย้งของรหัสระหว่างบุคคลและบริษัท (person_1 และ company_1 ซึ่งใช้ ID 1 เดียวกันในฐานข้อมูลดั้งเดิม)

The two files produced are ready to be imported in Odoo without any modifications. After having imported these two CSV files, there are four contacts and three companies (the first two contacts are linked to the first company). Keep in mind to first import the companies, and then the people.

Update data in Odoo¶

Existing data can be updated in bulk through a data import, as long as the External ID remains consistent.

Prepare data export¶

To update data through an import, first navigate to the data to be updated, and select the (list) icon to activate list view. On the far-left side of the list, tick the checkbox for any record to be updated. Then, click Actions, and select Export from the drop-down menu.

On the resulting Export Data pop-up window, tick the checkbox labeled, I want to update data (import-compatible export). This automatically includes the External ID in the export. Additionally, it limits the Fields to export list to only include fields that are able to be imported.

Select the required fields to be included in the export using the options on the pop-up window, then click Export.

Import updated data¶

After exporting, make any necessary changes to the data file. When the file is ready, it can be imported by following the same process as a normal data import.