Jérôme Belleman
Mail address
Instagram • GitHub • GitLab
Home  •  Tools  •  Posts  •  Talks  •  Travels  •  Graphics  •  About Me

Pouring Data into the Dolibarr ERP

24 Mar 2018

Just a word of praise for Dolibarr, whose authors had the good idea of keeping the programming interface transparent, which comes in handy for importing data.

1 Loading Data with the Import Module

The recommended approach for loading data into Dolibarr is by using the appropriate module that comes with it. To enable the module beforehand, from the Home menu, follow Setup → Modules and activate Data Imports. Then, open the Tools menu → New import under Import assistant. I'm taking here the example of loading physical people so choose the Third parties (Companies / foundations / physical people) and properties importable dataset.

You are now expected to choose a file format by clicking on the designated pictogram. Unfortunately, as of Dolibarr 5.0.4 (which is arguably a little old at the time of writing), I couldn't seem to be able find the bespoke icon, never mind go any further down the process.

Did I miss something? Or is it just a bug? No matter though, as it gave me the opportunity to become acquainted with a much more powerful feature of Dolibarr's: its programming interface.

2 Loading Data Programmatically

The documentation refers to a sample PHP script located in dev/skeletons/skeleton_scripts.php but I think that in the more recent version that I ought to be running, they really mean you to find your inspiration from htdocs/modulebuilder/template/scripts/myobject.php, even though there's plenty of good ideas from dev/examples/code too. If you installed Dolibarr with a packager manager, you'll find plenty in /usr/share/dolibarr/scripts.

The idea is invariably to load the master.inc.php script which makes useful variables such as $db available so you can directly talk to the database. And I'd say that would be the greatest source of inspiration, not just for you scripts but for software in general: to design data structures well enough to be able to directly work with them instead of creating clumsy interfaces that all but get in the way.

And all these examples showing this common, sensible approach ultimately tell us one simple thing: you could after all write a program in any language you fancy and directly fiddle with the database. The documentation has the good taste to specify that the llx_societe table must be provided values for the Name, Client and Supplier columns, and that the llx_socpeople table must be supplied values for the Name, Firstname and fk_soc (a foreign key to the id column in llx_societe) columns. I'm mainly quoting the documentation on the column names: as of Dolibarr 5.0.4, some of these columns were in French. They weren't keys and I could get away with not setting values to all of them. I once wrote the following tool to migrate people information from a home-made ERP based on SQLite to Dolibarr. It was a doddle:

#! /usr/bin/env python

import sqlite3
import mysql.connector

def main():
    liteconn = sqlite3.connect('/path/to/sqlite.db')
    litecurs = liteconn.cursor()
    litecurs.execute('SELECT firstname, surname FROM people')

    myconn = mysql.connector.connect(user='dolibarr',
                                     password='********',
                                     database='dolibarr')
    mycurs = myconn.cursor()

    mycurs.execute("DELETE FROM llx_societe")
    query = "INSERT INTO llx_societe (nom, client) VALUES (%s, 1)"
    for firstname, surname in litecurs:
        mycurs.execute(query, ('%s %s' % (firstname, surname),))

    myconn.commit()
    mycurs.close()
    myconn.close()

    liteconn.close()

if __name__ == '__main__':
    main()

I really do enjoy this simple idea of designing a database schema in such a way that it becomes the programming interface. And modern database systems provide the means to comprehensively achieve this, either with constraints for simple cases or triggers, even procedures for more complex ones.

3 References