Convertire XLSX in SQL

L’uscita è un file di istruzioni INSERT, una per riga del foglio, ognuna che nomina la tabella ed elenca le colonne dall’intestazione. Non c’è un CREATE TABLE in cima ed è deliberato: un foglio di calcolo non ha tipi, lunghezze, chiavi o vincoli, quindi qualsiasi schema derivato da esso sarebbe invenzione presentata come fatto.

Un’ipotesi sbagliata in una riga è un record cattivo. Un’ipotesi sbagliata in uno schema è un VARCHAR(50) che tronca indirizzi per due anni. L’assunzione qui è che abbiate già progettato la tabella, vero per quasi chiunque abbia bisogno di questa conversione, e ciò che volete è la parte noiosa scritta correttamente.

Il nome del foglio di calcolo, meno l’estensione, diventa la tabella in ogni istruzione. Viene sanificato prima: qualsiasi cosa non sia una lettera, cifra o underscore diventa un underscore, e un nome che inizia con una cifra ottiene un prefisso. Un file chiamato “2024 ordini.xlsx” inserisce quindi in _2024_ordini.

Il consiglio pratico segue direttamente. Rinominate il file esattamente come la tabella in cui state caricando prima di convertire, e l’uscita gira così com’è. Farlo al contrario significa una ricerca e sostituzione su cinquantamila righe, che funziona ed è un passo in più dove qualcuno sostituisce la stringa sbagliata.

I numeri vengono scritti nudi. Il testo viene avvolto in virgolette singole con qualsiasi apostrofo interno raddoppiato, la forma ANSI che ogni motore importante capisce — O’Brien diventa O”Brien tra virgolette raddoppiate. L’escape con barra rovesciata non viene usato deliberatamente, perché è un’estensione MySQL.

Una cella vuota diventa la parola chiave NULL piuttosto che una stringa vuota, quasi sempre la lettura giusta di un vuoto in un foglio di calcolo. I valori stessi non vengono riformattati lungo il percorso. Un numero appare come lo memorizza il foglio di calcolo.

Una cella booleana viene scritta come il letterale TRUE o FALSE. Postgres lo accetta direttamente, MySQL li tratta come alias per 1 e 0, e SQLite li accetta da anni. SQL Server no: il suo tipo bit vuole 1 e 0, e un’istruzione contenente TRUE non si analizza.

È una correzione di due secondi una volta saputo — sostituite i letterali prima di eseguire lo script — e venti minuti fastidiosi se lo scoprite da un errore sulla quattrocentesima istruzione. Se la destinazione è SQL Server, fate prima la sostituzione.

Un foglio di calcolo contiene una data come conteggio di giorni con un formato di visualizzazione addosso, e la conversione scrive il valore piuttosto che l’aspetto. Il primo gennaio 2024 diventa 45292, contato dalla fine di dicembre 1899, e un timestamp diventa quel numero con una frazione attaccata.

Inserito in una colonna numerica atterra silenziosamente ed è sbagliato; inserito in una colonna data viene rifiutato, il che è almeno rumoroso. Correggetelo nel foglio di calcolo convertendo la colonna in testo con un formato non ambiguo, o caricate in una colonna di staging e convertite dentro il database.

Un codice memorizzato come testo nel foglio viene scritto come stringa tra virgolette, quindi 007 arriva come ’007’ e un riferimento account con zeri iniziali entra intatto in una colonna VARCHAR. È l’opposto di ciò che succede quando gli stessi dati passano per un CSV.

Il test utile per sapere se una colonna è un identificativo è se sommare due dei suoi valori significherebbe qualcosa. Codici postali, codici articolo, numeri di telefono e codici di riferimento falliscono tutti questo test e appartengono a una colonna di testo anche nella tabella di destinazione.

Ogni istruzione nomina esplicitamente le sue colonne piuttosto che affidarsi all’ordine delle colonne della tabella, il che è ciò che fa sopravvivere il file a una tabella che nel frattempo ha guadagnato una colonna. L’insieme delle colonne è l’unione dei campi trovati in tutto il foglio.

Quella prolissità ha un costo da sapere. La lista delle colonne si ripete su ogni riga, quindi il file è più grande dei dati che contiene: cinquantamila righe di sei colonne sono uscite a circa 5,7 MB di SQL, contro circa 4,3 MB per il .xlsx da cui è stato fatto.

Le istruzioni vengono emesse una per riga senza transazione attorno e senza raggruppamento VALUES multi-riga. Eseguito così com’è contro Postgres o MySQL, sono cinquantamila viaggi di andata e ritorno e cinquantamila commit impliciti, abbastanza lento da essere notabile.

Avvolgere il file in BEGIN e COMMIT risolve entrambi i problemi e costa due righe. Per caricamenti genuinamente grandi lo strumento migliore è il percorso bulk — COPY in Postgres, LOAD DATA in MySQL, .import in SQLite — che legge un file delimitato direttamente ed è di un ordine di grandezza più veloce.

Il primo foglio del file di calcolo è quello che viene convertito, perché una serie di istruzioni INSERT si indirizza a una tabella. Se il foglio che volete non è il primo, spostatelo e convertite di nuovo.

Tutto avviene localmente: il foglio di calcolo viene analizzato e le istruzioni scritte nella pagina, senza caricamento e senza richiesta che porti il file. Conta più per questa coppia che per la maggior parte, dato che un foglio di calcolo diretto verso un database è di solito record clienti o transazioni.

Se la tabella di destinazione è vuota e il volume è grande, generare SQL è la via lenta. Ogni database ha un loader bulk che legge un file delimitato direttamente, applica i tipi una volta e scrive a blocchi, e batterà comodamente uno script di istruzioni INSERT su qualsiasi cosa oltre qualche migliaio di righe.

Le istruzioni INSERT si guadagnano il loro posto quando il caricamento è piccolo, quando deve essere revisionato o depositato nel controllo versione prima di girare, quando verrà riprodotto contro diversi ambienti, o quando l’unico accesso che avete è una finestra di query.