CSV in SQL: trasforma un foglio di calcolo in istruzioni INSERISCI senza i bug delle virgolette

Il compito che finalmente mi ha fatto costruire questo è stato noioso e mi è costato un'ora che non avevo Un client mi ha consegnato un foglio di calcolo di 1.800 righe di prodotto e mi ha chiesto di caricarlo nel loro database di staging prima di una demo la mattina successiva Nessuna API, nessuna schermata di importazione, solo un CSV e una tabella MySQL che lo aspettavo Ho fatto quello che fanno tutti la prima volta: ho aperto il file, ho scritto un paio di istruzioni INSERT a mano per ottenere la forma giusta e ho iniziato a copiare i valori in Tre righe dopo un nome di prodotto con un apostrofo in esso, O'Brien's Tools, ha rotto l'intera affermazione, perché la singola citazione ha chiuso la stringa in anticipo e MySQL ha soffocato il resto. Quello è il momento in cui ti rendi conto che scrivere a mano SQL da un foglio di calcolo non è un lavoro di cinque minuti, è un campo minato di citazioni con 1.800 possibilità di sbagliare. Quindi ho costruito il Convertitore CSV a SQL, e questa guida è il ragionamento alla base di ogni opzione in essa contenuta.

tl; dr: Un convertitore da CSV a SQL legge un file CSV e genera il CREATE TABLE e INSERT istruzioni un database deve memorizzarlo Lo strumento Toolz analizza il file con una macchina a stati RFC 4180, deduce se ogni colonna è un numero intero, decimale, booleano o testo, cita identificatori per MySQL, PostgreSQL o SQLite, sfugge correttamente a ogni valore e trasforma le celle vuote in NULL. Incolla il CSV, scegli il tuo dialetto e copia uno script che puoi eseguire subito Tutto funziona lato client: nessun caricamento, nessuna registrazione, funziona offline.

Costruisco prodotti SaaS su Laravel e React e spedisco plugin WordPress, quindi caricare dati tabulari in un database è un lavoro settimanale piuttosto che una tantum A volte si tratta di un'esportazione client, a volte un file seed per un ambiente fresco, a volte un dispositivo rapido per un test CSV è sempre il formato in cui arriva e SQL è sempre dove deve finire Ottenere dall'uno all'altro a mano è esattamente il tipo di lavoro ripetitivo e soggetto a errori che uno strumento browser dovrebbe cancellare, quindi questa è la guida che vorrei avere quando quell'apostrofo ha interrotto la mia importazione.

Che cosa è un convertitore CSV a SQL?

Un convertitore da CSV a SQL prende righe di valori separati da virgole e produce le istruzioni SQL che creano una tabella e caricano i dati al suo interno Il CSV stesso è standardizzato solo in modo approssimativo - RFC 4180 descrive la forma comune ed è esplicito che documenta la pratica esistente piuttosto che dettarla Legge la prima riga come i nomi delle colonne, tratta ogni riga successiva come un record ed emette due cose: a CREATE TABLE istruzione che definisce le colonne con tipi sensibili e un insieme di INSERT istruzioni che portano i valori L'output è un semplice script SQL che puoi incollare in un client di database come TablePlus o DBeaver, immettere una migrazione o eseguire il pipe in mysql, psql, o sqlite3 riga di comando per costruire e popolare la tabella in un unico passaggio.

La ragione per cui la categoria esiste è che la traduzione è fiddly in modi facili da sottovalutare Ogni valore di testo deve essere avvolto in virgolette singole Ogni singola citazione all'interno di un valore deve essere raddoppiata in modo che non termini la stringa Numeri e booleani devono essere lasciati senza virgolette o il database li memorizzerà come testo e il tuo WHERE price > 100 si comporterà in modo strano Le cellule vuote di solito devono diventare NULL piuttosto che una stringa vuota I nomi delle colonne con spazi o parole riservate devono essere citati con il carattere giusto per il tuo database Perdere uno qualsiasi di questi su poche migliaia di righe e l'intero lotto fallisce, spesso con un errore che punta alla riga sbagliata Un convertitore applica tutte queste regole allo stesso modo ogni volta, che è l'intera proposta di valore.

Come fa lo strumento a decidere ogni tipo di colonna's?

Questa è la parte che separa un convertitore utile da uno stupido Uno strumento ingenuo rende ogni colonna TEXT e lo chiama fatto, il che tecnicamente funziona ma ti dà una tabella in cui i numeri si ordinano come stringhe e non puoi fare aritmetica senza lanciare Il Toolz Strumento CSV-SQL invece esegue la scansione di ogni valore in una colonna e sceglie il tipo più stretto che si adatta a tutti loro.

