Waarom CSV-bestanden stukgaan in Excel: codering, scheidingstekens en voorloopnullen

1. De drie grote frustraties bij het openen van CSV in Excel

Het Comma-Separated Values (CSV) formaat is ogenschijnlijk het eenvoudigste gegevensformaat ter wereld: eenvoudige platte tekst waarin rijen gescheiden worden door regeleinden en kolommen door een scheidingsteken. Toch is het openen van een CSV-bestand in Microsoft Excel op Windows een van de meest voorkomende bronnen van ergernis onder data-analisten, marketeers en ontwikkelaars.

Vrijwel iedereen herkent de symptomen: diakritische tekens zoals trema's (ë, ï) en accenten (é, è) veranderen in cryptische tekens zoals ë of é, alle kolommen worden op één hoop gegooid in kolom A, of Nederlandse postcodes (0123 AB) en telefoonnummers verliezen plotseling hun voorloopnul (123). Deze problemen hebben duidelijke technische oorzaken.

2. Tekencodering: waarom UTF-8 BOM (\uFEFF) onmisbaar is voor Excel

Moderne programmeertalen en web-API's exporteren tekst standaard als standaard UTF-8 zonder bytevolgordemarkering (BOM). Microsoft Excel voor Windows gaat er bij het dubbelklikken op een CSV-bestand echter historisch van uit dat het bestand gecodeerd is in de verouderde lokale ANSI/Windows-1252 tekenset.

Omdat UTF-8 speciale tekens (zoals €, ë of é) codeert als twee of drie afzonderlijke bytes, interpreteert Excel die binaire bytes als afzonderlijke ANSI-karakters (mojibake). De oplossing is het toevoegen van een UTF-8 Byte Order Mark (BOM: 0xEF, 0xBB, 0xBF of \uFEFF) aan het allereerste begin van het bestand. Zodra Excel deze 3 bytes detecteert, schakelt de parser automatisch over naar correcte UTF-8 weergave.

3. Scheidingstekens: de komma versus de Europese puntkomma (;)

In de Angelsaksische wereld (VS, VK) wordt een punt gebruikt als decimaalscheidingsteken (10.50) en een komma als veldscheidingsteken in CSV (Jan,de Vries,10.50). In Nederland, België, Duitsland en het grootste deel van continentaal Europa is de komma echter het officiële decimaalscheidingsteken (10,50).

Als een CSV-bestand in een Europese Excel-versie komma's als kolomscheidingstekens gebruikt, raakt het rekenblad in de war tussen decimale komma's en kolomscheiders. De standaard Windows regionale instellingen in Nederland en België verwachten daarom een puntkomma (;) als scheidingsteken in CSV-bestanden. Een alternatief is om bovenaan het CSV-bestand expliciet de regel sep=, of sep=; toe te voegen.

4. Het verdwijnen van voorloopnullen (postcodes, telefoonnummers en BSN)

Excel probeert behulpzaam te zijn door data automatisch te typen. Ziet Excel een reeks cijfers zoals 00123 of 0612345678, dan interpreteert het dit als een numeriek getal en stript meedogenloos alle voorloopnullen: 123 en 612345678. Voor Nederlandse postcodes, artikelnummers, IBAN's of BSN-nummers is dit funest.

Zelfs het omsluiten van de waarde met dubbele aanhalingstekens ("00123") helpt bij het dubbelklikken in Excel vaak niet. Twee beproefde methoden om dit definitief op te lossen:

• Formule-escapen: Exporteer de waarde als een Excel-tekstformule: ="00123". Excel behandelt dit gegarandeerd als vaste tekst en behoudt alle nullen.
• Gegevens importeren via Power Query: Open Excel, ga naar Gegevens > Van tekst/CSV en stel het kolomtype handmatig in op "Tekst" in plaats van automatisch detecteren.

5. CSV-bestanden veilig genereren en converteren met GoToolstack

De GoToolstack JSON ↔ CSV Converter lost al deze problemen automatisch voor u op:

1. Gegevens plakken: Voer uw JSON-array of CSV-bestand in het linkerpaneel in.
2. Instellingen kiezen: Kies uw gewenste scheidingsteken (komma voor internationale systemen of puntkomma voor de Nederlandse Excel-omgeving).
3. Automatische UTF-8 BOM: Schakel de optie "Excel-compatibele UTF-8 BOM toevoegen" in.
4. Exporteren: Download het bestand direct. U kunt het nu zonder enige waarschuwing of vervorming direct dubbelklikken in Microsoft Excel.