CSV zu SQL: Verwandeln Sie eine Tabellenkalkulation in EINFÜGEN-Anweisungen ohne die Angebotsfehler

Die Aufgabe, die mich schließlich dazu brachte, das zu bauen, war langweilig und sie kostete mich eine Stunde, die ich nicht hatte Ein Client reichte mir eine Tabelle mit 1.800 Produktreihen und bat mich, sie vor einer Demo am nächsten Morgen in seine Staging-Datenbank zu ladenKeine API, kein Importbildschirm, nur ein CSV und eine MySQL-Tabelle warteten daraufIch habe das gemacht, was jeder beim ersten Mal macht: Ich habe die Datei geöffnet, ein paar INSERT-Anweisungen von Hand geschrieben, um die Form richtig zu machen, und angefangen, Werte in Drei Reihen später einen Produktnamen mit einem Apostroph darin zu kopieren, O'Brien's Tools, brach die ganze Aussage, denn das einzelne Zitat schloss die Zeichenfolge vorzeitig und MySQL erstickte am Rest Das ist der Moment, in dem du merkst, dass das manuelle Schreiben von SQL aus einer Tabellenkalkulation kein Fünf-Minuten-Job ist, es ist ein zitierendes Minenfeld mit 1.800 Chancen, falsch zu treten Also habe ich die gebaut CSV-zu-SQL-Konverter, und dieser Leitfaden ist der Grund für jede Option darin.

tl; dr: Ein CSV-zu-SQL-Konverter liest eine CSV-Datei und generiert die CREATE TABLE und INSERT Anweisungen, die eine Datenbank zum Speichern benötigt Das Toolz-Tool analysiert die Datei mit einer RFC 4180-Statusmaschine, gibt an, ob jede Spalte eine ganze Zahl, eine Dezimalzahl, eine Boolesche Zahl oder ein Text ist, zitiert Identifikatoren für MySQL, PostgreSQL oder SQLite, entgeht jedem Wert korrekt und verwandelt leere Zellen in NULL. Fügen Sie die CSV ein, wählen Sie Ihren Dialekt aus und kopieren Sie ein Skript, das Sie sofort ausführen können. Alles läuft clientseitig: kein Upload, kein Signup, funktioniert offline.

Ich baue SaaS-Produkte auf Laravel und React und ich verschicke WordPress-Plugins, daher ist das Laden tabellarischer Daten in eine Datenbank eher eine wöchentliche Pflicht als ein einmaliges Mal. Manchmal ist es ein Client-Export, manchmal eine Seed-Datei für eine frische Umgebung, manchmal eine schnelle Einrichtung für einen Test. CSV ist immer das Format, in dem es ankommt, und SQL ist immer dort, wo es landen muss. Von Hand von einem zum anderen zu gelangen ist genau die Art sich wiederholender, fehleranfälliger Arbeit, die ein Browser-Tool löschen sollte, also ist dies die Anleitung, die ich gerne hätte, als dieser Apostroph meinen Import brach.

Was ist ein CSV-zu-SQL-Konverter?

Ein CSV-zu-SQL-Konverter nimmt Zeilen kommagetrennter Werte und erzeugt die SQL-Anweisungen, die eine Tabelle erstellen und die Daten hineinladen CSV selbst ist nur lose standardisiert - RFC 4180 Schildert die gängige Form und ist explizit, dass sie die bestehende Praxis dokumentiert, anstatt sie vorzuschreiben Sie liest die erste Zeile als Spaltennamen, behandelt jede folgende Zeile als Datensatz und gibt zwei Dinge aus: a CREATE TABLE Anweisung, die die Spalten mit sinnvollen Typen und einer Reihe von definiert INSERT Anweisungen, die die Werte tragen Die Ausgabe ist ein einfaches SQL-Skript, das Sie in einen Datenbank-Client wie TablePlus oder DBeaver einfügen, in eine Migration ablegen oder in den Pipe einfügen können mysql, psql, oder sqlite3 Befehlszeile zum Erstellen und Befüllen der Tabelle in einem Schritt.

