CSV naar SQL: zet een spreadsheet om in INSERT-verklaringen zonder de citaatbugs

De taak die me er uiteindelijk toe bracht dit te bouwen was saai en het kostte me een uur dat ik niet had. Een klant overhandigde me een spreadsheet van 1.800 productrijen en vroeg me om het in hun ensceneringsdatabase te laden vóór een demo de volgende ochtend. Geen API, geen importscherm, alleen een CSV en een MySQL-tabel die erop wachtte. Ik deed wat iedereen de eerste keer doet: ik opende het bestand, schreef een paar INSERT-instructies met de hand om de vorm goed te krijgen, en begon waarden te kopiëren in. Drie rijen later een productnaam met een apostrof erin, O'Brien's Tools, brak de hele verklaring, omdat de enkele quote de string vroeg sloot en MySQL verstikte in de rest Dat is het moment dat je beseft dat het met de hand schrijven van SQL uit een spreadsheet geen taak van vijf minuten is, het is een citerend mijnenveld met 1.800 kansen om fout te stappen Dus heb ik de CSV naar SQL converter, en deze gids is de redenering achter elke optie erin.

tl;dr: Een CSV naar SQL converter leest een CSV bestand en genereert de CREATE TABLE en INSERT instructies die een database nodig heeft om het op te slaan De Toolz-tool parseert het bestand met een RFC 4180-statusmachine, leidt hieruit af of elke kolom een geheel getal, decimaal, Booleaans of tekst is, citeert identificatiegegevens voor MySQL, PostgreSQL of SQLite, ontsnapt aan elke waarde correct en verandert lege cellen in NULL. Plak de CSV, kies je dialect en kopieer een script dat je meteen kunt uitvoeren. Alles draait aan de clientzijde: geen upload, geen aanmelding, werkt offline.

Ik bouw SaaS producten op Laravel en React en ik verzend WordPress plugins, dus het laden van tabular data in een database is eerder een wekelijkse klus dan een eenmalige Soms is het een client export, soms een seed bestand voor een frisse omgeving, soms een snelle armatuur voor een test CSV is altijd het formaat waarin het aankomt, en SQL is altijd waar het moet eindigen Met de hand van de een naar de ander komen is precies het soort repetitief, foutgevoelig werk dat een browser tool moet wissen, dus dit is de gids die ik wou hebben toen die apostrof mijn import verbrak.

Wat is een CSV naar SQL converter?

Een CSV naar SQL converter neemt rijen van komma-gescheiden waarden en produceert de SQL-instructies die een tabel maken en de gegevens erin laden CSV zelf is slechts losjes gestandaardiseerd - RFC 4180 beschrijft de gemeenschappelijke vorm en is expliciet dat het de bestaande praktijk documenteert in plaats van deze te dicteren. Het leest de eerste rij als kolomnamen, behandelt elke volgende rij als een record en zendt twee dingen uit: a CREATE TABLE verklaring die de kolommen definieert met verstandige typen, en een reeks INSERT instructies die de waarden dragen De uitvoer is een gewoon SQL-script dat u kunt plakken in een databaseclient zoals TablePlus of DBeaver, in een migratie kunt laten vallen of in de mysql, psql, of sqlite3 opdrachtregel om de tabel in één stap te bouwen en te vullen.

De reden dat de categorie bestaat is dat de vertaling is fiddly op manieren die gemakkelijk te onderschatten zijn Elke tekstwaarde moet worden verpakt in enkele aanhalingstekens Elke enkele aanhalingstekens binnen een waarde moet worden verdubbeld, zodat het niet de tekenreeks beëindigen Getallen en booleans moeten worden niet geciteerd of de database zal ze opslaan als tekst en uw WHERE price > 100 zal zich vreemd gedragen Lege cellen moeten meestal worden NULL in plaats van een lege string Kolomnamen met spaties of gereserveerde woorden moeten worden geciteerd met het juiste teken voor uw database Mis een van deze over een paar duizend rijen en de hele batch mislukt, vaak met een fout die op de verkeerde lijn wijst Een converter past al deze regels elke keer op dezelfde manier toe, wat de hele waardepropositie is.

Hoe beslist de tool elke kolom's type?

Dit is het deel dat een nuttige converter scheidt van een domme Een naïef gereedschap maakt elke kolom TEXT en noemt het gedaan, wat technisch werkt maar je een tabel geeft waar getallen als tekenreeksen sorteren en je niet kunt rekenen zonder casting De Toolz CSV naar SQL-tool scant in plaats daarvan elke waarde in een kolom en kiest het smalste type dat bij alle waarden past.

