Mass imports - Migrate my Data To Dolibarr

From Dolibarr ERP CRM Wiki
Jump to navigation Jump to search

Following a new installation of Dolibarr, the database is empty. It is a common requirement to migrate or import existing data (suppliers, customers, products etc.) into your Dolibarr installation for example when you want to switch from another software to Dolibarr. This page gives some tips on how you can load eternal data into your Dolibarr installation for example when you want to switch from another software to Dolibarr.

There are several possible methods of importing data. The following chapters provide information about this. Information about exporting data is described on the page Module Exports.

This does not talk about the process of copying a Dolibarr instance into another Dolibarr instance. You can have a look at Duplicate a Dolibarr instance into another one for this, or on page Backups and Restores.

Art.png Using the Dolibarr Import Module

To import data, you can decide to use the Import tool.

You will find this wizard tool in menu Tools, if the Module Imports has been enabled. You can use it to import flat files (CSV or Excel). To avoid performance or memory problem, we recommend to use CSV flat files as sources files instead of Excel files; This tool will allow you to import data (one table per one table) from flat files. This method is slow, is limited to the object where a predefined import profile is available. The advantage is that the only knowledge you need to use it is how to get a CSV flat file from your source system. You'll find more information on the page Module Imports.

For import scenarios not covered by the built-in module, the following alternatives are also possible...

OpenOffice.png Use an ETL or a third party tool gateway

You can use third parties tools, able to connect to both your old system and your Dolibarr installation. It can be a local tool or an online SaaS tool if your systems can be reach by a SaaS tool. Documentation and process will depends on the third party tool;

For example, you can use LibreOffice, which is able to insert the contents of a table into a database such as Mysql Dolibarr. Here is a tutorial on this method: Import data with LibreOffice.

Some automation tools specialized in synchronizing data may also be able to do the job.

Outils.png Developing yourself your own import script

You can decide to develop custom scripts to load data into Dolibarr database.

You can develop custom scripts to read your source information. Depending on your source system, you can make:

  • Direct database access to read the data
  • Using the API of your source system to read its data.
  • If the source system is another Dolibarr, you can also use CRUD objects of Dolibarr (the PHP Class to manipulate Data objects, so you can call the method fetch() and fetchAll() to get the data). This need to have PHP development knowledge.

This scripts can then write the data read into your target Dolibarr using also the 3 methods.

  • Using the API is the more secured method (most of the business control are included).
  • Using the CRUD class objects to call the method (so you can call the method fetch() to check if object already exists in the target, call the method create() or update() to insert of update the record). This need to have PHP development knowledge. You may find examples of script to load data from CSV files into the database using the second method (Dolibarr CRUD objects) into this directory:
  • You can also make direct database write by a direct access to the database (This method is not recommended because it bypass all business rules implemented into CRUD class or into APIs, however, it is the more performant). This need to understand the database structure. This is not so difficult because each data is saved once and only once into the Dolibarr database structure.

This is examples of some use cases:

Import third parties and contacts

We must differentiate a third party from a physical contact (person). The third party information contains all the details pertaining to billing (whether companies, private individuals or associations). Third party details are compulsory whereas physical contact details are optional. Contacts are just a directory of physical persons/addresses related to the third party. In the case of a third party that is a company, there may be several additional physical contacts. In the case of an individual, there will be only one physical contact which would be identical to the third party.

The import of the third parties inserts rows in the table Table llx_societe. Ensure that the mandatory fields contain data.

  • Name: Must contain the name of the company
  • Client: Must contain 1 if third party is a client, 2 if prospect, 0 otherwise.
  • Supplier: Must contain 1 if its a supplier, 0 otherwise

The import of physical contacts inserts rows in the table Table llx_socpeople. Ensure that the mandatory fields contain data.

  • Name: Must contain the surname of the contact
  • Firstname: Must contain the first name of the contact
  • fk_soc: Must contain the id of the third party from table Table llx_societe.

Special Notes for certain import fields when you are importing Excel files (CSV or XLSX)

  • Field Type Date The default formatting abilities in the US Excel can be used (i.e. 2022/01/19 or 2022-01-19). You must highlight the column in Excel and with <CRTL><1> you get to the formatting menu. From there click "Date" and choose format "2022-01-19". Afterwards click "Custom" and you will see the format information "yyyy-mm-dd,-@". Delete the dashes and the character after dd. Now everything will show in the Excel column as needed for Import.
  • Field Type Country Although Dolibarr displays fully spelled out name such as "United States", Dolibarr only imports the two-digit Country code. A complete list of the countries in the world can be found here: Country Codes
  • Field Type State/Province Similar to Country Code (in particular US states) which are displayed for example as "MA - Massachusetts", Dolibarr only wants the two-digit State Code. A list of US State codes can be found here: United States. | Canada | Australia | Brazil | Mexico and France is a 3 digit code France

Import invoices

To complete...

Import products

To complete...

Put into practice

Page Script development explains how to create your own Dolibarr script.

Some tutorials / examples submitted by the community

Some scripts examples are available into Dolibarr snapshot distribution in directory

Other examples are provided by contributors:

Asking a freelance

You can also ask an expert that will choose the best method and will make the actions for you.

You can rely for example on a Dolibarr Preferred Partner

Related video

(vocal in french but subtitles are available and review in this language)