Der Grund, warum die Kategorie existiert, ist, dass die Übersetzung auf leicht zu unterschätzende Weise fummelig istJeder Textwert muss in einzelne Anführungszeichen gewickelt werden Jedes einzelne Zitat innerhalb eines Wertes muss verdoppelt werden, damit es die Zeichenfolge nicht beendet Zahlen und Booleans müssen unzitiert bleiben, sonst speichert die Datenbank sie als Text und Ihr WHERE price > 100 seltsam verhalten wird Leere Zellen müssen in der Regel werden NULL Statt einer leeren Zeichenfolge Spaltennamen mit Leerzeichen oder reservierten Wörtern müssen mit dem richtigen Zeichen für Ihre Datenbank zitiert werden, verpassen Sie eine davon über einige tausend Zeilen und der ganze Stapel schlägt fehl, oft mit einem Fehler, der auf die falsche Zeile zeigt Ein Konverter wendet alle diese Regeln jedes Mal auf die gleiche Weise an, was das gesamte Wertversprechen ist.

Wie entscheidet das Tool über die einzelnen Spalten und #39;s-Typen?

Das ist der Teil, der einen nützlichen Wandler von einem dummen trennt Ein naives Werkzeug macht jede Spalte TEXT Und nennt es erledigt, was technisch funktioniert, aber Ihnen eine Tabelle gibt, in der Zahlen als Zeichenfolgen sortiert werden und Sie ohne Gießen keine Arithmetik betreiben können. Die Toolz CSV-zu-SQL-Tool Scannt stattdessen jeden Wert in einer Spalte und wählt den schmalsten Typ aus, der für alle passt.

Die Logik ist unkompliziert, wenn man sie erst einmal gesehen hat, ist jeder nichtleere Wert in einer Spalte eine ganze Zahl, so wird die Spalte zum ganzzahligen Typ, ist jeder Wert numerisch, aber manche haben Dezimalpunkte, wird er zum schwebenden Typ, ist jeder Wert das Wort true oder falseEs wird zum Booleschen Alles andere, auch gemischte Inhalte und alles mit Buchstaben, fällt auf Text zurückEs gibt eine bewusste Ausnahme, die Menschen auf gute Weise erwischt: einen Wert wie 007 oder 00123 Text bleibt, keine ganze Zahl, denn eine führende Null bedeutet fast immer, dass die Spalte eine ID, eine Postleitzahl oder eine Telefonnummer ist, bei der die Null signifikant ist und verloren gehen würde, wenn sie als Zahl gespeichert würde Diese einzige Regel hat mich davor bewahrt, stillschweigend mehr als eine Produkt-SKU-Spalte zu beschädigen.

Sie können die Schlussfolgerung ausschalten, wenn Sie lieber möchten, dass jede Spalte Text ist. Dies ist die sichere Wahl, wenn Sie planen, die Typen nach dem Laden selbst zu ändern. Im allgemeinen Fall bedeutet die Ableitung von Typen durch das Tool jedoch die Generierung CREATE TABLE Übereinstimmt mit den Daten, anstatt sie zu verflachen, und die Tabelle ist in dem Moment nutzbar, in dem sie existiert.

Welchen SQL-Dialekt soll ich wählen?

SQL ist ein Standard so wie die englische Rechtschreibung ein Standard ist, das heißt jede Datenbank hat ihren eigenen Akzent Der Dialekt, den Sie auswählen, ändert drei Dinge in der Ausgabe: wie Bezeichner zitiert werden, wie die Typnamen heißen, und wie wahr und falsch geschrieben werden Hier ist, wie sich die vier Optionen unterscheiden.

Besorgnis erregend	MySQL	PostgreSQL	SQLite	Standard-sql
Kennungszitierung	Backticks `col`	Doppelte Zitate "col"	Doppelte Zitate "col"	Doppelte Zitate "col"
Ganzzahliger Typ	INT	INTEGER	INTEGER	INTEGER
Dezimaltyp	DOUBLE	DOUBLE PRECISION	REAL	REAL
Boolescher Typ	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
Texttyp	VARCHAR(255)	TEXT	TEXT	TEXT
Boolesche Werte	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

Die praktische Anleitung besteht darin, den Dialekt der Datenbank zuzuordnen, in die Sie tatsächlich laden, da die Unterschiede nicht kosmetischer Natur sind. MySQL lehnt die doppelt zitierten Identifikatoren ab, die PostgreSQL möchte, es sei denn, Sie befinden sich im ANSI-Modus und PostgreSQL hat keine TINYINT. SQLite hat überhaupt keinen echten booleschen Typ, so dass aus booleschen Zahlen ganze Zahlen werden, weshalb das Tool schreibt 1 und 0 Sowohl für MySQL als auch für SQLite aber TRUE und FALSE Für PostgreSQL und Standard-SQL. Wenn Sie sich nicht sicher sind oder etwas Portables schreiben, ist Standard-SQL die konservativste Wahl Sobald die Anweisungen generiert sind, können Sie sie immer durch die SQL-Formater Ausgabe hübsch auszudrucken, bevor sie in eine Migrationsdatei geht.

