Excel naar CSV zonder kapotte accenten: UTF-8, BOM en scheidingstekens

De meeste kapotte accenten in een CSV-bestand komen door een verkeerde codering: het bestand is opgeslagen als UTF-8, maar wordt geopend als een Windows-codering. Exporteer het werkblad als CSV UTF-8, met een byte order mark (BOM) als Excel het programma is dat het bestand opent, en kies het scheidingsteken dat bij je regionale instellingen past. Zet vóór het exporteren kolommen zoals postcodes en telefoonnummers op tekst, zodat de nullen behouden blijven.

Excel naar CSVCSV nodig, maar Excel-bestandGratis doen

Waarom "é" verandert in "é"

Een teken in een bestand wordt opgeslagen als bytes. In UTF-8 bestaat de letter é uit twee bytes, C3 en A9. Een programma dat die bytes leest als Windows-1252, een veelgebruikte Windows-codering, ziet het teken à voor C3 en © voor A9. Zo verschijnt "café" als "café". Dit kun je zelf controleren: als je é codeert als UTF-8 en het resultaat decodeert als Windows-1252, krijg je é. Een ü, ñ of ö gaat op dezelfde manier kapot.

De oplossing is niet om de tekens aan te passen. Je moet het programma vertellen welke codering het bestand gebruikt.

UTF-8 en de BOM

UTF-8 is de standaardcodering voor tekst op het web en in de meeste moderne software. Een gewoon CSV-bestand heeft geen veld dat aangeeft welke codering het gebruikt, dus programma's moeten raden. Een byte order mark is een reeks van drie bytes aan het begin van het bestand, EF BB BF, die programma's die daarop letten laten zien dat het om UTF-8 gaat. Excel op Windows opent een UTF-8-bestand zonder BOM vaak met de lokale codering, dus de BOM is de gangbare oplossing.

In recente versies van Excel biedt het menu Opslaan als de optie CSV UTF-8, die de BOM toevoegt. Je kunt het bestand ook importeren via Gegevens, Uit tekst/CSV, en de bestandsoorsprong instellen op 65001, UTF-8. De BOM heeft één nadeel: een programma dat er niet op rekent, kan hem zien als onderdeel van de eerste kolomnaam. De meeste parsers en spreadsheetprogramma's kunnen daar goed mee omgaan, maar als een gegevensimporteur een vreemde kop meldt, verwijder dan de BOM.

Komma of puntkomma

CSV staat voor comma-separated values, en de specificatie RFC 4180 beschrijft velden die door komma's worden gescheiden. In veel landen, waaronder landen die een decimale komma gebruiken, zoals 1,5 voor anderhalf, is het lijstscheidingsteken van Excel een puntkomma. Als dat zo is, kan een door komma's gescheiden bestand dat in Excel wordt geopend verkeerd splitsen, en wordt een getal als 1,5 twee velden.

Kies het scheidingsteken dat het doelprogramma verwacht. De Excel naar CSV-tool biedt komma, puntkomma en tab. Als het bestand bestemd is voor een programma met een decimale komma, is een puntkomma meestal de veiligere keuze.

Datums worden reeksnummers

Excel slaat datums op als reeksnummers, door dagen te tellen vanaf een startpunt. In het datumsysteem van 1900 is 1 januari 1900 reeksnummer 1. Excel behandelt 1900 ook als schrikkeljaar, dus telt het een 29 februari 1900 mee, die nooit heeft bestaan, als reeksnummer 60. Daardoor is 1 maart 1900 reeksnummer 61 en schuift elke latere datum één dag op. De fout komt van Lotus 1-2-3, en Microsoft heeft hetzelfde systeem behouden voor compatibiliteit. Andere software kan datums van vóór 1 maart 1900 één dag verkeerd lezen.

Bij het exporteren van een datum zie je misschien 44927 in plaats van 2023-01-01, of een dag verschil bij oude datums. De tool zet datums om naar leesbare tekst. Als je handmatig exporteert, zet je de datumkolom op tekst in de ISO-vorm jjjj-mm-dd, die elk programma op dezelfde manier leest.