La logica è semplice una volta che la vedi Se ogni valore non vuoto in una colonna è un numero intero, la colonna diventa di tipo intero Se ogni valore è numerico ma alcuni hanno punti decimali, diventa di tipo variabile Se ogni valore è la parola true oppure false, diventa un booleano Qualsiasi altra cosa, compresi i contenuti misti e qualsiasi cosa con le lettere, ricade sul testo C'è una deliberata eccezione che cattura le persone in un buon modo: un valore come 007 oppure 00123 rimane testo, non un numero intero, perché uno zero iniziale significa quasi sempre che la colonna è un ID, un codice postale o un numero di telefono in cui lo zero è significativo e verrebbe perso se memorizzato come numero Quella singola regola mi ha salvato dal corrompere silenziosamente più di una colonna SKU del prodotto.

Puoi disattivare l'inferenza se preferisci che ogni colonna sia testo, che è la scelta sicura quando prevedi di alterare i tipi da solo dopo il caricamento. Ma per il caso comune, lasciare che lo strumento deduca i tipi significa che è stato generato CREATE TABLE corrisponde ai dati invece di appiattirli e la tabella è utilizzabile nel momento in cui esiste.

Quale dialetto SQL dovrei scegliere?

SQL è uno standard nel modo in cui l'ortografia inglese è uno standard, vale a dire che ogni database ha il proprio accento Il dialetto scelto cambia tre cose nell'output: come vengono citati gli identificatori, come vengono chiamati i nomi dei tipi e come sono scritti vero e falso Ecco come differiscono le quattro opzioni.

Preoccupazione	MYSQL	Postgresql	SQLite	SQL standard
Citazione identificativa	Controcorse `col`	Quotazioni doppie "col"	Quotazioni doppie "col"	Quotazioni doppie "col"
Tipo intero	INT	INTEGER	INTEGER	INTEGER
Tipo decimale	DOUBLE	DOUBLE PRECISION	REAL	REAL
Tipo booleano	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
Tipo di testo	VARCHAR(255)	TEXT	TEXT	TEXT
Valori booleani	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

La guida pratica è quella di abbinare il dialetto al database in cui stai effettivamente caricando, perché le differenze non sono estetiche MySQL rifiuterà gli identificatori a doppia citazione che PostgreSQL desidera a meno che tu non sia in modalità ANSI e PostgreSQL non lo abbia TINYINT. SQLite non ha alcun tipo booleano reale, quindi i booleani diventano numeri interi, motivo per cui lo strumento scrive 1 e 0 sia per MySQL che per SQLite ma TRUE e FALSE per PostgreSQL e SQL standard Se non sei sicuro o stai scrivendo qualcosa di portatile, SQL standard è la scelta più conservativa Una volta generate le istruzioni puoi sempre eseguirle attraverso il Formattatore SQL per stampare in modo ottimale l'output prima che entri in un file di migrazione.

Come faccio a convertire un CSV in SQL con lo strumento?

Il flusso è volutamente breve Incolla il tuo CSV nella casella di input, oppure fai clic su Carica campione per vedere un esempio funzionante con un id, un nome, un ruolo, un booleano e una colonna dello stipendio.

Imposta il nome della tabella su qualunque sia la chiamata della tabella di destinazione e scegli il tuo dialetto SQL. Quindi decidi le opzioni di output Lascia & quot; Includi CREA TABELLA& quot; attiva se la tabella non esiste ancora, oppure disattivala per generare solo il INSERT istruzioni quando si sta caricando in una tabella che è già presente Mantenere & quot; Inferisci tipi di colonna& quot; su per una tabella digitata, o fuori per rendere tutto testo Scegliere & quot; Inserimento e quot multiriga; per un'istruzione compatta con molte tuple di valore, che si carica più velocemente, o spegnerlo per ottenerne una INSERT per riga, che è più amichevole per il controllo della versione e ti consente di eseguire righe individualmente Lascia & quot; Celle vuote come NULL" su a meno che tu non voglia specificamente archiviate stringhe vuote.

Il delimitatore può essere rilevato automaticamente o impostato a mano Rileva automaticamente i punteggi virgola, punto e virgola, scheda e pipe in base alla coerenza con cui ciascuno divide le prime diverse righe nello stesso numero di colonne, che gestisce correttamente i file europei punto e virgola e le esportazioni separate da schede. Fare clic su Converti in SQL e l'output viene visualizzato con un conteggio di righe e colonne, oltre a eventuali avvisi sui nomi di colonne duplicati o righe frastagliate. Copiarlo ed eseguirlo Se l'esportazione è arrivata senza riga di intestazione, disattiva & quot; La prima riga è intestazione& quot; e lo strumento nomina le colonne column_1, column_2, e così via, tratta quindi ogni riga come dati.