Wie konvertiere ich mit dem Tool eine CSV in SQL?

Der Ablauf ist bewusst kurz Fügen Sie Ihren CSV in das Eingabefeld ein, oder klicken Sie auf Probe laden, um ein funktioniertes Beispiel mit einer ID, einem Namen, einer Rolle, einem Booleschen und einer Gehaltsspalte zu sehen.

Stellen Sie den Tabellennamen auf das ein, was auch immer die Zieltabelle heißen soll, und wählen Sie Ihren SQL-Dialekt aus. Entscheiden Sie sich dann für die Ausgabeoptionen Lassen Sie & quot; einschließen CREATE TABLE" auf, wenn die Tabelle noch nicht existiert, oder schalten Sie sie aus, um nur die zu generieren INSERT Anweisungen, wenn Sie in eine Tabelle laden, die bereits vorhanden ist Halten Sie & quot; Spaltentypen einfügen" auf für eine getippte Tabelle, oder aus, um alles Text zu machen Wählen Sie & quot; Mehrreihiges EINFÜGEN" für eine kompakte Anweisung mit vielen Werttupeln, die am schnellsten lädt, oder schalten Sie sie aus, um eine zu erhalten INSERT Pro Zeile, die freundlicher zur Versionskontrolle ist und Sie Zeilen einzeln ausführen lassen können Lassen Sie & quot; Leerzellen als NULL" an, es sei denn, Sie möchten ausdrücklich leere Zeichenfolgen gespeichert haben.

Der Trennzeichen kann automatisch erkannt oder von Hand gesetzt werden Erkennen Sie die Punktzahlen Komma, Semikolon, Tabulator und Pipe automatisch, indem Sie jeweils die ersten mehreren Zeilen konsistent in die gleiche Anzahl von Spalten aufteilen, was europäische Semikolondateien und durch Tabulator getrennte Exporte korrekt verarbeitet Klicken Sie auf Konvertieren in SQL und die Ausgabe erscheint mit einer Anzahl von Zeilen und Spalten, plus alle Warnungen vor doppelten Spaltennamen oder zerlumpten Zeilen Kopieren Sie es und führen Sie es aus Wenn der Export ohne Kopfzeile angekommen ist, schalten Sie & quot; Die erste Zeile ist header" und das Tool benennt die Spalten column_1, column_2Und so weiter, behandelt dann jede Zeile als Daten.

Warum ist richtiges Wert-Escape so wichtig?

Denn falsches Escaping ist nicht nur ein Bug, es ist eine Klasse von Sicherheitslücken Das Apostroph, das meinen ersten Import von Hand gebrochen hat, ist der gleiche Mechanismus hinter SQL-Injection: ein einzelnes Zitat innerhalb eines Wertes, wenn nicht entgangen, beendet die Zeichenfolge vorzeitig und lässt alles, was folgt, als SQL interpretieren In einer Anwendung löst man dies mit parametrisierten Abfragen, wobei der Datenbanktreiber Werte und Code streng getrennt hält Wenn man ein statisches SQL-Skript aus einem CSV generiert, hat man diese Trennung nicht, also muss das Escaping im generierten Text selbst korrekt sein.

Das Tool folgt der ANSI-SQL-Regel: Ein Textwert wird in einzelne Anführungszeichen gewickelt und jedes einzelne Anführungszeichen innerhalb des Werts wird verdoppelt. Also O'Brien's Tools wird 'O''Brien''s Tools', die jede der unterstützten Datenbanken als Originalzeichenfolge zurückliest Zahlen und Boolesche werden ohne Anführungszeichen ausgegeben, so dass sie als der richtige Typ gespeichert werden, und Kennungen werden mit dem Dialekt zitiert's eigenes Zeichen, so dass eine Spalte genannt wird order oder select Kollidiert nicht mit einem zurückhaltenden Wort Genau diese Art von mühsamer Korrektheit bekommen Menschen unter Zeitdruck falsch und ein Werkzeug wird jedes Mal richtig, das ist der ganze Sinn der Automatisierung Wenn Sie neugierig sind, wie das Entweichen formatübergreifend abschneidet, ist die CSV-zu-JSON-Konverter Das gleiche Problem tritt auf der JSON-Seite auf, wo der Escape-Charakter eher ein Backslash als ein doppeltes Zitat ist.

Ist es sicher, sensible CSV-Daten online zu konvertieren?

