Convertire CSV in SQL

Vale la pena dirlo prima di qualsiasi altra cosa, perché per una gran parte delle persone che cercano questa conversione la risposta è che non ne hanno bisogno. Postgres ha COPY, MySQL ha LOAD DATA INFILE, SQLite ha .import, SQL Server ha BULK INSERT, e ognuno di essi legge un file delimitato direttamente, applica i tipi di colonna una volta, e scrive in blocchi. Su qualcosa sopra poche migliaia di righe quella strada batte comodamente uno script di istruzioni INSERT.

Ciò che rende questa conversione la scelta giusta è un vincolo, non una preferenza. I caricatori in blocco hanno bisogno che il file sia in un posto raggiungibile dal server, e spesso hanno bisogno di un privilegio che l’account non ha. Se tutto ciò che avete è una finestra di query (una console di database gestita, uno strumento di migrazione, un ticket di supporto che sarà eseguito da qualcun altro) allora le istruzioni sono l’unica valuta disponibile, e generarle a mano per quattrocento righe è un uso scadente di un pomeriggio.

L’output è un file di istruzioni INSERT, una per riga di dati, ognuna che nomina la tabella e elenca le colonne prese dall’intestazione. Niente le precede. È un rifiuto piuttosto che un’omissione: un CSV non registra né tipi né lunghezze né chiavi né nullabilità, quindi uno schema generato sarebbe un insieme di ipotesi presentate con l’autorità di un’istruzione DDL.

Il costo di un’ipotesi sbagliata è asimmetrico. Una riga sbagliata è un record sbagliato e una delete lo aggiusta. Un VARCHAR(50) inferito dal valore più lungo in un campione tronca gli indirizzi per due anni prima che qualcuno se ne accorga. L’assunzione qui è che la tabella sia stata progettata da chi la usa, il che è vero per quasi tutti quelli che vogliono questo file, e che quello che volete automatizzato è la parte noiosa. Se serve anche una tabella, scrivete l’istruzione CREATE e incollatela in cima; le istruzioni generate girano contro di essa senza modifiche.

Il nome del CSV, con la sua estensione rimossa, viene usato come tabella. Viene sanificato lungo il percorso: qualsiasi cosa al di fuori di lettere, cifre e underscore diventa un underscore, e un nome che inizia con una cifra ne riceve uno prefisso, dato che un identificatore non può iniziare con un numero. Un file chiamato `2024 orders.csv` produce quindi insert in `_2024_orders`.

La stessa sanificazione è applicata ai nomi delle colonne presi dalla riga di intestazione, che è la parte più probabile che vi sorprenda. Un’intestazione che legge `Order total (£)` diventa `Order_total____` e non corrisponderà a nulla nella vostra tabella. Sistemate la riga di intestazione nella sorgente per corrispondere esattamente alle colonne di destinazione, rinominate il file con il nome della tabella di destinazione, e il file generato non ha bisogno di alcuna modifica, che vale due minuti, perché l’alternativa è un trova-e-sostituisci su cinquantamila righe.

Questa è la differenza tra convertire un CSV e convertire un foglio di calcolo, ed è la cosa più probabile che vada storta. Una cella di foglio di calcolo sa se contiene testo o un numero. Un campo CSV sono caratteri, quindi il parser deduce, e qualunque cosa deduca è ciò che il writer SQL vede: un valore letto come numero viene scritto nudo, un valore lasciato come testo viene scritto tra virgolette.

La conseguenza pratica è che le colonne identificatore perdono le loro virgolette e il loro padding allo stesso tempo. Un codice postale di 01234 diventa `1234`, non quotato; un numero di telefono senza spazi diventa un intero grande; un numero di parte come `1e5` diventa `100000`. Inseriti in una colonna VARCHAR atterrano come stringhe non paddate senza errore, che è il peggior risultato possibile perché nulla si lamenta. Esportate le colonne identificatore quotate o con un prefisso non numerico, o controllatele nel file generato prima di eseguirlo: leggere la prima istruzione richiede dieci secondi e mostra la tabella, l’elenco delle colonne e un esempio di ogni tipo di valore in una volta.

Uno spazio vuoto tra due delimitatori diventa la parola chiave NULL invece di una stringa vuota. È quasi sempre la lettura giusta di un vuoto in un export, ed è la lettura che un vincolo NOT NULL rifiuterà rumorosamente invece di accettare silenziosamente, che è il comportamento che volete.

