Convertir CSV en SQL

Cela mérite d’être dit d’emblée, parce que pour une bonne part des gens qui cherchent cette conversion, la réponse est qu’ils n’en ont pas besoin. Postgres a COPY, MySQL a LOAD DATA INFILE, SQLite a .import, SQL Server a BULK INSERT, et chacun lit un fichier délimité directement, applique les types de colonne une fois, et écrit par blocs. Au-delà de quelques milliers de lignes, cette voie bat confortément un script d’INSERT.

Ce qui fait que cette conversion est le bon choix, c’est une contrainte, pas une préférence. Les chargeurs en masse ont besoin que le fichier soit à un endroit que le serveur peut atteindre, et souvent d’un privilège que le compte n’a pas. Si vous n’avez qu’une fenêtre de requête — une console de base de données managée, un outil de migration, un ticket d’assistance qui sera exécuté par quelqu’un d’autre — alors les instructions sont la seule monnaie disponible, et les générer à la main pour quatre cents lignes est un mauvais usage d’un après-midi.

La sortie est un fichier d’instructions INSERT, une par ligne de données, chacune nommant la table et énumérant les colonnes prises de l’en-tête. Rien ne les précède. C’est un refus plutôt qu’une omission : un CSV n’enregistre ni types, ni longueurs, ni clés, ni nullabilité, donc un schéma généré serait un ensemble de suppositions présentées avec l’autorité d’une instruction DDL.

Le coût d’une mauvaise supposition est asymétrique. Une mauvaise ligne est une fiche et un `DELETE` la corrige. Un `VARCHAR(50)` inféré à partir de la plus longue valeur d’un échantillon tronque des adresses pendant deux ans avant que quelqu’un le remarque. L’hypothèse ici est que vous avez conçu la table, ce qui est vrai de presque quiconque veut ce fichier, et que ce que vous voulez automatiser est la partie fastidieuse. Si vous avez besoin aussi d’une table, écrivez l’instruction CREATE vous-même et collez-la en haut ; les instructions générées s’exécutent contre elle sans changement.

Le nom du CSV, extension retirée, est utilisé comme table. Il est assaini en cours de route : tout ce qui n’est pas lettre, chiffre ou soulignement devient un soulignement, et un nom commençant par un chiffre se voit préfixer un, parce qu’un identifiant ne peut pas commencer par un nombre. Un fichier nommé `2024 commandes.csv` produit donc des inserts dans `_2024_commandes`.

Le même assainissement est appliqué aux noms de colonnes pris de la ligne d’en-tête, ce qui est la partie qui surprend le plus souvent. Un en-tête `Total commande (€)` devient `Total_commande____` et ne correspondra à rien dans votre table. Nettoyez la ligne d’en-tête de la source pour qu’elle corresponde exactement aux colonnes cibles, renommez le fichier à la table cible, et le fichier généré n’a besoin d’aucune édition — ce qui vaut deux minutes, parce que l’alternative est un chercher-remplacer sur cinquante mille lignes.

C’est la différence entre convertir un CSV et convertir un tableur, et c’est ce qui a le plus de chances de mal tourner. Une cellule de tableur sait si elle contient du texte ou un nombre. Un champ CSV est fait de caractères, donc le parseur infère, et ce qu’il infère est ce que l’encodeur SQL voit : une valeur lue comme un nombre est écrite nue, une valeur laissée comme texte est écrite entre guillemets.

La conséquence pratique, c’est que les colonnes d’identifiants perdent leurs guillemets et leur remplissage en même temps. Un code postal `01234` devient `1234`, sans guillemets ; un numéro de téléphone sans espaces devient un grand entier ; une référence de pièce comme `1e5` devient `100000`. Insérés dans une colonne VARCHAR, ils atterrissent comme chaînes non remplies sans erreur, ce qui est le pire scénario parce que rien ne se plaint. Exportez les colonnes d’identifiants entre guillemets ou avec un préfixe non numérique, ou vérifiez-les dans le fichier généré avant de l’exécuter — lire la première instruction prend dix secondes et montre la table, la liste des colonnes et un exemple de chaque type de valeur en une fois.

Un blanc entre deux délimiteurs devient le mot-clé NULL plutôt qu’une chaîne vide. C’est presque toujours la bonne lecture d’un trou dans un export, et c’est la lecture qu’une contrainte NOT NULL rejettera bruyamment plutôt que d’accepter silencieusement, ce qui est le comportement voulu.

