Artikel
Waarom Excel je CSV-bestanden verminkt
Een patiënt-ID die met drie nullen begint. Een gen genaamd SEPT2. Een rekeningnummer van zestien cijfers. Open een van deze in een spreadsheet als platte CSV en Excels importfunctie toont ze niet zomaar, ze herschrijft ze: de nullen verdwijnen, de gennaam wordt een datum, en het rekeningnummer verliest zijn laatste cijfers. Niets hiervan is precies een bug, het is wat er gebeurt als een algemene getal-en-datumraadmachine tekst tegenkomt die er alleen uitziet als een getal of een datum. Dit is wat er echt gebeurt met een CSV-bestand zodra Excel het opent, en hoe je het kunt tegenhouden voordat het gebeurt.
Voorloopnullen verdwijnen
Typ 02134 in een cel en Excels standaard kolomtype, Standaard, leest het als een getal en laat de voorloopnul vallen: 2134. Dit treft elke code die als cijfers is opgeslagen maar eigenlijk geen hoeveelheid is: Amerikaanse postcodes, Franse postcodes, werknemers- of factuurnummers, telefoonnummers. Technisch gaat er niets verloren uit het onderliggende CSV-bestand, de tekst naast de cel blijft ongewijzigd, tot je de werkmap opslaat, waarna Excel terugschrijft wat het toonde, en de nullen zijn voorgoed weg. De oplossing is om Excel te vertellen dat de kolom tekst is voordat het de kans krijgt om te raden: via Gegevens > Uit tekst/CSV (of Gegevens ophalen > Uit bestand) kun je het gegevenstype van elke kolom expliciet instellen, of een apostrof voor een waarde in een enkele cel zetten om tekst af te dwingen.
Tekst verandert ongevraagd in datums
Dezelfde raadmachine zoekt ook naar datumachtige tekst. Een cel met MAR1 of SEPT2, gelezen als 1 maart of 2 september, wordt stilletjes omgezet naar een datumwaarde en opnieuw geformatteerd, zonder waarschuwing. Dit is niet hypothetisch: in 2020 hernoemde het HUGO Gene Nomenclature Committee ongeveer 27 menselijke gensymbolen, waaronder SEPT2 en MARCH1, specifiek omdat spreadsheetsoftware ze steeds in datums veranderde telkens een dataset werd geopend, een probleem dat ernstig genoeg was dat een enquête uit 2016 onder genomica-publicaties dit soort autocorrectiefouten aantrof in ongeveer één op de vijf publicaties die Excel-supplementen meestuurden. De les geldt breder dan gennamen: elke korte alfanumerieke code die toevallig op een dag en maand lijkt, loopt risico zodra iemand jouw CSV in een spreadsheet opent, niet alleen die van jou.
Zeer lange getallen verliezen hun laatste cijfers
Excel slaat elk getal op als een drijvendekommawaarde met 15 significante cijfers precisie, een limiet die Microsoft rechtstreeks documenteert. Een kaart- of rekeningnummer van zestien cijfers zoals 4111111111111111 wordt afgerond om te passen, waardoor het laatste cijfer of twee stilletjes nul worden en de waarde niet meer die is die je typte. De cel achteraf formatteren brengt de ontbrekende cijfers niet terug, omdat het onderliggende opgeslagen getal al is afgerond; de enige oplossing is de kolom als tekst importeren, of de waarde vooraf laten gaan door een apostrof, voordat Excel het ooit als een getal behandelt.
Tekst met accenten verandert in rommel
Codering is een aparte foutmodus dan autocorrectie. Een CSV-bestand zonder byte-order mark bestaat uit platte bytes; als het als UTF-8 is opgeslagen, opent Excel op Windows het meestal ervan uitgaand dat het de codepagina van het systeem zelf gebruikt, waardoor letters met accenten, valutasymbolen en emoji als mojibake verschijnen, een tekenreeks als café komt eruit als iets dat dichter bij café ligt. Het veilige pad bij het maken van een CSV voor Excel is om op te slaan als CSV UTF-8 of een UTF-8 byte-order mark toe te voegen; het veilige pad bij het gebruiken van een CSV is te importeren via Gegevens > Uit tekst/CSV, waarmee je de broncodering expliciet kunt kiezen in plaats van te vertrouwen op een dubbelklik die goed raadt.
Data tussen formaten verplaatsen zonder ze in Excel te openen
Niets van dit alles is echt een Excel-bug, het is wat een spreadsheets getal-en-datumraadmachine juist hoort te doen, en het geldt ongeacht hoe het bestand wordt geopend, niet alleen in Excel. De veiligste manier om een CSV-bestand opnieuw vorm te geven of te controleren dat een kolom echt tekst blijft voordat hij een spreadsheet bereikt, is er rechtstreeks aan te werken: onze dataconverter verwerkt CSV met een standaard quoting-bewuste parser en herinterpreteert nooit het type van een waarde, dus een postcode of gennaam blijft precies de tekst die je hebt gegeven. Ze draait volledig in je browser, niets van wat je plakt wordt ergens naartoe geüpload. Dat beschermt alleen de conversiestap: als de uitvoer daarna nog in een spreadsheet wordt geopend, markeer de gevoelige kolommen daar dan ook als tekst.
Tools in dit artikel
Veelgestelde vragen
Hoe stop ik Excel met het verwijderen van voorloopnullen uit een CSV?
Open het bestand niet door erop te dubbelklikken. Importeer het in plaats daarvan via Gegevens > Uit tekst/CSV (of Gegevens ophalen), wat een kolom-voor-kolom gegevenstypekiezer toont; zet de kolom met postcode, ID of telefoonnummer op Tekst voordat je de import afrondt. Een snelle oplossing per cel is een apostrof voor de waarde te typen, zoals '02134, wat tekst afdwingt zonder te veranderen wat er wordt getoond.
Waarom veranderde Excel mijn ID of code in een datum?
Excels kolomtype Standaard zoekt actief naar datumachtige tekst, patronen zoals een kort woord plus een getal, of twee getallen gescheiden door een streepje of schuine streep, en zet alles om dat overeenkomt, zonder om bevestiging te vragen. Dit is precies wat de hernoeming van gensymbolen zoals SEPT2 en MARCH1 in 2020 afdwong. Importeer de kolom als Tekst, op dezelfde manier als bij voorloopnullen, om de gok te voorkomen voordat hij plaatsvindt.
Vermijdt het op een andere manier converteren van mijn CSV deze problemen helemaal?
Het vermijdt ze voor die conversiestap, niet voor altijd. Een parser die elk veld als platte tekst behandelt, zoals onze dataconverter, herschrijft geen postcode of gennaam bij het omzetten van een CSV naar JSON. Maar als de uitvoer uiteindelijk toch in een spreadsheet wordt geopend, door jou of door wie hem ontvangt, krijgt de eigen importfunctie van dat programma opnieuw de kans om de waarden te herinterpreteren, dus moeten de gevoelige kolommen ook daar als tekst worden gemarkeerd.