Convertir XLSX en SQL

La sortie est un fichier d’instructions INSERT, une par ligne de la feuille, chacune nommant la table et listant les colonnes de l’en-tête. Il n’y a pas de CREATE TABLE au début, et c’est délibéré : un tableur n’a pas de types, pas de longueurs, pas de clés, pas de contraintes, donc tout schéma qui en serait dérivé serait de l’invention présentée comme un fait.

Une mauvaise supposition dans une ligne, c’est un mauvais enregistrement. Une mauvaise supposition dans un schéma, c’est un VARCHAR(50) qui tronque les adresses pendant deux ans. L’hypothèse ici est que vous avez déjà conçu la table — ce qui est vrai de presque toute personne qui a besoin de cette conversion — et que ce que vous voulez, c’est la partie fastidieuse écrite correctement. Si vous avez aussi besoin d’une table, écrivez vous-même l’instruction CREATE et placez-la en tête du fichier. Cela prend deux minutes, cela enregistre un ensemble de décisions que quelqu’un devrait prendre à dessein, et les instructions générées en dessous s’exécuteront contre elle sans modification.

Le nom du classeur, sans son extension, devient la table dans chaque instruction. Il est d’abord assaini : tout ce qui n’est pas une lettre, un chiffre ou un trait de soulignement devient un trait de soulignement, et un nom qui commence par un chiffre en reçoit un préfixe, parce qu’un identifiant ne peut pas commencer par un nombre. Un fichier nommé « commandes 2024.xlsx » produit donc des insertions dans _commandes_2024.

Le conseil pratique en découle directement. Renommez le fichier en le nom exact de la table dans laquelle vous chargez avant de convertir, et la sortie s’exécute telle quelle. Faire l’inverse veut dire un rechercher-remplacer sur cinquante mille lignes, ce qui marche et est une étape de plus où quelqu’un remplace la mauvaise chaîne. Le même assainissement s’applique aux noms de colonnes tirés de la ligne d’en-tête, donc un en-tête « Total commande (€) » devient Total_commande____ et ne correspondra pas à votre colonne. Mettez de l’ordre dans la ligne d’en-tête du tableur pour qu’elle corresponde à la table avant de convertir, et le fichier n’a besoin d’aucune édition.

Les nombres sont écrits à nu. Le texte est entouré de guillemets simples, toute apostrophe à l’intérieur étant doublée, ce qui est la forme ANSI comprise par tous les moteurs courants — O’Brien devient 'O''Brien'. L’échappement par barre oblique inverse n’est pas utilisé, parce que c’est une extension MySQL dont le comportement dépend d’un réglage serveur plutôt que de l’instruction.

Une cellule vide devient le mot-clé NULL plutôt qu’une chaîne vide, ce qui est presque toujours la bonne lecture d’un blanc dans un tableur et vaut la peine d’être su si votre colonne est NOT NULL. Quand la distinction compte vraiment — un blanc qui signifie « aucun » contre « non enregistré » — le tableur ne l’a jamais portée à l’origine et aucun convertisseur ne peut la retrouver. Les valeurs elles-mêmes ne sont pas reformatées au passage. Un nombre apparaît tel que le classeur le stocke, donc un prix affiché à 19,99 avec deux décimales et un symbole monétaire est écrit 19,99, et un affiché à 20 qui est en réalité 19,995 est écrit à sa pleine précision.

Une cellule booléenne est écrite comme le littéral TRUE ou FALSE. Postgres l’accepte directement, MySQL les traite comme des alias de 1 et 0, et SQLite les accepte depuis longtemps. SQL Server ne le fait pas : son type bit veut 1 et 0, et une instruction contenant TRUE échoue à l’analyse.

C’est une correction de deux secondes une fois que vous savez — remplacer les littéraux avant d’exécuter le script — et vingt minutes irritantes si vous l’apprenez d’une erreur sur l’instruction quatre cent. Si la destination est SQL Server, faites le remplacement d’abord et vérifiez qu’aucune colonne de texte ne contient le mot par hasard. L’habitude plus sûre pour n’importe quelle cible est de regarder la première instruction du fichier avant d’exécuter la dernière. Elle vous montre le nom de table, la liste de colonnes et un exemple de chaque type de valeur en une seule ligne, ce qui est la revue la plus rapide que ce fichier recevra jamais.

Un tableur stocke une date comme un nombre de jours habillé d’un format d’affichage, et la conversion écrit la valeur plutôt que l’apparence. Le 1er janvier 2024 devient 45292, compté à partir de fin décembre 1899, et un horodatage devient ce nombre avec une fraction attachée.

