Chapitre 4 : les macro Excel pour effectuer la normalisation et la

Activité d’appel à données CABIN 2013
Soutien à distance pour la publication on-line de données de biodiversité
Chapitre 4 : les macro Excel pour effectuer la
normalisation et la conversion vers le code
SQL
1 Introduction
Cette partie de l’activité de publication de données concerne l’utilisation de plusieurs
macros Excel, créées pour les besoins du projet. Elles facilitent la normalisation de
données provenant d’un logiciel tableur ainsi que leur migration vers une base de
données relationnelle.
2 Note importante sur l’opportunité de normaliser et d’utiliser ces
macros:
Ces macros ont été développées pour enseigner la normalisation des données et générer
des bases de données relationnelles au départ de Microsoft Excel. Si vous êtes familier
avec Excel et comptez conserver cet outil pour stocker vos données de base, une
normalisation trop poussée peut dans certains cas représenter un obstacle à la gestion
quotidienne des données, notamment parce que l’information est éclatée sur plusieurs
feuilles Excel, qui impliquent à chaque fois l’usage d’identifiant numériques.
Nous conseillons d’utiliser ces macros sur une copie de vos fichiers de travail plutôt que
sur les fichiers originaux, et de ne pas hésiter à continuer à effectuer la gestion de base de
données (insertion de nouvelles données ou modification de données existantes) sur vos
anciens fichiers, éventuellement dénormalisés, si vous vous préférez procéder ainsi.
Les données sous forme normalisées ne doivent pas être vues comme un remplacement
des fichiers d’origines, mais comme un outil supplémentaire à utiliser de façon régulière,
vous permettant de :
-mieux contrôler la qualité des données (faux doublons, variation d’orthographes
dans les noms scientifiques et autres noms),
- générer des check-list de vos noms scientifiques et localités,
-éventuellement préparer la conversion (ou duplication) de vos données vers une
base de données relationnelle
A vrai dire, les cas de figure où données de bases sont stockées de façon continue sous
une forme normalisée se rencontrent principalement dans le cas des bases de données
publiées sur Internet, et/ou dans le cas grandes institutions qui doivent utiliser un même
système informatique pour plusieurs dizaine de millier de spécimens et plusieurs

gestionnaires de collection. La normalisation est aussi intéressante pour des bases de
données liées à des projets qui concernent plusieurs institutions. Pour des collections de
petites tailles ou de taille moyenne, la normalisation peut, à mon sens, être plutôt
envisagée comme une technique supplémentaire permettant d’effectuer des tâches de
contrôle ou de nettoyage de la qualité des données mises en œuvre de façon régulières et
itératives (répétées), ainsi qu’un moyen d’apprentissage des techniques de bases de
données relationnelles liées à la consultation et à l’accès aux données sur Internet.
Notons aussi qu’une compréhension technique de la normalisation est très utile si vous
envisagez de collaborer avec des initiatives internationales comme le GBIF ou Catalog of
Life, Mais par ailleurs il existe depuis environ cinq ans un nouveau type de de bases de
données non relationnelles (dites NoSQL
1
) et sans intégrité référentielles conçues pour
accéder rapidement sur Internet à de très grands volumes de données sur Internet (on
parle ici de plusieurs dizaines de millions de données).
3 Présentation
La liste des macros proposées dans le fichier est la suivante :
Ces macros effectuent 3 types de tâches :
1
SQL pour « Structured Query Language » (« langage de requête structuré ») est un standard de
programmation apparu il y a un trentaine d’année et partagé par la plupart des bases de données
relationnelles (Microsoft Access, MySQL, PostgreSQL, Oracle etc..). SQL a fortement influencé la
conception d’autres langages informatiques manipulant des donénes. Les différents type de bases de
données SQL accepte cependant le langage sous une forme modifiée par rapport à la nrome, ce qui
empêche de réutiliser facilement les programmes écrit pour un logiciel donné dans un autre modèle de
logiciel, même si la manière de programmer reste très proche dans les différents logiciels. Certaines bases
de données étendent le SQL par des modules spécifiques (prise en compte de calculs géographique,
possibilité d’écrire ds procédures informatiques complexes, etc…). Un grand nombre de logiciels SQL
Open-Source existent : MySQL, MariaDB, PostgreSQL, SQLLite ). « NoSQL » désigne un type de base de
données qui s’éloigne de ce standard, et est plutôt conçu pour les moteurs de recherche sur Internet que
pour la gestion de données.

