CSV vers SQL : transformez une feuille de calcul en instructions INSERT sans les bugs de devis

La tâche qui m'a finalement fait construire ça était ennuyeuse et ça m'a coûté une heure que je n'avais pas Un client m'a remis une feuille de calcul de 1 800 lignes de produits et m'a demandé de la charger dans leur base de données de mise en scène avant une démo le lendemain matin Pas d'API, pas d'écran d'importation, juste un CSV et une table MySQL qui l'attendaient J'ai fait ce que tout le monde fait la première fois : j'ai ouvert le fichier, j'ai écrit quelques instructions INSERT à la main pour obtenir la bonne forme, et j'ai commencé à copier des valeurs en trois lignes plus tard un nom de produit avec une apostrophe dedans, O'Brien's Tools« , a brisé toute la déclaration, parce que la citation unique a fermé la chaîne plus tôt et MySQL s'est étouffé avec le reste. C'est à ce moment-là que vous réalisez que l'écriture manuscrite de SQL à partir d'une feuille de calcul n'est pas un travail de cinq minutes, c'est un champ de mines de citations avec 1 800 chances de se tromper. Alors j'ai construit le Convertisseur CSV vers SQL« Et ce guide est le raisonnement derrière chaque option qui s'y trouve ».

tl;dr : Un convertisseur CSV vers SQL lit un fichier CSV et génère le CREATE TABLE et INSERT instructions dont une base de données a besoin pour la stocker L'outil Toolz analyse le fichier avec une machine à états RFC 4180, déduit si chaque colonne est un entier, une décimale, un booléen ou un texte, cite les identifiants pour MySQL, PostgreSQL ou SQLite, échappe correctement à chaque valeur et transforme les cellules vides en NULL. Collez le CSV, choisissez votre dialecte et copiez un script que vous pouvez exécuter immédiatement. Tout s'exécute côté client : pas de téléchargement, pas d'inscription, fonctionne hors ligne.

Je construis des produits SaaS sur Laravel et React et j'expédie des plugins WordPress, donc charger des données tabulaires dans une base de données est une corvée hebdomadaire plutôt qu'une corvée ponctuelle Parfois c'est une exportation client, parfois un fichier de départ pour un nouvel environnement, parfois un montage rapide pour un test CSV est toujours le format dans lequel il arrive, et SQL est toujours l'endroit où il doit finir Se faire passer de l'un à l'autre à la main est exactement le genre de travail répétitif et sujet aux erreurs qu'un outil de navigateur devrait effacer, donc c'est le guide que j'aurais aimé avoir quand cette apostrophe a rompu mon importation.

Qu'est-ce qu'un convertisseur CSV vers SQL ?

Un convertisseur CSV vers SQL prend des lignes de valeurs séparées par des virgules et produit les instructions SQL qui créent une table et y chargent les données. Le CSV lui-même n'est que vaguement standardisé RFC 4180 décrit la forme commune et est explicite qu'elle documente la pratique existante plutôt que de la dicter Elle lit la première ligne comme les noms de colonnes, traite chaque ligne suivante comme un enregistrement, et émet deux choses : a CREATE TABLE énoncé qui définit les colonnes avec des types sensibles et un ensemble de INSERT les instructions qui portent les valeurs La sortie est un script SQL simple que vous pouvez coller dans un client de base de données tel que TablePlus ou DBeaver, déposer dans une migration ou pipe dans le mysql, psql, ou sqlite3 ligne de commande pour construire et remplir la table en une seule étape.