De logica is rechttoe rechtaan als je het eenmaal ziet Als elke niet-lege waarde in een kolom een geheel getal is, wordt de kolom een geheel getal.als elke waarde numeriek is maar sommige decimalen hebben, wordt het een zwevend type. Als elke waarde het woord is true of false, het wordt een boolean Al het andere, ook gemengde inhoud en alles met letters, valt terug op tekst Er is één bewuste uitzondering die mensen op een goede manier betrapt: een waarde als 007 of 00123 blijft tekst, geen geheel getal, want een voorloopnul betekent bijna altijd dat de kolom een ID, een postcode of een telefoonnummer is waarbij de nul significant is en verloren zou gaan als deze als een getal wordt opgeslagen. Die enkele regel heeft mij ervan weerhouden stilletjes meer dan één SKU-kolom van het product te corrumperen.

U kunt gevolgtrekking uitschakelen als u liever heeft dat elke kolom tekst is, wat de veilige keuze is als u van plan bent de typen zelf te wijzigen na het laden. Maar in het algemene geval betekent het laten afleiden van de typen door het gereedschap de gegenereerde CREATE TABLE komt overeen met de gegevens in plaats van deze af te vlakken, en de tabel is bruikbaar op het moment dat deze bestaat.

Welk SQL dialect moet ik kiezen?

SQL is een standaard zoals Engelse spelling een standaard is, dat wil zeggen dat elke database zijn eigen accent heeft, Het dialect dat je kiest verandert drie dingen in de output: hoe identifiers worden geciteerd, hoe de typenamen worden genoemd en hoe waar en onwaar worden geschreven, Hier is hoe de vier opties verschillen.

Concern	mysql	Postgresql	SQLite	Standaard SQL
Identificatie citaat	Backticks `col`	Dubbele aanhalingstekens "col"	Dubbele aanhalingstekens "col"	Dubbele aanhalingstekens "col"
Geheel getal type	INT	INTEGER	INTEGER	INTEGER
Decimaal type	DOUBLE	DOUBLE PRECISION	REAL	REAL
Booleaans type	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
Teksttype	VARCHAR(255)	TEXT	TEXT	TEXT
Booleaanse waarden	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

De praktische leidraad is om het dialect te matchen met de database waarin u daadwerkelijk laadt, omdat de verschillen niet cosmetisch zijn. MySQL zal de dubbel geciteerde identificatiegegevens weigeren die PostgreSQL wil, tenzij u zich in de ANSI-modus bevindt, en PostgreSQL heeft geen TINYINT. SQLite heeft helemaal geen echt Booleaans type, dus Booleanen worden gehele getallen, en daarom schrijft de tool 1 en 0 voor zowel MySQL als SQLite maar TRUE en FALSE voor PostgreSQL en standaard SQL. Als u niet zeker bent of als u iets draagbaars schrijft, is standaard SQL de meest conservatieve keuze. Zodra de instructies zijn gegenereerd, kunt u ze altijd door de SQL-formatter om de uitvoer mooi af te drukken voordat deze in een migratiebestand terechtkomt.

Hoe converteer ik een CSV naar SQL met de tool?

De stroom is bewust kort Plak uw CSV in het invoervak, of klik op Voorbeeld laden om een bewerkt voorbeeld te zien met een id, een naam, een rol, een boolean en een salariskolom.

Stel de tabelnaam in op hoe de doeltabel ook moet worden aangeroepen, en kies uw SQL-dialect Beslis dan over de uitvoeropties Laat & quot; Maak tabel" op als de tabel nog niet bestaat, of schakel deze uit om alleen de INSERT uitspraken wanneer u in een tabel laadt die er al is Houd & quot;Kolomtypes&quot afleiden; aan voor een getypte tabel, of uit om alles tekst te maken Kies "Meerrijige INSERT" voor één compacte verklaring met veel waardetupels, die het snelst laadt, of zet deze uit om er een te krijgen INSERT per rij, die vriendelijker is voor versiebeheer en u rijen afzonderlijk laat uitvoeren Laat & quot; Lege cellen als NULL" aan tenzij u specifiek wilt dat lege tekenreeksen worden opgeslagen.

De scheidingsteken kan automatisch worden gedetecteerd of met de hand worden ingesteld. Automatische detectie van scores komma, puntkomma, tabblad en pipe door hoe consistent elk de eerste verschillende regels in hetzelfde aantal kolommen splitst, waardoor Europese puntkommabestanden en tab-gescheiden export correct worden verwerkt Klik op Converteren naar SQL en de uitvoer wordt weergegeven met een telling van rijen en kolommen, plus eventuele waarschuwingen over dubbele kolomnamen of rafelige rijen Kopieer het en voer het uit Als de export zonder kopregel is aangekomen, schakelt u & quot uit; Eerste rij is koptekst en quot; en het gereedschap noemt de kolommen column_1, column_2, enzovoort, behandelt vervolgens elke rij als gegevens.

Waarom is het zo belangrijk dat de juiste waarde ontsnapt?