Voorloopnullen verdwijnen

Een postcode zoals 02134 is tekst, maar Excel behandelt een getal dat je typt als getal, dus het toont 2134. De nul is uit het werkblad verdwenen, en de CSV die daarvan wordt gemaakt bevat hem ook niet. Telefoonnummers die met 0 beginnen en lange ID-nummers hebben hetzelfde probleem. Zeer lange getallen kunnen ook cijfers verliezen, omdat Excel slechts ongeveer 15 significante cijfers bewaart.

De oplossing is die kolommen als tekst op te slaan voordat je de gegevens invoert of importeert. Als je een CSV opent die de nullen al bevat, gebruik dan Gegevens, Uit tekst/CSV en stel de kolom in op Tekst, in plaats van dubbel te klikken op het bestand.

Regels voor quotering

RFC 4180 definieert het gangbare CSV-formaat. De belangrijkste quoteringsregels zijn:

• Een veld dat een komma, een dubbele aanhalingsteken of een regeleinde bevat, moet tussen dubbele aanhalingstekens staan.
• Een dubbel aanhalingsteken binnen een tussen aanhalingstekens geplaatst veld wordt geschreven als twee dubbele aanhalingstekens.
• Regels eindigen in de specificatie met CRLF, hoewel de meeste tools ook LF accepteren.

Neem als voorbeeld de rij "Müller, Anna","She said ""ok""". Die heeft twee velden: Müller, Anna en She said "ok". Het bij IANA geregistreerde MIME-type voor CSV is text/csv.

RFC 4180 staat geen dubbel aanhalingsteken toe in een veld zonder quotering, en veel parsers weigeren zo'n rij of splitsen hem verkeerd. Zet het veld correct tussen aanhalingstekens, of verwijder het losse teken.

Een controlelijst voor je exporteert

1. Exporteer als CSV UTF-8, met een BOM als Excel het bestand gaat openen.
2. Kies het scheidingsteken dat het doelprogramma verwacht.
3. Zet kolommen met ID-achtige waarden, zoals postcodes en telefoonnummers, op tekst.
4. Schrijf datums als jjjj-mm-dd.
5. Open het bestand in een eenvoudige teksteditor en controleer de eerste regel en een voorbeeld van namen met accenten.

Verborgen tekens, zoals niet-brekende spaties, kunnen ook importfouten veroorzaken. De tool Verborgen tekens verwijderen vindt ze en verwijdert ze, en verborgen tekens in tekst legt uit waar ze vandaan komen. Als het bestand schoon is, kan CSV naar JSON het voor een ontwikkelaar omzetten, zoals beschreven in CSV naar JSON en terug.

Kijk vóór het exporteren ook naar de cellen zelf: de Excel-formulechecker toont de formules die een fout tonen of naar een ander bestand verwijzen, en dat zijn precies de cellen die leeg in een CSV terechtkomen. Na de export kun je de twee spreadsheets vergelijken om te zien wat er tussen versies is veranderd.

Snelle antwoorden

Waarom ziet mijn bestand er goed uit in het ene programma en kapot in Excel? Als een UTF-8-bestand geen BOM heeft, kan Excel op Windows het lezen met de lokale codering. De BOM toevoegen, of importeren met UTF-8 geselecteerd, lost dat op.

Hebben Mac-versies van Excel de BOM nodig? Sommige bronnen zeggen van wel, dus test met een bestand dat een naam met accenten bevat voordat je het verstuurt.

Kan ik de datums na de export nog repareren? Ja, als het nog reeksnummers zijn, maar het is beter ze vanaf het begin als jjjj-mm-dd te exporteren.

Wordt mijn spreadsheet geüpload als ik hem omzet? De tool draait in je browser, dus het bestand wordt op je eigen apparaat verwerkt. Je kunt dat controleren met de gids over uploadcontrole.