La raison pour laquelle la catégorie existe est que la traduction est tripotée d'une manière facile à sous-estimer Chaque valeur de texte doit être enveloppée dans des guillemets simples Chaque guillemet à l'intérieur d'une valeur doit être doublé afin qu'il ne termine pas la chaîne Les nombres et les booléens doivent être laissés sans guillemets ou la base de données les stockera sous forme de texte et votre WHERE price > 100 se comportera bizarrement Les cellules vides doivent généralement devenir NULL plutôt qu'une chaîne vide Les noms de colonnes avec des espaces ou des mots réservés doivent être cités avec le bon caractère pour votre base de données Manquez l'un d'entre eux sur quelques milliers de lignes et tout le lot échoue, souvent avec une erreur qui pointe sur la mauvaise ligne Un convertisseur applique toutes ces règles de la même manière à chaque fois, ce qui est la proposition de valeur entière.

Comment l'outil décide-t-il de chaque colonne&#39 ; s tapez ?

C'est la partie qui sépare un convertisseur utile d'un convertisseur stupide Un outil naïf fait chaque colonne TEXT et appelle cela fait, ce qui fonctionne techniquement mais vous donne un tableau où les nombres trient comme des chaînes et vous ne pouvez pas faire d'arithmétique sans lancer Le Toolz Outil CSV vers SQL au lieu de cela, analyse chaque valeur d'une colonne et sélectionne le type le plus étroit qui correspond à toutes.

La logique est simple une fois que vous la voyez Si chaque valeur non vide d'une colonne est un nombre entier, la colonne devient un type entier Si chaque valeur est numérique mais que certaines ont des points décimaux, elle devient un type flottant Si chaque valeur est le mot true ou false[TRADUCTION], ça devient un booléen Tout le reste, y compris le contenu mixte et tout ce qui a des lettres, revient au texte Il y a une exception délibérée qui surprend les gens dans le bon sens : une valeur comme 007 ou 00123 reste du texte, pas un entier, car un zéro non significatif signifie presque toujours que la colonne est un ID, un code postal ou un numéro de téléphone où le zéro est significatif et serait perdu s'il était stocké sous forme de nombre. Cette règle unique m'a évité de corrompre silencieusement plus d'une colonne SKU de produit.

Vous pouvez désactiver l'inférence si vous préférez que chaque colonne soit du texte, ce qui est le choix sûr lorsque vous prévoyez de modifier les types vous-même après le chargement. Mais pour le cas courant, laisser l'outil déduire les types signifie le généré CREATE TABLE correspond aux données au lieu de les aplatir, et le tableau est utilisable dès qu'il existe.

Quel dialecte SQL dois-je choisir ?

SQL est un standard la façon dont l'orthographe anglaise est un standard, c'est-à-dire que chaque base de données a son propre accent Le dialecte que vous choisissez change trois choses dans la sortie : comment les identifiants sont cités, comment les noms de types sont appelés et comment sont écrits vrai et faux Voici comment les quatre options diffèrent.

Préoccupation	mysql	PostgreSQL	SQLite	SQL standard
Citation d'identifiant	Contour `col`	Citations doubles "col"	Citations doubles "col"	Citations doubles "col"
Type entier	INT	INTEGER	INTEGER	INTEGER
Type décimal	DOUBLE	DOUBLE PRECISION	REAL	REAL
Type booléen	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
Type de texte	VARCHAR(255)	TEXT	TEXT	TEXT
Valeurs booléennes	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

Les conseils pratiques sont de faire correspondre le dialecte à la base de données dans laquelle vous chargez réellement, car les différences ne sont pas cosmétiques MySQL rejettera les identifiants à double citation que PostgreSQL souhaite à moins que vous ne soyez en mode ANSI, et PostgreSQL n'en a pas TINYINT. SQLite n'a pas du tout de vrai type booléen, donc les booléens deviennent des entiers, c'est pourquoi l'outil écrit 1 et 0 pour MySQL et SQLite mais TRUE et FALSE pour PostgreSQL et SQL standard Si vous n'êtes pas sûr ou si vous écrivez quelque chose de portable, SQL standard est le choix le plus conservateur Une fois les instructions générées, vous pouvez toujours les exécuter via le Formateur SQL pour imprimer la sortie avant qu'elle n'entre dans un fichier de migration.

