CSV naar XLSX omzetten

Een CSV is een tekstbestand. Er staan waarden en scheidingstekens in en verder niets: geen typen, geen opmaak, geen aanwijzing wat een kolom hoort te zijn. Als Excel er een opent, moet het dat allemaal al lezend raden, kolom voor kolom, zonder te weten waar de gegevens voor dienen.

Die gokjes zijn berucht om hun schade. Een postcode 01234 wordt 1234. Een artikelcode 0071 wordt 71. Een rekeningnummer van zestien cijfers wordt 1,23457E+15 en wordt in sommige versies zo afgerond dat de oorspronkelijke cijfers niet meer terug te halen zijn. Alles wat op een datum lijkt wordt er een — zo werd een gen met de naam SEPT2 opeens 2 september, een probleem dat ernstig genoeg was dat genetici hun genen hebben hernoemd in plaats van te blijven vechten met spreadsheets.

Een XLSX is een spreadsheet waarin de typen al vastliggen. Elke cel draagt zelf of hij tekst of getal is, dus Excel opent het bestand in plaats van het te interpreteren, en de waarden die je ziet zijn de waarden die zijn weggeschreven.

De omzetting hier leest de CSV één keer, beslist per waarde op grond van wat er staat, en schrijft hem weg als wat hij is. Wat onmiskenbaar een getal is wordt een getal; wat dat niet is blijft tekst, voorloopnullen en al. Het is geen toverij en het kan je gedachten niet lezen over een kolom cijfers die allebei kan zijn — maar de beslissing valt één keer en zichtbaar, in plaats van in een importvenster waar verschillende mensen verschillend op antwoorden.

De andere helft van de CSV-ellende is het scheidingsteken, en in het Nederlands ken je die goed. Het formaat is naar komma’s vernoemd en gebruikt er vaak geen enkele: exports uit systemen die op Nederlands, Duits, Frans of vrijwel elke Europese landinstelling staan gebruiken doorgaans puntkomma’s, omdat de komma daar al dienstdoet als decimaalteken.

Open zo’n bestand in de verwachting van komma’s en elke rij belandt in één kolom. Hier wordt het scheidingsteken uit het bestand zelf afgeleid, wat de gewone gevallen afdekt, en je kunt het expliciet instellen bij de opties onder het resultaat als de detectie ernaast zat — nog eens omzetten en het is in een seconde geregeld. Tabs gaan op dezelfde manier, wat de exports dekt die eigenlijk TSV zijn onder een naam op .csv.

Het is goed om duidelijk te zijn over het plafond. Opmaak, kleuren, kolombreedtes, geblokkeerde deelvensters, formules, grafieken, filters en meerdere bladen overleven de omzetting niet, omdat ze nooit in het bestand hebben gezeten. Een CSV bevat een raster van waarden; al het andere dat een spreadsheet tot spreadsheet maakt ontbreekt bij ontwerp.

Wat je krijgt is een schone werkmap van één blad met je gegevens correct getypeerd — en dat is het juiste vertrekpunt. Opmaken kost een minuut in Excel; een kolom postcodes terughalen die drie stappen geleden hun voorloopnullen kwijtraakten kost aanzienlijk langer.

Kijk naar wat er meestal in CSV’s staat. Klantgegevens, orderhistorie, loonstroken, mailinglijsten, exports uit een CRM of een webwinkel. Dat komt aardig in de buurt van de definitie van gegevens die je niet afgeeft aan een bedrijf dat je via een zoekresultaat hebt gevonden.

Deze omzetting draait volledig in je browser: de parser en de schrijver van de werkmap zijn allebei JavaScript dat de pagina heeft binnengehaald, en het bestand wordt van je eigen schijf in je eigen tabblad gelezen. Er wordt niets verstuurd, en dat kun je nagaan in plaats van geloven: open de ontwikkelaarshulpmiddelen, kijk op het netwerktabblad en zet iets om. Het betekent ook dat er geen groottelimiet is die door een upload wordt opgelegd, en geen wachtrij achter andere mensen.

De andere manier waarop een CSV kapot binnenkomt is de tekencodering. Een bestand dat als UTF-8 is weggeschreven en met een oude codetabel wordt gelezen verandert letters met accenten in paren symbolen; andersom gelezen verdwijnen ze helemaal.

Het betrouwbare teken is een woord dat je kent. Staat er «Ã©én» waar «één» hoort te staan, dan kijk je naar UTF-8 dat als Windows-1252 wordt gelezen. Staat er een vraagteken, dan is de informatie al weg en moet de export opnieuw. In het Nederlands verraadt een ij-ligatuur of een trema zich snel genoeg. Deze pagina leest UTF-8, wat vrijwel elke huidige export oplevert.

XLSX heeft een harde grens van 1.048.576 rijen en 16.384 kolommen per blad. Een CSV heeft helemaal geen grens, dus het is heel goed mogelijk dat je een bestand vasthoudt dat niet in één werkblad past.

Zit je export boven de rijlimiet, dan is een spreadsheet de verkeerde bestemming en niet een klein probleem om omheen te werken. Zoveel gegevens vragen om een database of een Parquet-bestand, en hier omzetten zou alleen betekenen dat alles voorbij de miljoenste rij verdwijnt.

De eerste rij wordt de koprij van het blad. Er wordt niets hernoemd of opgeschoond, dus hoe de export zijn kolommen noemde is wat je krijgt.

Dertig seconden vóór het omzetten zijn goed besteed, want kopnamen reizen verder dan mensen verwachten: draaitabellen, grafieklegenda’s en elke latere import gebruiken ze. Een kolom die col_7 heet rechtzetten kost een moment in de CSV en overleeft elke kopie van het bestand.

Zet er zoveel CSV’s neer als je wilt. Elk wordt zijn eigen werkmap en ze komen terug als ZIP. Ze worden niet samengevoegd tot één werkmap met meerdere bladen, want dan zou besloten moeten worden welk bestand welk blad is, en in welke volgorde.

Is één werkmap met meerdere bladen wat je echt wilt, zet ze dan afzonderlijk om en schuif de bladen daarna in Excel bij elkaar. Zo is het samenvoegen een beslissing die jij hebt genomen en niet een die een omzetter namens jou nam.