Omdat foutief ontsnappen niet zomaar een bug is, is het een klasse van beveiligingskwetsbaarheid De apostrof die mijn eerste import met de hand brak is hetzelfde mechanisme achter SQL-injectie: een enkele quote binnen een waarde, als deze niet is ontsnapt, beëindigt de string vroegtijdig en laat alles wat volgt worden geïnterpreteerd als SQL. In een applicatie los je dit op met geparametriseerde query's, waarbij het databasestuurprogramma waarden en code strikt gescheiden houdt. Wanneer je een statisch SQL-script genereert uit een CSV, heb je die scheiding niet, dus het ontsnappen moet correct zijn in de gegenereerde tekst zelf.

De tool volgt de ANSI SQL-regel: een tekstwaarde wordt verpakt in enkele aanhalingstekens, en elke enkele aanhaling binnen de waarde wordt verdubbeld Dus O'Brien's Tools HALD 'O''Brien''s Tools', die elk van de ondersteunde databases terugleest als de originele string Getallen en booleans worden zonder aanhalingstekens uitgezonden, zodat ze als het juiste type worden opgeslagen, en identificatiegegevens worden geciteerd met het dialect's eigen teken, dus een kolom genaamd order of select botst niet met een gereserveerd woord Dit is precies het soort vervelende correctheid dat mensen onder tijdsdruk fout lopen en een tool elke keer goed krijgt, wat het hele punt is van het automatiseren ervan Als je benieuwd bent hoe het ontsnappen zich over formaten heen verhoudt, dan is de CSV naar JSON converter wordt geconfronteerd met hetzelfde probleem aan de JSON-kant, waar het escape-personage een backslash is in plaats van een dubbele quote.

Is het veilig om gevoelige CSV-gegevens online te converteren?

Voor deze tool, ja, en de reden is eerder architectonisch dan een belofte op een pagina Elke stap, het parseren van de CSV, het afleiden van kolomtypen, het ontsnappen van waarden, en het opbouwen van de instructies, draait als JavaScript binnen uw eigen browser tabblad Er is geen upload, geen server round trip, en niets is gelogd of opgeslagen U kunt het bewijzen door het openen van uw browser' s netwerk tabblad en klikken op Converteren: geen verzoek verlaat de pagina Zodra de pagina is geladen kunt u de verbinding met het internet volledig verbreken en het blijft SQL genereren.

Dat is belangrijk omdat de CSV's die mensen naar SQL converteren vaak de meest gevoelige bestanden zijn die een bedrijf heeft Klantentabellen, bestelgeschiedenissen, gebruikersrecords en prijsgegevens reizen allemaal als CSV voordat ze in een database terechtkomen Een converter die uw bestand naar een server uploadt, hoe goed bedoeld ook, verandert een privéspreadsheet in iemand anders's logboekinvoer De verwerking aan de clientzijde verwijdert de vraag volledig: de gegevens verlaten nooit de machine waarop ze zijn gestart. Die standaard is opzettelijk voor elke tool op Toolz.dev, en ik heb het langere argument in de Toolz.dev opgeschreven gegevensprivacygids voor online tools voor iedereen die de volledige redenering wil.

Waar past CSV naar SQL in een echte workflow?

De conversie is zelden de hele klus, het is één station in een pijplijn Het patroon waar ik het vaakst op druk is seeding: een client verzendt een spreadsheet, ik converteer het naar een SQL-script, en ik voer dat script uit om een staging - of ontwikkelingsdatabase te vullen zodat de app realistische gegevens heeft om tegen te werken Omdat de uitvoer een gewoon script is in plaats van een live verbinding, is het eenvoudig om in een repo als een seed-bestand te checken, een pull-verzoek te bekijken en in elke omgeving opnieuw uit te voeren.

De tools eromheen zijn afhankelijk van wat de gegevens vervolgens doen. Als ik het bestand als een sorteerbaar raster moet bekijken voordat ik het converteer, zal de CSV-viewer geeft dezelfde RFC 4180-parse weer als een tabel, zodat ik een verkeerd opgemaakte rij kan herkennen voordat deze verkeerd wordt opgemaakt INSERT. Als de bestemming een API of een configuratiebestand is in plaats van een database, zal de CSV naar JSON converter is in plaats daarvan de juiste hop, en zijn broer of zus JSON naar CSV verwerkt het omgekeerde wanneer ik gegevens als spreadsheet moet teruggeven En zodra de SQL bestaat, zal de SQL-formatter tidies het in iets leesbaars voor een migratie Geen van deze upload je gegevens, dus je kunt ze zonder nadenken in hetzelfde privébestand ketenen Als je een kit aan het samenstellen bent voor dit soort gegevenswerk, mijn Handleiding voor webontwikkelaars loopt door hoe de stukken met elkaar verbonden zijn.

Wat zijn de grenzen die de moeite waard zijn om te weten?