Comment convertir un CSV en SQL avec l'outil ?

Le flux est délibérément court Collez votre CSV dans la zone de saisie, ou cliquez sur Charger l'échantillon pour voir un exemple travaillé avec un identifiant, un nom, un rôle, un booléen et une colonne de salaire.

Définissez le nom de la table sur l'appel que doit donner la table cible, et choisissez votre dialecte SQL, puis décidez des options de sortie Laisser & devis ; Inclure CRÉER TABLE&quot ; sur si la table n'existe pas encore, ou désactivez-la pour générer seulement le INSERT instructions lorsque vous chargez dans une table qui est déjà là Gardez & quot ; Inférez les types de colonnes&quot ; marche pour une table dactylographiée, ou éteint pour tout faire du texte Choisissez &quot ; Multi-ligne INSERT&quot ; pour une instruction compacte avec de nombreux tuples de valeur, qui se charge le plus rapidement, ou éteignez-la pour en obtenir un INSERT par ligne, ce qui est plus convivial pour le contrôle de version et vous permet d'exécuter des lignes individuellement. Laisser &quot ; Videz les cellules sous forme NULL&quot ; activé, sauf si vous souhaitez spécifiquement que les chaînes vides soient stockées.

Le délimiteur peut être détecté automatiquement ou défini à la main Détecter automatiquement les scores virgule, point-virgule, onglet et tuyau en fonction de la cohérence avec laquelle chacun divise les premières lignes en un même nombre de colonnes, ce qui gère correctement les fichiers de point-virgule européens et les exportations séparées par des onglets Cliquez sur Convertir en SQL et la sortie apparaît avec un nombre de lignes et de colonnes, plus tous les avertissements concernant les noms de colonnes en double ou les lignes en haillons Copiez-le et exécutez-le Si l'exportation est arrivée sans ligne d'en-tête, désactivez &quot ; La première ligne est en-tête et quotez ; et l'outil nomme les colonnes column_1, column_2(et ainsi de suite), traite ensuite chaque ligne comme des données.

Pourquoi la valeur correcte échappant compte-t-elle autant ?

Parce que l'échappement incorrect n'est pas seulement un bug, c'est une classe de vulnérabilité de sécurité L'apostrophe qui a cassé ma première importation à la main est le même mécanisme derrière l'injection SQL : une seule citation à l'intérieur d'une valeur, si elle n'est pas échappée, termine la chaîne plus tôt et laisse tout ce qui suit être interprété comme SQL Dans une application, vous résolvez cela avec des requêtes paramétrées, où le pilote de base de données garde les valeurs et le code strictement séparés Lorsque vous générez un script SQL statique à partir d'un CSV, vous n'avez pas cette séparation, donc l'échappement doit être correct dans le texte généré lui-même.

L'outil suit la règle ANSI SQL : une valeur de texte est enveloppée dans des guillemets simples, et toute citation unique à l'intérieur de la valeur est doublée Donc O'Brien's Tools se transforme 'O''Brien''s Tools'« , que chacune des bases de données prises en charge lit comme chaîne d'origine. Les nombres et les booléens sont émis sans guillemets, ils sont donc stockés comme bon type, et les identifiants sont cités avec le dialecte & #39 ; son propre caractère, donc une colonne appelée » order ou select n'entre pas en collision avec un mot réservé C'est exactement le genre de correction fastidieuse que les humains se trompent sous la pression du temps et un outil se rétablit à chaque fois, ce qui est tout l'intérêt de l'automatiser Si vous êtes curieux de savoir comment l'échappement se compare entre les formats, le Convertisseur CSV vers JSON fait face au même problème du côté JSON, où le personnage d'échappement est une barre oblique inverse plutôt qu'une citation doublée.

Est-il sécuritaire de convertir des données sensibles du CSV en ligne ?