Für dieses Tool ja, und der Grund ist eher architektonisch als ein Versprechen auf einer Seite Jeder Schritt, das Parsen der CSV, das Ableiten von Spaltentypen, das Entweichen von Werten und das Erstellen der Anweisungen, läuft als JavaScript innerhalb Ihres eigenen Browser-Tab. Es gibt keinen Upload, keine Server-Round-Trip, und nichts wird protokolliert oder gespeichert. Sie können es beweisen, indem Sie Ihren Browser öffnen & #39; s Netzwerk-Tab und auf Konvertieren klicken: keine Anfrage verlässt die Seite Sobald die Seite geladen wurde, können Sie die Verbindung zum Internet vollständig trennen und es generiert weiterhin SQL.

Das ist wichtig, weil die CSVs, die Leute in SQL konvertieren, oft die sensibelsten Dateien sind, die ein Unternehmen hat Kundentabellen, Bestellverläufe, Benutzerdatensätze und Preisdaten reisen alle als CSV, bevor sie in einer Datenbank landen Ein Konverter, der Ihre Datei auf einen Server hochlädt, wie gut das auch gemeint ist, verwandelt eine private Tabellenkalkulation in jemand anderen's-Protokolleintrag. Die clientseitige Verarbeitung entfernt die Frage vollständig: Die Daten verlassen nie die Maschine, auf der sie gestartet wurden. Diese Standardeinstellung ist in jedem Tool auf Toolz.dev bewusst, und ich habe das längere Argument in der geschrieben Datenschutzleitfaden für Online-Tools Für jeden, der die volle Begründung haben will.

Wo passt CSV zu SQL in einen echten Workflow?

Die Konvertierung ist selten der ganze Job, es ist eine Station in einer Pipeline Das Muster, das ich am häufigsten getroffen habe, ist Seeding: Ein Client sendet eine Tabellenkalkulation, ich konvertiere sie in ein SQL-Skript und ich führe dieses Skript aus, um eine Staging - oder Entwicklungsdatenbank zu füllen, sodass die App realistische Daten hat, gegen die sie arbeiten kann Da es sich bei der Ausgabe um ein einfaches Skript und nicht um eine Live-Verbindung handelt, ist es einfach, als Seed-Datei in ein Repo einzuchecken, in einer Pull-Anfrage zu überprüfen und in jeder Umgebung erneut auszuführen.

Die Werkzeuge drumherum hängen davon ab, was die Daten als nächstes tun Wenn ich die Datei vor der Konvertierung als sortierbares Raster betrachten muss, wird die CSV-Betrachter Rendert die gleiche RFC 4180-Analyse wie eine Tabelle, sodass ich eine fehlerhafte Zeile erkennen kann, bevor sie fehlerhaft wird INSERT. Wenn das Ziel eine API oder eine Konfigurationsdatei und keine Datenbank ist, wird die CSV-zu-JSON-Konverter Ist stattdessen der richtige Hop und sein Geschwister JSON zu CSV Umgekehrt handhabe, wenn ich Daten als Tabellenkalkulation zurückgeben muss Und sobald es das SQL gibt, wird die SQL-Formater Aufgeräumt in etwas für eine Migration lesbares Keines davon lädt deine Daten hoch, so kannst du sie ohne einen zweiten Gedanken an dieselbe private Datei ketten Wenn du ein Kit für diese Art von Daten zusammenbaust, funktioniert mein Leitfaden für Webentwickler-Toolkit Durchläuft, wie sich die Teile verbinden.

Worin bestehen die wissenswerten Grenzen?

Ehrlichkeit über Grenzen gehört zum Vertrauen auf ein Werkzeug Der Konverter generiert Standard CREATE TABLE und INSERT Anweisungen, was bedeutet, dass es keine Primärschlüssel, Fremdschlüssel, Indizes oder Spaltenbeschränkungen ableitet, da keine dieser Informationen in einem flachen CSV vorhanden ist. Es wählt für jede Spalte einen vernünftigen Typ aus, aber VARCHAR(255) Für MySQL ist Text eine Standardeinstellung und keine Messung; Wenn Sie eine Spalte mit langen Beschreibungen haben, möchten Sie diese möglicherweise erweitern TEXT Nach dem Laden, und wenn Sie eine Spalte haben, die sein sollte DATE oder DATETIME Sie möchten es ändern, da das Tool Daten als Text behandelt, um ein falsches Format nicht zu erraten.