Perché il valore corretto che sfugge così tanto è importante?

Poiché l'escape errato non è solo un bug, è una classe di vulnerabilità della sicurezza L'apostrofo che ha interrotto manualmente la mia prima importazione è lo stesso meccanismo dietro SQL injection: una singola citazione all'interno di un valore, se non sfuggita, termina anticipatamente la stringa e lascia che tutto ciò che segue venga interpretato come SQL. In un'applicazione si risolve questo problema con query parametrizzate, in cui il driver del database mantiene i valori e il codice rigorosamente separati Quando si genera uno script SQL statico da un CSV, non si ha quella separazione, quindi l'escape deve essere corretto nel testo generato stesso.

Lo strumento segue la regola ANSI SQL: un valore di testo è avvolto in virgolette singole e qualsiasi singola virgoletta all'interno del valore è raddoppiata Così O'Brien's Tools diventa 'O''Brien''s Tools', che ognuno dei database supportati legge come stringa originale. Numeri e booleani vengono emessi senza virgolette in modo che vengano memorizzati come il tipo giusto e gli identificatori vengono citati con il dialetto & #39; proprio carattere quindi una colonna chiamata order oppure select non si scontra con una parola riservata Questo è esattamente il tipo di noiosa correttezza che gli esseri umani sbagliano sotto la pressione del tempo e uno strumento va bene ogni volta, che è il punto centrale di automatizzarlo Se sei curioso di sapere come la fuga si confronta tra i formati, il Convertitore CSV a JSON affronta lo stesso problema dal lato JSON, dove il personaggio dell'escape è una barra rovesciata anziché una citazione raddoppiata.

È sicuro convertire i dati sensibili CSV online?

Per questo strumento, sì, e il motivo è architetturale piuttosto che una promessa su una pagina Ogni passaggio, analizzando il CSV, deducendo tipi di colonne, valori di fuga e costruendo le istruzioni, viene eseguito come JavaScript all'interno della scheda del browser Non c'è caricamento, nessun server di andata e ritorno e non viene registrato o archiviato nulla Puoi dimostrarlo aprendo il tuo browser's scheda di rete e facendo clic su Converti: nessuna richiesta lascia la pagina Una volta caricata la pagina puoi disconnetterti completamente da Internet e continua a generare SQL.

Ciò è importante perché le persone CSV convertite in SQL sono spesso i file più sensibili di un'azienda Tabelle clienti, cronologie degli ordini, record utente e dati sui prezzi viaggiano tutti come CSV prima di atterrare in un database Un convertitore che carica il tuo file su un server, per quanto ben intenzionato, trasforma un foglio di calcolo privato in qualcun altro's log entry L'elaborazione lato client rimuove completamente la domanda: i dati non lasciano mai la macchina su cui è iniziata. Tale impostazione predefinita è deliberata su ogni strumento su Toolz.dev e ho scritto l'argomento più lungo nel guida alla privacy dei dati per gli strumenti online per chiunque voglia il ragionamento completo.

Dove si inserisce CSV in SQL in un flusso di lavoro reale?

La conversione è raramente l'intero lavoro, è una stazione in una pipeline Il modello che ho colpito più spesso è il seeding: un client invia un foglio di calcolo, lo converto in uno script SQL ed eseguo quello script per popolare un database di staging o di sviluppo in modo che l'app abbia dati realistici su cui lavorare Poiché l'output è uno script semplice piuttosto che una connessione live, è facile controllare in un repo come file seed, rivedere in una richiesta pull e rieseguire in qualsiasi ambiente.

Gli strumenti intorno ad esso dipendono da ciò che i dati stanno facendo dopo Se ho bisogno di guardare il file come una griglia ordinabile prima di convertire, il Visualizzatore CSV rende la stessa analisi RFC 4180 di una tabella in modo da poter individuare una riga non valida prima che diventi malformata INSERT. Se la destinazione è un'API o un file di configurazione anziché un database, il Convertitore CSV a JSON è invece il luppolo giusto e suo fratello JSON a CSV gestisce il contrario quando devo restituire i dati come foglio di calcolo E una volta che l'SQL esiste, il Formattatore SQL riordina in qualcosa di leggibile per una migrazione Nessuno di questi carica i tuoi dati, così puoi incatenarli sullo stesso file privato senza pensarci due volte Se stai assemblando un kit per questo tipo di lavoro sui dati, mio Guida al kit per gli sviluppatori web cammina attraverso il modo in cui i pezzi si collegano.