1-La macro « Normalize» permet de normaliser des données dans des relations « 1 à
plusieurs » :
2-la macro split_and_normalize permet de normaliser des données dans des relations
« plusieurs à plusieurs ».
3 la macro « annuleMacro » a été créé pour revenir en arrière après avoir exécuté
« normalize » mais ne marche pas très bien, je vous déconseille de l’utiliser
4 : la macro « GenerateMySQL » permet, via un système de menus, de générer
automatiquement un fichier pour convertir une feuille Excel en table MySQL. Elle
permet aussi d’importer le contenu de ces données dans la table.
Elle doit être exécutée pour chaque table du classeur.
5 : la macro « GeneratePostgreSQL » effectue la même opération pour PostgreSQL. Il
s’agît d’un autre système de bases de données open-source, plus complexe techniquement
que MySQL, mais qui présente l’intérêt de pouvoir gérer des données géographiques
complexes.
4 la macro « Normalize » (relation « 1 à plusieurs »)
Comme indiqué ci-dessus, la macro « normalise » permer la création d’une relation « un
à plusieurs » dans une normalisation : création d’une nouvelle table avec les valeurs triées
par une clé unique, et pose d’une colonne clé étrangère dans l’ancienne table.
Cette macro permet à l’utilisateur de :
1.1- sélectionner une ou plusieurs colonnes. Ces colonnes doivent être ou bien
uniques, ou bien regroupées de manière à concerner ensemble un même
objet ou un même concept
2
2
Ainsi :
-le genre
-l’espèce
- (éventuellement) la famille et les autres taxons supérieurs
peuvent être groupés ensemble car ils relèvent d’un même concept : le nom scientifique. On
pourra ensuite pousser la normalisation plus loin en normalisant à leur tour la famille et les rangs
supérieures et en les séparant des rangs inférieurs, mais cette étape nécessite au préalable de
grouper ensemble et de d’isoler tous les rangs taxonomiques dans une même feuille de données.
C’est la combinaison de tous ces champs qui constitue ce qu’est un nom scientifique.
De même, :
-la latitude,
-la longitude
-la technique de capture des coordonnées (GPS, cartes),
-la projection géographique,
-l’incertitude géographies
Constituent 5 colonnes qui décrivent un même concept ( la position géographique») et peuvent
être normalisées ensembles, avant d’être éventuellement normalisées de manière plus approfondie

1.2- détecter si une même valeur (c’est-à-dire combinaison des valeurs sur une
même ligne) est répétée plusieurs fois.
1.3-créer une nouvelle feuille distincte qui va contenir une seule fois chaque
combinaison de valeurs distincte et lui assigner dans une colonne placée en
première position un numéro unique.
Cette nouvelle feuille correspondra à une nouvelle table dans la base de données
relationnelle, possédant une clé primaire en première position
3
.
1.4-créer une colonne supplémentaire dans la feuille d’origine, qui est placée
devant les champs normalisés, et qui contient la clé étrangère de la valeur. Elle
établit la correspondance entre la table de départ et la table des valeurs iniques
crée à l’étape précédente
1.5-Finalement, la macro propose un menu qui laisse à l’utilisateur le choix
d’effacer ou non dans la feuille de départ les colonnes qu’il vient de normaliser.
Nous recommandons dans un premier temps de conserver les anciennes colonnes
qui ont été normalisées, de vérifier ensuite si la correspondance entre la colonne
de clé étrangère créée dans la feuille d’origine et la clé primaire de la nouvelle
feuille est correcte, et éventuellement d’effacer ensuite les colonnes de départ.
Le fait d’effacer les colonnes de la feuille de départ après les avoir normalisées se
justifie surtout si vous souhaitez exporter les données dans une base de données
SQL.
ensuite (on pourrait inclure la localité dans certain cas, bien que cela soit discutable : une localité
peut correspondre à plusieurs position).
Mais normaliser ensemble l’espèce et la latitude n’aurait aucun sens, car ce sont deux colonnes qui sont
reliées à deux réalités indépendantes l’une de l’autre.
3
Veuillez noter que les macro Excel génèrent ds numéros identifiant uniques, mais ne pose pas de
contrainte d’unicité sur la colonne. C’est-à-dire qu’elle ne crée rien qui empêche d’utiliser à nouveau une
valeur qui existe déjà. Cette unicité doit être gérée manuellement par l’utilisateur après que la feuille des
valeurs uniques ait été créée et remplie par la macro.

Mode d’emploi
1 : Couper/Coller les données que vous souhaitez normaliser dans la macro.
Il est important que la première ligne du tableau reprenne le nom des colonnes
2) Sélectionnez la ou le groupe de colonnes que vous souhaitez normaliser
-vous pouvez soit prendre la première ligne des colonnes ou non (je recommande
néanmoins de la prendre
-avec la nouvelle version de la macro, il n’est plus nécessaire de limiyer la
sélection à la dernière ligne du fichier (mais il est mieux de le faire)
Exemple avec des colonnes concernant les noms scientifiques (sur plusieurs rangs)