Import data directly through SQL into DB

Using my Dolibarr
1

Hello!

I know we have “Import Module” in order to import data into the Dolibarr Data Base.

However, we are looking for a 100% automatic procedure. The idea is to introduce the data directly in the dolibarr DB using SQL commands.

Question is: Probably dolibarr controls the database using IDs and autoincremental references. As a consequence, we might corrupt the Data Base if we try to import thirds information directly in the DB using SQL.

Has anyone experience on this? Is it possible?

Thanks in advance!

Oliver.

2

You should explain the big picture…what business problem are you are trying to solve?

3

Sure! This is the big picture:

We do receive a lot of Facebook Ads Leads. We have the details for name, telephone and Email. We might have 50 new contacts everyday. These 50 new contacts need to be phoned and after that followed.

So, in order to make things easier, we want to have the new thirds information introduced automatically into the dolibarr database.

We have the option for a daily “manual” importation, but we would prefer to do it automatically, for example, each time a new leads is added in facebook.

We are currently exporting some information from our thirds to Mailchimp automatically using “integromat” service and SQL queries. Right now we would like to try something similar but in “reverse” way: Instead of exporting the data, doing an importation directly to the dolibarr DB.

The “steps” would be:

  1. New lead is created in facebook.
  2. Integromat receives the info and syncs with dolibarr DB.
  3. An SQL query or command is used, to introduce automatically the lead info into dolibarr DB.

As soon as we don´t want to corrupt the dolibarr DB, we would like to know if there is any problem adding new “thirds” using SQL. Might affect the DB structure? Should we take care of any ID? Focus on special table?

Any advise would be appreciate.

Thanks!

Oliver.

4

I am no expert in these things but this is my opinion.
Whether a 3rd party is created via the Dolibarr GUI, or by the import script, or by something else, all will use SQL to create the necessary fields so there is no reason why you should do this, if the appropriate care is taken with validating fields.
You could examine the import script to see what is does, or just create a 3rd party and compare the database before/after to see what has been added.
You could adapt the import script to a standalone script so it can be called by an external app or use an integration tool: I use Pentaho Data Integration: https://sourceforge.net/projects/pentaho/files/
It can literally take any data from anywhere, manipulate it as you wish and put it anywhere else, all as a job that can be autorun.