Pour cet outil, oui, et la raison est architecturale plutôt qu'une promesse sur une page Chaque étape, en analysant le CSV, en déduisant les types de colonnes, en échappant aux valeurs et en construisant les instructions, s'exécute en JavaScript à l'intérieur de votre propre onglet de navigateur Il n'y a pas de téléchargement, pas de serveur aller-retour, et rien n'est enregistré ou stocké Vous pouvez le prouver en ouvrant votre navigateur&#39 ; s onglet réseau et en cliquant sur Convertir : aucune requête ne quitte la page Une fois la page chargée, vous pouvez vous déconnecter entièrement d'Internet et il continue de générer SQL.

Cela est important car les CSV que les gens convertissent en SQL sont souvent les fichiers les plus sensibles d'une entreprise Les tables clients, les historiques de commandes, les enregistrements utilisateur et les données de tarification voyagent tous en tant que CSV avant d'atterrir dans une base de données Un convertisseur qui télécharge votre fichier sur un serveur, aussi bien intentionné soit-il, transforme une feuille de calcul privée en quelqu'un d'autre&#39 ; l'entrée du journal Le traitement côté client supprime entièrement la question : les données ne quittent jamais la machine sur laquelle elles ont démarré Cette valeur par défaut est délibérée sur chaque outil sur Toolz.dev, et j'ai écrit l'argument le plus long dans le guide de confidentialité des données pour les outils en ligne pour tous ceux qui veulent le raisonnement complet.

Où CSV vers SQL s'intègre-t-il dans un véritable flux de travail ?

La conversion est rarement le travail entier, c'est une station dans un pipeline Le modèle que j'ai le plus souvent frappé est le seeding : un client envoie une feuille de calcul, je la convertis en un script SQL, et j'exécute ce script pour remplir une base de données de mise en scène ou de développement afin que l'application ait des données réalistes contre lesquelles travailler Parce que la sortie est un script simple plutôt qu'une connexion en direct, il est facile de vérifier dans un repo comme un fichier de départ, examiner dans une requête pull, et ré-exécuter dans n'importe quel environnement.

Les outils qui l'entourent dépendent de ce que font les données ensuite Si je dois considérer le fichier comme une grille triable avant de le convertir, le Visionneuse CSV rend la même analyse RFC 4180 qu'une table afin que je puisse repérer une ligne mal formée avant qu'elle ne devienne mal formée INSERT. Si la destination est une API ou un fichier de configuration plutôt qu'une base de données, le Convertisseur CSV vers JSON est plutôt le bon saut et son frère JSON vers CSV gère l'inverse lorsque je dois remettre les données sous forme de feuille de calcul Et une fois que le SQL existe, le Formateur SQL range-le dans quelque chose de lisible pour une migration Aucun de ceux-ci ne télécharge vos données, vous pouvez donc les enchaîner sur le même fichier privé sans une seconde réflexion Si vous êtes en train d'assembler un kit pour ce genre de travail de données, mon Guide d'outils pour les développeurs Web marche à travers la façon dont les pièces se connectent.

Quelles sont les limites à connaître ?

L'honnêteté sur les limites fait partie de la confiance à un outil Le convertisseur génère la norme CREATE TABLE et INSERT instructions, ce qui signifie qu'il ne déduit pas de clés primaires, de clés étrangères, d'index ou de contraintes de colonnes, car aucune de ces informations n'existe dans un CSV plat. Il choisit un type raisonnable pour chaque colonne, mais VARCHAR(255) pour MySQL, le texte est une valeur par défaut et non une mesure ; si vous disposez d'une colonne de descriptions longues, vous souhaiterez peut-être l'élargir TEXT après le chargement, et si vous avez une colonne qui devrait être DATE ou DATETIME vous voudrez le modifier, car l'outil traite les dates comme du texte pour éviter de deviner un format erroné.

