Importing and exporting data

A single mechanism for the whole database

Import and export are not features specific to an application. They are core operations, available from any list view: products, contacts, invoices, tasks, journal entries, stock locations. The procedure is the same everywhere, only the fields change.

That generality makes it the reference tool for three needs:

• data migration at go-live, from the previous system or from spreadsheets;
• mass modification of existing records: a price increase, a change of owner on three hundred files;
• extraction to a spreadsheet, for an analysis or for passing on to a third party.

Exporting

1. Open the list view and apply the filters that delimit the scope you want.
2. Tick the records, or tick the header box to select everything.
3. Click Actions then Export.
4. Compose the list of Fields to export: look for the field you want in the Available fields column, then click the plus to its right. It joins the right-hand column.
5. Remove a field from the selection with the bin icon beside it, and change the column order by dragging a field by its handle.
6. Choose the Format: Excel Workbook (.xlsx) or Plain Text (.csv).
7. Click Export.

Choosing the format

Preparing a re-importable export

The Updatable fields only switch, placed beside the Available fields title, changes the nature of the file produced. It reduces the list of fields offered to those Odoo will know how to read back, includes External ID among them, and above all writes the technical names of the fields into the heading row instead of their labels. It is that technical heading row that makes the file re-importable.

It is therefore that switch which distinguishes an export for consultation from an export for work. Without it, the file can be read but cannot be re-imported.

Exporting translated fields

When several languages are installed and at least one translatable field appears in the right-hand column, a language selector appears under that column. Each language kept produces its own column for each translatable field, the heading carrying the field name followed by an at sign and the language code: name@fr_FR. The column with no suffix disappears, so that the same value is not exported twice under two different headings.

The external identifier

This is the notion to understand before any import. It explains both why an import creates duplicates and how to prevent it.

Every record in Odoo carries an internal number, invisible and specific to the database. It cannot serve as a reference point: product number 412 in one database is not the same in another. The external identifier is a second name, textual and stable, which you choose yourself: for example product_screw_m6_stainless.

It serves as the matching key at import:

• if the value in the External ID column is unknown to the database, Odoo creates the record and assigns it that identifier;
• if it is already known, Odoo updates the existing record instead of creating another one.

Importing

1. Open the list view of the record type concerned.
2. Open the actions menu through the three-dot icon in the top bar, which only appears if no record is ticked, then click Import.
3. Load the file with Upload, or drop it straight onto the central area. Once the preview is shown, the button becomes Re-Upload and serves to replace the file.
4. If the workbook has several tabs, designate the right one in Sheet:. That selector does not appear if the file has only one.
5. Check that Use first row as header is ticked when the file carries a heading row.
6. Check line by line the match between the File Column and the Odoo Field. Odoo proposes a mapping for each column, which a cross allows you to remove and redo.
7. Read the last column of the table, which carries no title: it flags errors, but also the expected entry conventions.
8. Click Test: Odoo analyses the whole file without writing anything.
9. Correct the errors, retest, then click Import.

The side panel

Besides the Help pane, the left-hand panel carries the loading settings:

• Sheet:, the tab of the workbook to read;
• Use first row as header;
• Track changes in chatter, cleared by default: ticked, every write is recorded in the history of the document and its followers are notified, which slows the load down;
• Attachments, which only appears on the models carrying a file field: a second Upload button there takes the images and documents the cells of the table name.

The Formatting pane governs the interpretation of the file: column separator, decimal separator, date format, encoding. Odoo detects them on its own in most cases; they are corrected here when numbers or dates come out wrong in the preview.

Linking related records

A product refers to a category, a contact to a country, an invoice to a customer. These links are loaded by the value that designates the target record, in one of three possible ways.

For a field that accepts several values (a contact's tags, a line's taxes) separate them with commas in the same cell: Prospect,Key Account,Export.

Updating in bulk

Modifying several hundred records always follows the same round trip:

1. Filter the list on the records to modify.
2. Export them with Updatable fields only enabled, External ID column included.
3. Open the file in a spreadsheet and modify only the columns concerned, without touching the external identifier column.
4. Re-import the file.

Odoo recognises each row by its external identifier and applies the modifications to the existing records.

Sequencing a data migration

A file that references a non-existent value fails, row by row. The loading order therefore follows from the dependencies: what is designated must exist before what designates it.

1. Reference data: categories, units of measure, payment terms, taxes, tags.
2. Third parties: contacts, companies, then their linked delivery addresses.
3. The catalogue: products, then vendor prices and pricelists.
4. Transaction data: opening stock, orders in progress, opening entries.

Diagnosing a failure

The errors reported carry the row number and the field at fault. The most frequent ones:

Loading large volumes

Beyond a hundred rows, Odoo splits the load into batches of 2,000 rows. A progress window announces the current batch, the total number of batches and the estimated time remaining; the red cross at the end of the bar interrupts the load, after letting the current batch finish.

There is nothing to fill in to resume a load stopped before the end, whether it was interrupted or refused on a row: the Import button becomes Resume and a message states the row the resumption will start from. The batches already written stay in the database, and you can test or reload the file before resuming.