XLSX naar SQL omzetten

De uitvoer is een bestand met INSERT-statements, één per rij van het werkblad, elk met de tabelnaam en de kolommen uit de kopregel. Er staat geen CREATE TABLE bovenaan, en dat is bewust: een rekenblad heeft geen types, lengtes, sleutels of beperkingen, dus elk schema daaruit afgeleid zou verzinsel zijn dat als feit gepresenteerd wordt.

Een verkeerde gok in een rij is één slechte record. Een verkeerde gok in een schema is een VARCHAR(50) die twee jaar lang adressen afkapt. Heb je zelf ook een tabel nodig, schrijf dan de CREATE-instructie zelf en zet die boven het bestand.

De naam van het werkboek, zonder extensie, wordt de tabel in elke instructie. Ze wordt eerst opgeschoond: alles dat geen letter, cijfer of underscore is wordt een underscore, en een naam die met een cijfer begint krijgt er een ervoor.

Het praktische advies volgt direct. Hernoem het bestand naar precies de tabel waarin je laadt voordat je omzet, en de uitvoer draait zoals hij is. Dezelfde opschoning geldt voor de kolomnamen uit de kopregel, dus stem die vooraf af op de tabel.

Getallen worden kaal geschreven. Tekst wordt tussen enkele aanhalingstekens gezet met een apostrof erin verdubbeld, de ANSI-vorm die elke grote engine begrijpt — O'Brien wordt 'O''Brien'. Backslash-escaping wordt bewust niet gebruikt, want dat is een MySQL-uitbreiding.

Een lege cel wordt het sleutelwoord NULL in plaats van een lege tekst, meestal de juiste lezing van een leegte in een rekenblad. De waarden zelf worden niet herformatteerd onderweg — een prijs die getoond wordt als 19,99 wordt geschreven als 19.99.

Een boolean-cel wordt geschreven als het letterlijke TRUE of FALSE. Postgres accepteert dat direct, MySQL behandelt ze als aliassen voor 1 en 0, en SQLite accepteert ze al jaren. SQL Server niet: zijn bit-type wil 1 en 0.

Het is een fix van twee seconden zodra je het weet — vervang de letterlijke waarden voor je het script draait — en een irritant kwartier als je het ontdekt bij een fout op statement vierhonderd. Bekijk het eerste statement in het bestand voor je het laatste draait.

Een rekenblad slaat een datum op als een dagteller met een weergaveopmaak eromheen, en de omzetting schrijft de waarde in plaats van het uiterlijk. 1 januari 2024 wordt 45292, geteld vanaf eind december 1899.

In een numerieke kolom landt dat stilletjes en fout; in een datumkolom wordt het geweigerd, wat op zijn minst luid is. Repareer het in het werkblad door de kolom om te zetten naar tekst in een ondubbelzinnig formaat, of laad in een staging-kolom en zet binnen de database om.

Een code opgeslagen als tekst in het blad wordt geschreven als gequote string, dus 007 komt aan als '007' en een rekeningnummer met voorloopnullen komt intact in een VARCHAR-kolom. Dit is het omgekeerde van wat gebeurt als dezelfde data via een CSV en een onzorgvuldige rekenbladronde gaat.

De nuttige test voor of een kolom een identificatie is: zou het optellen van twee waarden ooit iets betekenen? Postcodes, artikelnummers en telefoonnummers falen die test allemaal, en horen in een tekstkolom in de doeltabel net als in het blad.

Elk statement noemt zijn kolommen expliciet in plaats van te vertrouwen op de volgorde van de tabel, wat het bestand overleeft in een tabel die inmiddels een kolom erbij kreeg. De set kolommen is de unie van velden gevonden over het hele blad.

Die uitvoerigheid heeft een kostenplaatje. De kolomlijst herhaalt zich op elke regel, dus het bestand is groter dan de data erin: vijftigduizend rijen van zes kolommen kwamen uit op ongeveer 5,7 MB SQL, tegen ruwweg 4,3 MB voor de .xlsx waar het van gemaakt was.

De statements worden één per rij uitgezonden zonder transactie eromheen en zonder meerregelige VALUES-batching. Zo tegen Postgres of MySQL draaien is vijftigduizend rondreizen en vijftigduizend impliciete commits, traag genoeg om op te vallen.

Het bestand omwikkelen met BEGIN en COMMIT lost beide in één keer op en kost twee regels. Voor echt grote ladingen is het bulkpad de betere weg — COPY in Postgres, LOAD DATA in MySQL, .import in SQLite.

Het eerste blad van het werkboek wordt omgezet, want een reeks INSERT-statements richt zich op één tabel. Staat het gewenste blad niet vooraan, verplaats het dan en zet opnieuw om.

Alles gebeurt lokaal: het werkboek wordt ontleed en de statements geschreven in de pagina, zonder upload. Dat telt zwaarder voor dit paar dan voor de meeste, want een rekenblad op weg naar een database is vaak klantgegevens of transacties.

Is de doeltabel leeg en het volume groot, dan is SQL genereren de langzame weg. Elke database heeft een bulklader die een gescheiden bestand rechtstreeks leest, types eenmaal toepast en in blokken schrijft.

INSERT-statements verdienen hun plek bij een kleine lading, wanneer ze gecontroleerd of geversioneerd moeten worden voordat ze draaien, of wanneer je alleen toegang hebt tot een queryvenster.