Exporting data is the first stage of migrating a system to Odoo 19. Although the import function in Odoo is quite reliable, it is necessary that data should be well-prepared and in line with the relational rules. Ordinary databases do not store data in such a manner, hence it becomes imperative to first export data prior to the import into Odoo. This article will outline the procedures to export legacy data for import into Odoo. Before data can be imported into Odoo, it must be extracted from the existing system. This process involves : Data that is not correctly exported, which may lead to duplication, loss of relationships, and inaccurate financial records. A good plan for the export stage will help to prevent major problems during the implementation.
What is Specific to Odoo 19 Import
Although export logic is database-driven, import behavior is controlled by Odoo.
In Odoo 19:
- The import wizard performs stricter validation on relational fields such as Many2one and One2many
- External IDs are required for proper relationship mapping between records
- Duplicate detection is tighter, especially for fields like email and reference
- UTF-8 encoding is expected consistently during import
- Large datasets are handled better with improved batch processing
This means your exported data should:
- Include stable identifiers (External IDs)
- Avoid ambiguous matches, such as duplicate names
- Be cleaned and normalized before importing
Identifying Data to Export
Not all data from a legacy system needs to be migrated. It’s better to move only what is necessary for ongoing operations.
Typical datasets include:
- Customers and vendors
- Products and categories
- Open sales and purchase orders
- Accounting data (journals, invoices, balances)
- Inventory quantities
Archiving unused data helps reduce database size and keeps the system more efficient.
1. Identify Source Tables and Relationships
Inspect the legacy database schema:
- Primary keys
- Foreign keys
- Required fields
- Data spread across multiple tables
Example:
customer_master
id (PK)
name
email
sales_order
id (PK)
customer_id (FK)
order_date
The customer_id must be preserved; otherwise, relationships will be lost in Odoo.
2. Export from Relational Databases Using SQL
Most legacy systems use MySQL or PostgreSQL.
Customer Export
SELECT
id AS legacy_id,
name,
email,
phone
FROM customer_master;
Order Export
SELECT
so.id AS order_id,
so.order_date,
so.customer_id AS legacy_customer_id
FROM sales_order so;
3. Export to CSV Using Database Tools
PostgreSQL
COPY (
SELECT id, name, email
FROM customer_master
) TO '/tmp/customers.csv'
WITH CSV HEADER;
MySQL
SELECT id, name, email
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customer_master;
Always include headers. Odoo maps fields based on column names.
4. Exporting Data from Spreadsheet Systems
For Excel-based systems:
Correct format:
name,email,phone
John Traders,john@example.com,9876543210
Avoid:
- Merged cells
- Multi-row headers
- Formulas
Export as CSV UTF-8, not XLSX.
5. Preserving Relational Integrity (External ID Format)
Odoo reconstructs relationships using External IDs, not raw database IDs.
Correct Odoo Format
customers.csv
id,name,email
__export__.res_partner_1,John Traders,john@example.com
__export__.res_partner_2,ABC Supplies,abc@example.com
orders.csv
name,partner_id/id,order_date
SO001,__export__.res_partner_1,2024-01-10
Key Points
- Column name must be id (not legacy_id)
- Value format: __export__.model_name_uniqueid
- Relations use /id syntax (partner_id/id)
- Stored internally in ir.model.data
Without this format, Odoo cannot link records correctly.
6. Exporting Large Datasets in Batches
Avoid exporting millions of rows at once.
Example
SELECT *
FROM account_move
WHERE id BETWEEN 1 AND 50000;
Or:
SELECT *
FROM account_move
WHERE invoice_date >= '2024-01-01';
This prevents memory issues and allows retrying failed batches.
7. Data Cleaning During Export
Clean data at SQL level.
SELECT
id,
TRIM(name) AS name,
LOWER(email) AS email
FROM customer_master;
SELECT
id,
name,
COALESCE(phone, '') AS phone
FROM customer_master;
8. Handling Character Encoding
Odoo expects UTF-8.
PostgreSQL
\encoding UTF8
MySQL
SET NAMES utf8mb4;
Incorrect encoding leads to corrupted text after import.
9. Validating Exported Data
Verify row counts:
SELECT COUNT(*) FROM customer_master;
Compare with:
wc -l customers.csv
10. Automating Repeatable Exports
Exports are repeated during testing and deployment.
#!/bin/bash
psql -d legacy_db -c "
COPY (
SELECT id, name, email
FROM customer_master
) TO '/tmp/customers.csv'
WITH CSV HEADER;
"
11. Common Export Issues
- Missing foreign keys
- Duplicate partners
- Invalid date formats
- Null values in required fields
Fix these during export, not during import.
Recommended Migration Tools
A practical migration setup usually combines export scripts with the right tools:
- OpenUpgrade – Best for Odoo-to-Odoo version migrations
- Odoo Migration Service – Official paid service by Odoo
- OCA Import/Export Modules – Structured CSV imports inside Odoo
- ETL Tools (Talend, Pentaho) – Useful for complex enterprise systems
- Python XML-RPC Scripts – Direct API-based data push into Odoo
Choose based on complexity:
- Simple DB > CSV > Odoo > Import Wizard
- Complex systems > ETL or API-based approach
Exporting data for Odoo 19 migration is not just extraction; it is about producing structured, relational, and import-ready datasets.
Key requirements:
- Use proper External ID format
- Maintain relationships explicitly
- Ensure UTF-8 encoding
- Clean and validate data at source
A well-designed export pipeline ensures smooth import, minimal errors, and accurate business data in Odoo.
To read more about Overview of Database Migration in Odoo, refer to our blog Overview of Database Migration in Odoo.