Import de données Excel vers SQL/NoSQL





Dans ce chapitre, il est question d’automatiser le transfert de données d’ Excel vers une table SQL ou une collection NoSQL, lorsque ceci se révèle utile.

Excel/VBA vs SQL

Avantages d’Excel:

  • Des outils de mise en forme, tris, filtres disponibles dans le ruban, à partir de plages de données brutes ou de tableaux, ainsi que des outils d’analyse plus avancés comme les tableaux et graphiques croisés dynamiques. Par exemple, deux TCD, voire plus, peuvent être affichés sur une même feuille, pour une analyse fine d’un ensemble de données, vue sous des angles différents.
  • Une possibilité d’afficher des graphiques qui n’existe pas avec SQL.

Faiblesses d’Excel:

  • Quand la base de données est volumineuse, les traitements sur tables SQL sont bien plus rapides.
  • Les filtres se programment en quelques lignes sur l’éditeur SQL, sans risque de modifier la base de données originale.
Excel/VBA vs NoSQL

Avant tout, une brève comparaison entre bases relationnelles et non (uniquement) relationnelles a été faite au chapitre Python et NoSQL.
L’avantage essentiel des bases de type Mongo est de ne pas se limiter à des formats type tableaux, les documents pouvant intégrer des listes ou des données isolées.

Import de données dans SQL

On peut enregistrer le contenu d’une feuille Excel dans une table SQL sans passer par un programme. La méthode consiste à enregistrer la feuille sous format csv, puis d’importer le fichier dans une table directement à partir de l’éditeur SQL.
Dans cette présentation, nous utilisons la version SQLite.

Exemple: la feuille Excel contient des enregistrements de réservation dans un hôtel, affichant les nom, prénom, date d’arrivée et date de départ de clients. Cette feuille appartient à un classeur appelé ResaHotel.xlsm et on peut sauvegarder ces données en changeant l’extension (de xlsm à csv) sans effacer le classeur initial: les deux fichiers subsisteront sous deux extensions différentes.
 

 
Cette feuille étant sauvegardée dans ResaHotel.csv (séparateur point-virgule dans notre cas, mais ce n’est pas obligatoire), on ouvre l’éditeur DB Browser. A partir du menu Fichier, activer Importer
 

 
Après paramétrage du séparateur de champ ainsi que de l’encodage (ici ISO-8859-1 pour prendre en compte les accents), une table s’affiche dans une fenêtre.
 

 
Après validation, la structure de la table apparaît dans l’éditeur DB Browser. L’activation de parcourir les données montre que la table a été incorporée à la base de données (que nous avons créée sous le nom Hotel)
 

 

Import de données dans Mongo

Dans la partie import de fonctions python dans VBA, nous avons vu que python acceptait les arguments de type Range et les transformait en objets de type list à l’intérieur des fonctions. Pour enregistrer des données d’Excel dans une base Mongo, il faut donc créer une fonction convertissant un objet list en objet au format JSON.
A titre d’exemple, prenons une plage de données, contenant quatre colonnes, et figurant un devis adressé à un client dont le nom est donné à la feuille Excel (ici « Carrefour »)

  • Ref: références de produits
  • Qt: quantité de produits
  • Unit: prix unitaire
  • Total: total par produit

 

 
la fonction python, appelée MongoInit, reçoit deux arguments dev (devis) et cl (client) et commence par transformer chaque ligne du devis en dictionnaire, avant de les regrouper dans une liste de valeurs affectée à la clé « Carrefour »(valeur de la variable cl). L’objet créé sera ajouté à la collection devis de la base myDB:
 

 
La procédure VBA qui lance MongoInit est très simple (ne pas oublier de relancer ImportPythonUDFs() du module xlwings avant d’exécuter cette procédure)
 

 
Un aperçu du résultat dans l’éditeur de Mongo, en affichant le document enregistré en mode « arbre » (les 3 premiers éléments de la liste sont développés)
 

 
En mode texte, pour faire apparaître le format JSON, l’affichage des premiers éléments du document donne
 

 
On peut souhaiter que le document soit enregistré différemment, par exemple regrouper les références, les quantités et les prix unitaires dans 3 listes différentes, toujours rattachées à un client. Dans MongoInit, il suffit de remplacer
 

 
par
 

 
Dans l’éditeur Mongo, le résultat en mode texte donne