Le multi-rangée INSERT est compact et rapide, mais les fichiers très volumineux produisent une seule instruction très longue, et certaines bases de données plafonnent la taille d'une instruction ou le nombre d'espaces réservés Si vous chargez des dizaines de milliers de lignes et atteignez une limite, passez à une INSERT par ligne, qui échange un script plus grand contre des instructions qu'une base de données acceptera toujours Enfin, l'outil est pour générer des scripts de charge, pas pour diffuser un fichier multi-gigabytes, car toute l'entrée vit dans la mémoire du navigateur Pour les exportations quotidiennes, les fichiers de démarrage, et les luminaires, ce qui est ce que la plupart des gens ont réellement, aucune de ces limites ne mordent Savoir qu'ils existent est juste la différence entre bien utiliser un outil et être surpris par lui.

Questions fréquentes

Comment convertir un fichier CSV en SQL ? Collez le CSV dans le Convertisseur CSV vers SQL(en anglais), définissez le nom de la table, et choisissez votre dialecte SQL Il lit la ligne d'en-tête comme noms de colonnes, infère chaque type de colonne, et génère un CREATE TABLE plus INSERT les déclarations que vous pouvez copier et exécuter dans votre base de données Tout se passe dans votre navigateur, le fichier n'est donc jamais téléchargé.

Quelles bases de données SQL prend-il en charge ? Le convertisseur génère des instructions pour MySQL, PostgreSQL, SQLite et SQL standard. Le dialecte que vous choisissez contrôle le devis d'identifiant ainsi que le type et la syntaxe booléenne, de sorte que le script s'exécute dans cette base de données sans modifications. MySQL utilise des backticks et TINYINT(1) booléens, tandis que PostgreSQL et SQL standard utilisent des guillemets doubles et TRUE ou FALSE.

Comment décide-t-il des types de colonnes ? Chaque colonne est cochée par rapport à chaque valeur dans les données Si toutes les valeurs non vides sont des nombres entiers la colonne devient un type entier, tous les nombres deviennent un type décimal, toutes les valeurs vraies ou fausses deviennent un type booléen, et toute autre chose devient un texte Une valeur avec un zéro non significatif tel que 007 reste le texte car il s'agit généralement d'un identifiant significatif Vous pouvez désactiver l'inférence pour créer chaque texte de colonne.

Est-ce que ça crée la table ou seulement les inserts ? Les deux par défaut. Il émet un CREATE TABLE instruction avec les types de colonnes déduits suivie du INSERT déclarations. Vous pouvez tourner le CREATE TABLE désactivé lorsque la table existe déjà et que vous n'avez qu'à charger des lignes.

Comment les citations et les caractères spéciaux sont-ils traités ? Les valeurs de texte sont enveloppées dans des guillemets simples et toute citation unique dans une valeur est doublée, ce qui est donc l'échappement SQL standard O'Brien se transforme 'O''Brien'. Les nombres et les booléens ne sont pas cités, et les identifiants sont cités avec des backticks pour MySQL ou des guillemets doubles pour les autres dialectes, donc un mot réservé comme order ne rompt pas la déclaration.

Qu'arrive-t-il aux cellules vides ? Par défaut une cellule vide devient NULL(en), ce qui est généralement ce que vous voulez pour une valeur manquante Si vous préférez stocker une chaîne vide à la place, désactivez l'option vide-comme-NULL et les cellules vides deviennent deux guillemets simples.

Puis-je convertir un CSV qui n'a pas de ligne d'en-tête ? Oui. Désactivez l'option d'en-tête et l'outil nomme les colonnes column_1, column_2(en anglais), et ainsi de suite, traite ensuite chaque ligne comme des données Ceci est utile pour les exportations brutes qui expédient sans ligne d'en-tête.

Mes fichiers CSV sont-ils téléchargés n'importe où ? No. Parsing et la génération SQL s'exécutent en JavaScript dans votre navigateur Rien n'est transmis, enregistré ou stocké, et l'outil continue de fonctionner hors ligne une fois chargé, ce que vous pouvez confirmer en regardant l'onglet réseau pendant que vous convertissez.