Die mehrreihige INSERT Kompakt und schnell ist, aber sehr große Dateien eine einzige sehr lange Anweisung erzeugen, und einige Datenbanken die Größe einer Anweisung oder die Anzahl der Platzhalter, wenn Sie Zehntausende von Zeilen laden und ein Limit treffen, wechseln Sie zu einer INSERT Pro Zeile, die ein größeres Skript gegen Aussagen tauscht, die eine Datenbank immer akzeptieren wird Schließlich dient das Tool zum Generieren von Lastskripten, nicht zum Streamen einer Multi-Gigabyte-Datei, denn die ganze Eingabe lebt im Browserspeicher Für alltägliche Exporte, Seed-Dateien, und Einrichtungsgegenstände, was die meisten Menschen tatsächlich haben, beißt keine dieser Grenzen zu Wissen, dass es sie gibt, ist nur der Unterschied zwischen der Verwendung eines Tools gut und der Überraschung darüber.

Häufig gestellte Fragen

Wie konvertiere ich eine CSV-Datei in SQL? Fügen Sie den CSV in den ein CSV-zu-SQL-Konverter, den Tabellennamen festlegen und Ihren SQL-Dialekt auswählen. Es liest die Header-Zeile als Spaltennamen, leitet jeden Spaltentyp ab und generiert einen CREATE TABLE Pluszeichen INSERT Anweisungen, die Sie kopieren und in Ihrer Datenbank ausführen können Alles passiert in Ihrem Browser, sodass die Datei nie hochgeladen wird.

Welche SQL-Datenbanken werden unterstützt? Der Konverter gibt Anweisungen für MySQL, PostgreSQL, SQLite und Standard-SQL aus. Der von Ihnen ausgewählte Dialekt steuert die Angabe der Kennung sowie den Typ und die boolesche Syntax, sodass das Skript in dieser Datenbank ohne Änderungen ausgeführt wird. MySQL verwendet Backticks und TINYINT(1) Booleaner, während PostgreSQL und Standard-SQL doppelte Anführungszeichen und verwenden TRUE oder FALSEdrohen

Wie entscheidet es über die Spaltentypen? Jede Spalte wird mit jedem Wert in den Daten abgeglichen, sind alle nichtleeren Werte ganze Zahlen wird die Spalte zum ganzzahligen Typ, alle Zahlen werden zum Dezimaltyp, alle wahren oder falschen Werte werden zum booleschen Typ, alles andere zum Text Ein Wert mit einer führenden Null wie z.B 007 Text bleibt, da es sich in der Regel um eine signifikante Kennung handelt Sie können die Schlussfolgerung ausschalten, um jeden Spaltentext zu erstellen.

Erstellt es die Tabelle oder nur die Einfügungen? Sowohl standardmäßig Es gibt eine CREATE TABLE Anweisung mit den abgeleiteten Spaltentypen gefolgt von der INSERT Aussagen. Sie können die CREATE TABLE Aus, wenn die Tabelle bereits existiert und Sie nur noch Zeilen laden müssen.

Wie wird mit Zitaten und Sonderzeichen umgegangen? Textwerte werden in einzelne Anführungszeichen gewickelt und jedes einzelne Anführungszeichen innerhalb eines Werts wird verdoppelt, was das Standard-SQL-Escape darstellt O'Brien wird 'O''Brien'. Zahlen und Boolesche Zahlen bleiben unzitiert, und Kennungen werden mit Backticks für MySQL oder doppelten Anführungszeichen für die anderen Dialekte zitiert, also ein reserviertes Wort wie order Bricht die Aussage nicht.

Was passiert mit leeren Zellen? Standardmäßig wird eine leere Zelle NULL, was man sich für einen fehlenden Wert meistens wünscht, wer stattdessen lieber eine leere Zeichenfolge hinterlegt, schaltet die Option leer-als-NULL aus und leere Zellen werden zu zwei einzelnen Anführungszeichen.

Kann ich einen CSV konvertieren, der keine Header-Zeile hat? Ja. Schalten Sie die Header-Option aus und das Tool benennt die Spalten column_1, column_2, und so weiter, behandelt dann jede Zeile als Daten Dies ist nützlich für Rohexporte, die ohne Header-Linie versenden.

Werden meine CSV-Dateien irgendwo hochgeladen? Nr. Parsen und SQL-Generierung laufen in Ihrem Browser als JavaScript, es wird nichts übertragen, protokolliert oder gespeichert, und das Tool funktioniert nach dem Laden offline weiter, was Sie bestätigen können, indem Sie während der Konvertierung die Registerkarte Netzwerk ansehen.