Là où un blanc voulait vraiment dire chaîne vide, le fichier n’a jamais contenu la distinction et aucun convertisseur ne peut la récupérer. Si votre table a une colonne où `""` et `NULL` signifient des choses différentes — un champ optionnel de texte libre, le plus souvent — encodez la différence dans le CSV avant la conversion, ou ajoutez un `COALESCE` dans une étape de staging. L’inverse s’applique aussi : une colonne où chaque trou devrait devenir une valeur par défaut est mieux gérée en omettant la colonne de l’insert qu’en insérant NULL par-dessus la valeur par défaut.

Les nombres et booléens sont écrits nus. Le texte est entouré de guillemets simples avec tout guillemet simple à l’intérieur doublé, donc `O’Brien` devient `'O''Brien'`. C’est la convention ANSI et tout moteur majeur la lit de la même manière.

L’échappement par barre oblique inversée est délibérément absent. C’est une extension MySQL dont le comportement dépend du paramètre serveur `NO_BACKSLASH_ESCAPES` plutôt que de l’instruction elle-même, ce qui veut dire qu’un fichier qui l’utilise peut s’exécuter correctement sur un serveur et corrompre des données sur un autre de la même version. Doubler le guillemet fonctionne partout, et le fichier résultant peut être archivé dans un gestionnaire de versions et rejoué contre plusieurs environnements sans transporter d’hypothèse sur aucun d’eux.

Un champ valant `true` ou `false` dans le CSV est lu comme un booléen et écrit comme le littéral nu TRUE ou FALSE. Postgres l’accepte tel quel, MySQL traite les mots comme des alias de 1 et 0, et SQLite les accepte depuis longtemps.

SQL Server ne les accepte pas. Son type bit attend 1 et 0, et une instruction contenant TRUE ne se parse pas — ce que vous découvrirez à la première instruction si vous avez de la chance et à la quatre-centième si la colonne booléenne a une série de blancs en haut. Si c’est la destination, faites le remplacement avant d’exécuter quoi que ce soit, et vérifiez d’abord qu’aucune colonne de texte ne contient par hasard le mot tout seul.

Rien dans cette conversion n’interprète une date. Un champ valant `2024-03-11` n’est pas numérique, donc il reste une chaîne et est écrit entre guillemets, et un champ valant `11/03/2024` fait de même. C’est mieux que ça n’en a l’air : une date ISO entre guillemets s’insère correctement dans une colonne date dans tous les moteurs ici, ce qui fait d’un CSV bien formé la plus facile de toutes les sources à charger.

C’est aussi là que l’ambiguïté voyage intacte. `11/03/2024` est le 11 mars ou le 3 novembre selon qui a exporté, et le fichier ne dit pas. La base le parsera selon ses propres paramètres de locale et ne vous avertira pas qu’elle a choisi. Si le CSV vient d’un système dont vous ne pouvez pas vérifier le format de date, chargez dans une colonne staging de texte et convertissez explicitement, où l’hypothèse est écrite plutôt qu’héritée.

Les instructions sont émises individuellement, sans BEGIN, sans COMMIT, et sans groupement VALUES multi-lignes. Exécuté tel quel contre un serveur distant, c’est cinquante mille allers-retours et cinquante mille commits implicites, ce qui est assez lent pour se remarquer et qui laisse une table à moitié chargée si l’opération échoue au milieu.

Deux lignes règlent les deux problèmes : enveloppez le fichier dans BEGIN et COMMIT et la totalité du chargement devient atomique et nettement plus rapide. Le fichier est aussi plus gros que les données qu’il contient, parce que la liste de colonnes est répétée sur chaque ligne — une table large avec des noms de colonnes longs peut produire un fichier SQL plusieurs fois plus gros que le CSV dont il vient, ce qu’il vaut la peine de savoir avant de l’envoyer par courrier.

Le parseur du fichier et l’écriture des instructions sont du JavaScript ordinaire qui tourne ici, donc rien n’est téléversé et il n’y a pas de file ; ce qui borne le travail est le plafond de 100 Mo par fichier de l’offre gratuite, puis votre propre mémoire. Cela compte pour cette paire en particulier : un CSV en partance vers une base est en général des fiches clients, des transactions ou une liste de correction, et le coller dans un site pour le faire traiter est exactement ce contre quoi la plupart des politiques de données sont écrites.

Le plafond, c’est la table entière tenue d’un coup, ce qui met des dizaines de mégaoctets confortément à portée. Au-delà, la réponse est le chargeur en masse de la première section plutôt qu’un convertisseur plus gros, et c’est une meilleure réponse de toute façon.