Eerlijkheid over limieten is een onderdeel van het vertrouwen op een tool De converter genereert standaard CREATE TABLE en INSERT instructies, wat betekent dat er geen primaire sleutels, vreemde sleutels, indexen of kolombeperkingen uit worden afgeleid, omdat geen van die informatie in een platte CSV bestaat. Het kiest voor elke kolom een redelijk type, maar VARCHAR(255) voor MySQL is tekst een standaard, geen meting; als u een kolom met lange beschrijvingen heeft, wilt u deze misschien verbreden TEXT na het laden, en als je een kolom hebt, zou dat zo moeten zijn DATE of DATETIME u wilt het wijzigen, omdat de tool datums als tekst behandelt om te voorkomen dat u een verkeerd formaat raadt.

De multi-rij INSERT is compact en snel, maar zeer grote bestanden produceren een enkele zeer lange instructie, en sommige databases hebben de grootte van één instructie of het aantal tijdelijke aanduidingen Als u tienduizenden rijen laadt en een limiet bereikt, schakelt u over naar één INSERT per rij, die een groter script ruilt voor uitspraken die een database altijd zal accepteren, Ten slotte is de tool voor het genereren van load scripts, niet voor het streamen van een multi-gigabyte bestand, omdat de hele invoer in het geheugen van de browser leeft Voor dagelijkse export, seed bestanden, en armaturen, wat de meeste mensen eigenlijk hebben, geen van deze limieten bijten Weten dat ze bestaan is slechts het verschil tussen het gebruik van een tool goed en verrast worden door het.

Veelgestelde vragen

Hoe converteer ik een CSV-bestand naar SQL? Plak de CSV in de CSV naar SQL converter, stel de tabelnaam in en kies uw SQL-dialect. Het leest de koprij als kolomnamen, leidt elk kolomtype af en genereert een CREATE TABLE plusteken INSERT verklaringen die u kunt kopiëren en uitvoeren in uw database Alles gebeurt in uw browser, zodat het bestand nooit wordt geüpload.

Welke SQL databases ondersteunt het? De converter voert instructies uit voor MySQL, PostgreSQL, SQLite en standaard SQL. Het dialect dat u kiest, regelt het aanhalen van de identificatiecode en het type en de Booleaanse syntaxis, dus het script draait zonder bewerkingen in die database. MySQL gebruikt backticks en TINYINT(1) booleans, terwijl PostgreSQL en standaard SQL dubbele aanhalingstekens gebruiken en TRUE of FALSE.

Hoe bepaalt het de kolomtypen? Elke kolom wordt gecontroleerd aan de hand van elke waarde in de gegevens Als alle niet-lege waarden hele getallen zijn wordt de kolom een geheel getal type, alle getallen worden een decimaal type, alle ware of valse waarden worden een booleaans type, en al het andere wordt tekst Een waarde met een voorloopnul zoals 007 blijft tekst omdat het meestal een significante identificatie is U kunt gevolgtrekking uitschakelen om elke kolomtekst te maken.

Maakt het de tabel of alleen de inzetstukken? Beide standaard Het zendt een CREATE TABLE verklaring met de afgeleide kolomtypen gevolgd door de INSERT uitspraken. Je kunt de CREATE TABLE uit wanneer de tabel al bestaat en u alleen rijen hoeft te laden.

Hoe wordt er omgegaan met aanhalingstekens en speciale tekens? Tekstwaarden zijn verpakt in enkele aanhalingstekens en elk afzonderlijk aanhalingsteken binnen een waarde wordt verdubbeld, wat de standaard SQL-ontsnapping is, dus O'Brien HALD 'O''Brien'. Getallen en booleans worden niet geciteerd, en identificatiegegevens worden geciteerd met backticks voor MySQL of dubbele aanhalingstekens voor de andere dialecten, dus een gereserveerd woord als order verbreekt de verklaring niet.

Wat gebeurt er met lege cellen? Standaard wordt een lege cel NULL, wat meestal is wat je wilt voor een ontbrekende waarde Als je liever een lege tekenreeks opslaat in plaats daarvan, schakel dan de optie leeg-als-NULL uit en lege cellen worden twee enkele aanhalingstekens.

Kan ik een CSV converteren die geen koprij heeft? Ja. zet de koptekstoptie uit en het gereedschap noemt de kolommen column_1, column_2, enzovoort, behandelt dan elke rij als gegevens Dit is handig voor ruwe export die zonder kopregel wordt verzonden.

Worden mijn CSV-bestanden ergens geüpload? Nee. Parsing en SQL-generatie worden uitgevoerd als JavaScript in uw browser Niets wordt verzonden, gelogd of opgeslagen, en de tool blijft offline werken zodra deze is geladen, wat u kunt bevestigen door het tabblad netwerk te bekijken terwijl u converteert.