Inséré dans une colonne numérique, il arrive en silence et est faux ; inséré dans une colonne de date, il est refusé, ce qui est au moins bruyant. Ni l’un ni l’autre n’est ce que vous voulez. Corrigez dans le classeur en convertissant la colonne en texte dans un format sans ambiguïté, ou chargez dans une colonne d’étape et convertissez à l’intérieur de la base de données, où l’arithmétique d’époque tient en une expression. L’étape est la meilleure réponse quand le chargement doit être répété. Elle vous donne un endroit où valider avant que quoi que ce soit n’atteigne la vraie table, et la conversion des nombres de jours en dates devient une étape documentée plutôt que quelque chose que quelqu’un a pensé à faire à la main la dernière fois.

Un code stocké comme texte dans la feuille est écrit comme une chaîne entre guillemets, donc 007 arrive en '007' et une référence de compte avec zéros de tête arrive intacte dans une colonne VARCHAR. C’est l’opposé de ce qui se passe quand la même donnée passe par un CSV puis un aller-retour de tableur négligent, où ces colonnes deviennent des nombres et le remplissage a disparu avant même que la conversion ne commence.

Le test utile pour savoir si une colonne est un identifiant est de se demander si additionner deux de ses valeurs aurait un sens. Les codes postaux, les références de pièces, les numéros de téléphone et les codes de référence échouent tous à ce test, et tous appartiennent à une colonne de texte dans la table cible comme dans la feuille. Le cas inverse est celui à surveiller. Si le classeur a stocké un code comme nombre à un moment antérieur, le remplissage a déjà disparu et l’instruction insérera 7 dans votre colonne VARCHAR, tout à fait tranquillement et tout à fait à tort.

Chaque instruction nomme ses colonnes explicitement plutôt que de s’appuyer sur l’ordre des colonnes de la table, ce qui rend le fichier durable face à une table qui a depuis gagné une colonne. L’ensemble des colonnes est l’union des champs trouvés sur l’ensemble de la feuille, donc un champ qui n’apparaît que dans des lignes plus tardives est quand même dans la liste, avec NULL fourni pour les lignes qui en sont dépourvues.

Cette verbosité a un coût à connaître. La liste de colonnes est répétée à chaque ligne, donc le fichier est plus gros que la donnée qu’il contient : cinquante mille lignes de six colonnes sont sorties en environ 5,7 Mo de SQL, contre environ 4,3 Mo pour le .xlsx dont il vient. C’est la seule de ces destinations qui est plus grande que le classeur.

Les instructions sont émises une par ligne sans transaction autour et sans regroupement en VALUES à plusieurs lignes. Exécutées telles quelles contre Postgres ou MySQL, ce sont cinquante mille allers-retours et cinquante mille validations implicites, ce qui est assez lent pour être perceptible et laisse une table à moitié chargée en cas d’échec au milieu.

Encapsuler le fichier dans BEGIN et COMMIT règle les deux problèmes en même temps et coûte deux lignes. Pour des chargements véritablement volumineux, le bon outil est le chemin en bloc — COPY en Postgres, LOAD DATA en MySQL, .import en SQLite — qui lit un fichier délimité directement et est d’un ordre de grandeur plus rapide. Convertissez plutôt en CSV ou TSV pour cette voie.

La première feuille du classeur est ce qui est converti, parce qu’une série d’instructions INSERT s’adresse à une table. Si la feuille que vous voulez n’est pas la première, déplacez-la et convertissez à nouveau ; si plusieurs feuilles vont dans plusieurs tables, convertissez une fois par feuille avec le fichier nommé pour chaque table cible tour à tour.

Tout cela se fait localement : le classeur est analysé et les instructions sont écrites dans la page, sans téléversement et sans requête transportant le fichier. Cela compte plus pour cette paire que pour la plupart, car un tableur qui prend le chemin d’une base de données est habituellement des fiches clients, des transactions ou autre chose qui ne doit pas être collé dans un site web pour être traité.

Si la table cible est vide et que le volume est important, générer du SQL est la voie lente. Chaque base de données dispose d’un chargeur en bloc qui lit un fichier délimité directement, applique les types une fois et écrit par blocs, et il battra confortablement un script d’instructions INSERT sur tout ce qui dépasse quelques milliers de lignes.

Les instructions INSERT gagnent leur place quand le chargement est petit, quand il doit être revu ou archivé dans un gestionnaire de versions avant exécution, quand il sera rejoué contre plusieurs environnements, ou quand le seul accès dont vous disposez est une fenêtre de requête et non le système de fichiers. C’est une situation réelle et fréquente, et c’est celle pour laquelle cette conversion existe.