Quali sono i limiti che vale la pena conoscere?

L'onestà sui limiti fa parte della fiducia in uno strumento Il convertitore genera standard CREATE TABLE e INSERT istruzioni, il che significa che non deduce chiavi primarie, chiavi esterne, indici o vincoli di colonna, perché nessuna di queste informazioni esiste in un CSV piatto. Sceglie un tipo ragionevole per ciascuna colonna, ma VARCHAR(255) per MySQL il testo è un valore predefinito, non una misurazione; se hai una colonna di lunghe descrizioni potresti volerla ampliare TEXT dopo il caricamento, e se hai una colonna che dovrebbe essere DATE oppure DATETIME si vorrà alterarlo, dal momento che lo strumento tratta le date come testo per evitare di indovinare un formato sbagliato.

La multi-fila INSERT è compatto e veloce, ma i file molto grandi producono un'unica istruzione molto lunga e alcuni database limitano la dimensione di un'istruzione o il numero di segnaposto Se stai caricando decine di migliaia di righe e raggiungi un limite, passa a uno INSERT per riga, che scambia uno script più grande per le istruzioni che un database accetterà sempre Infine, lo strumento è per generare script di caricamento, non per lo streaming di un file multi-gigabyte, perché l'intero input vive nella memoria del browser Per le esportazioni quotidiane, i file seed e gli infissi, che è ciò che la maggior parte delle persone ha effettivamente, nessuno di questi limiti morde Sapere che esistono è solo la differenza tra usare bene uno strumento ed esserne sorpresi.

Domande frequenti

Come faccio a convertire un file CSV in SQL? Incolla il CSV nel Convertitore CSV a SQL, imposta il nome della tabella e scegli il tuo dialetto SQL. Legge la riga dell'intestazione come nomi di colonna, deduce ogni tipo di colonna e genera a CREATE TABLE positivo INSERT dichiarazioni che puoi copiare ed eseguire nel tuo database Tutto accade nel tuo browser, quindi il file non viene mai caricato.

Quali database SQL supporta? Il convertitore emette istruzioni per MySQL, PostgreSQL, SQLite e SQL standard Il dialetto scelto controlla la quotazione dell'identificatore e il tipo e la sintassi booleana, quindi lo script viene eseguito in quel database senza modifiche MySQL utilizza backtick e TINYINT(1) booleani, mentre PostgreSQL e SQL standard utilizzano virgolette doppie e TRUE oppure FALSE.

Come decide i tipi di colonna? Ogni colonna viene controllata rispetto a ogni valore nei dati Se tutti i valori non vuoti sono numeri interi la colonna diventa un tipo intero, tutti i numeri diventano un tipo decimale, tutti i valori veri o falsi diventano un tipo booleano e qualsiasi altra cosa diventa testo Un valore con uno zero iniziale come 007 rimane il testo perché di solito è un identificatore significativo È possibile disattivare l'inferenza per creare ogni testo di colonna.

Crea il tavolo o solo gli inserti? Entrambi di default Emette un CREATE TABLE istruzione con i tipi di colonna dedotti seguita dal INSERT dichiarazioni. Puoi girare il CREATE TABLE spento quando la tabella esiste già e devi solo caricare le righe.

Come vengono gestite le citazioni e i personaggi speciali? I valori di testo sono racchiusi in virgolette singole e qualsiasi singola virgoletta all'interno di un valore viene raddoppiata, che è l'escape SQL standard, quindi O'Brien diventa 'O''Brien'. I numeri e i booleani non sono citati e gli identificatori sono citati con backtick per MySQL o virgolette doppie per gli altri dialetti, quindi una parola riservata come order non infrange l'affermazione.

Cosa succede alle celle vuote? Per impostazione predefinita una cella vuota diventa NULL, che di solito è ciò che si desidera per un valore mancante Se invece si preferisce memorizzare una stringa vuota, disattivare l'opzione vuoto come NULL e le celle vuote diventano due virgolette singole.

Posso convertire un CSV che non ha riga di intestazione? Sì. Disattiva l'opzione di intestazione e lo strumento nomina le colonne column_1, column_2, e così via, poi tratta ogni riga come dati Questo è utile per le esportazioni grezze che spediscono senza una riga di intestazione.

I miei file CSV vengono caricati da qualche parte? No. Parsing e generazione SQL vengono eseguiti come JavaScript nel browser, Nulla viene trasmesso, registrato o memorizzato e lo strumento continua a funzionare offline una volta caricato, che è possibile confermare guardando la scheda di rete mentre si converte.