Dove un vuoto significava genuinamente una stringa vuota, il file non ha mai contenuto la distinzione e nessun convertitore può recuperarla. Se la vostra tabella ha una colonna in cui «» e NULL significano cose diverse (un campo di testo libero opzionale, più comunemente) codificate la differenza nel CSV prima di convertire, o aggiungete un COALESCE in una fase di staging. Lo stesso vale al contrario: una colonna in cui ogni vuoto dovrebbe diventare un default è meglio gestita omettendo la colonna dall’insert piuttosto che inserendo NULL sopra il default.

I numeri e i booleani sono scritti nudi. Il testo è avvolto in virgolette singole con qualsiasi apostrofo al suo interno raddoppiato, quindi O’Brien diventa «O’’Brien». Questa è la convenzione ANSI e ogni motore principale la legge allo stesso modo.

L’escape con backslash è deliberatamente assente. È un’estensione di MySQL il cui comportamento dipende dall’impostazione del server NO_BACKSLASH_ESCAPES piuttosto che dall’istruzione stessa, il che significa che un file che lo usa può girare correttamente su un server e corrompere dati su un altro con la stessa versione. Raddoppiare la virgoletta funziona ovunque, e il file risultante può essere messo sotto controllo di versione e rieseguito contro diversi ambienti senza portarsi appresso un’assunzione su nessuno di essi.

Un campo che legge `true` o `false` nel CSV è letto come booleano e scritto come letterale nudo TRUE o FALSE. Postgres lo prende direttamente, MySQL tratta le parole come alias per 1 e 0, e SQLite li accetta da anni.

SQL Server non lo fa. Il suo tipo bit vuole 1 e 0, e un’istruzione che contiene TRUE fallisce l’analisi, cosa che scoprirete alla prima istruzione se siete fortunati e alla quattrocentesima se la colonna booleana ha una serie di vuoti all’inizio. Se quella è la destinazione, fate la sostituzione prima di eseguire qualsiasi cosa, e controllate prima che nessuna colonna di testo contenga per caso la parola da sola.

Niente in questa conversione interpreta una data. Un campo che legge `2024-03-11` non è numerico, quindi resta una stringa e viene scritto tra virgolette, e un campo che legge `11/03/2024` fa lo stesso. È meglio di quanto suoni: una data ISO quotata si inserisce correttamente in una colonna di date in ogni motore qui, il che rende un CSV ben formato la più facile di tutte le sorgenti da caricare.

È anche dove l’ambiguità viaggia indisturbata. `11/03/2024` è l’undici marzo o il tre novembre a seconda di chi l’ha esportato, e il file non lo dice. Il database la analizzerà secondo le sue impostazioni di locale e non vi avviserà che ha scelto. Se il CSV proveniva da un sistema di cui non potete verificare il formato di data, caricate in una colonna di staging di testo e convertite esplicitamente, dove l’assunzione è scritta invece che ereditata.

Le istruzioni sono emesse individualmente, senza BEGIN, senza COMMIT e senza batching VALUES multi-riga. Eseguito così com’è contro un server remoto sono cinquantamila round trip e cinquantamila commit impliciti, che è abbastanza lento da notare e lascia una tabella semi-caricata se fallisce a metà.

Due righe risolvono entrambi i problemi: avvolgete il file in BEGIN e COMMIT e l’intero caricamento diventa atomico e considerevolmente più veloce. Il file è anche più grande dei dati che contiene, perché l’elenco delle colonne è ripetuto su ogni riga: una tabella larga con nomi di colonna lunghi può produrre un file SQL molte volte la dimensione del CSV da cui proviene, che vale la pena sapere prima di inviarlo via email.

Analizzare il file e scrivere le istruzioni sono entrambi semplice JavaScript che gira qui, quindi nulla viene caricato e non c’è coda; ciò che delimita il lavoro è il limite di 100 MB per file del livello gratuito e poi la vostra memoria. Questo conta per questa coppia in particolare: un CSV diretto a un database di solito sono record di clienti, transazioni o una lista di correzioni, e incollarne uno in un sito web per essere elaborato è la cosa che la maggior parte delle policy sui dati è scritta per fermare.

Il tetto è l’intera tabella tenuta in una volta, il che mette comodamente decine di megabyte in gamma. Sopra quello la risposta è il caricatore in blocco della prima sezione invece di un convertitore più grande, ed è una risposta migliore comunque.