Lange getallen en voorloopnullen behouden als je een csv in Excel opent

Wat Excel doet met een bestand vol ID's en codes

Een csv-bestand is platte tekst zonder celtypen. Dubbelklik je erop, dan gokt Excel per waarde of het een getal, een datum of tekst is. Het verzonnen voorbeeldbestand hieronder bevat precies het soort waarden waarbij die gok misgaat.

Beide ordernummers verschijnen als 4.02177E+19, maar dat is alleen de weergave. Daaronder heeft Excel slechts de eerste 15 cijfers bewaard, waardoor beide cellen nu dezelfde waarde bevatten: 40217730091152400000. Een bredere kolom laat die nullen zien, niet de oorspronkelijke cijfers, en een VERT.ZOEKEN of controle op dubbele waarden ziet de twee orders als één.

Wetenschappelijke notatie is alleen weergave, verloren cijfers zijn echt weg

4.02177E+19 is alleen hoe een te smalle kolom met de notatie Standaard een groot getal toont; een bredere kolom of de getalnotatie laat meer zien. Het echte verlies gebeurt eerder: Excel slaat getallen op met een precisie van 15 significante cijfers, dus elk cijfer vanaf het 16e wordt bij de omzetting een nul. Een creditcard- of rekeningnummer van 16 cijfers verliest zo al zijn laatste cijfer.

• Voorloopnullen verdwijnen omdat 00412 en 412 hetzelfde getal zijn. Een aangepaste notatie zoals 00000 toont weer vijf cijfers, maar in de cel staat nog steeds 412, en codes van wisselende lengte zijn zo niet te herstellen.
• Lange ID's verliezen elk cijfer na het 15e. Geen notatie, formule of tool kan ze terughalen uit een bestand dat na de omzetting is opgeslagen.
• Codes als 3-12 of 12E3 worden een datum of een getal in wetenschappelijke notatie, en de oorspronkelijke tekst staat niet meer in de cel.
• Waarden die met = beginnen ziet Excel als formules, die het vervangt door hun uitkomst.

Maak in plaats daarvan een xlsx met tekstcellen

Anders dan een csv slaat een .xlsx-werkmap voor elke cel een type op, en een tekstcel toont Excel precies zoals hij geschreven is. De converter CSV naar Excel schrijft standaard elke waarde als tekstcel. Hij werkt in je browser en uploadt het bestand niet.

1. Open je csv in de converter CSV naar Excel, of kies Voorbeeld proberen om de rijen van hierboven te laden.
2. Controleer de tabel onder Invoer: ID's, nullen en codes staan er precies zoals in het bestand.
3. Laat Waardetypen op Alle waarden als tekst bewaren staan.
4. Kies XLSX downloaden en open de werkmap in Excel.

In de werkmap zijn 00412, alle 20 cijfers van beide ordernummers, +15550100234, 3-12, 12E3 en =SUM(F2:F3) tekstcellen. Een waarde die op een formule lijkt, wordt als gewone tekst opgeslagen, dus bij het openen wordt niets berekend. Excel kan tekst die op een getal lijkt markeren met een klein groen driehoekje (Getal opgeslagen als tekst); de waarde zelf blijft ongewijzigd.

Als je daarnaast echte getallen nodig hebt

Getallen en true/false herkennen maakt een kolom alleen numeriek als elke waarde erin een gewoon getal is dat Excel ongewijzigd kan opslaan. In het voorbeeld worden quantity en unit_price getallen, terwijl customer_id (voorloopnullen), order_ref (meer dan 15 cijfers), part_code en note tekst blijven.

De herkenning beoordeelt de vorm van de waarden, niet hun betekenis, en beslist steeds voor een hele kolom. Een kolom met alleen waarden als +15550100234 leest als gehele getallen met een teken, en een kolom met alleen waarden als 12E3 als wetenschappelijke notatie. In het voorbeeld zouden phone en lot_code dus ook getallen worden. Houd voor zulke bestanden de standaardinstelling aan en zet in Excel alleen de cellen met aantallen om: selecteer ze en kies Converteren naar getal via het waarschuwingspictogram.

Als je in Excel vanuit de csv moet beginnen

Is een omgezette werkmap geen optie, dubbelklik dan niet op de csv maar importeer hem, en zet de kwetsbare kolommen op tekst voordat er iets geladen wordt. In de huidige Excel voor Microsoft 365 gaat dat via Gegevens > Van tekst/CSV > Gegevens transformeren: verwijder de automatische stap Type gewijzigd, zet de ID-kolommen op tekst en laad de gegevens. De handleiding csv-bestand importeren in Excel beschrijft dit uitgebreid, net als de oudere wizard Tekst importeren.

Recente builds van Excel voor Microsoft 365 en Excel 2024 hebben in de gegevensopties ook instellingen voor automatische gegevensconversie, die kunnen voorkomen dat voorloopnullen, lange getallen, E-notatie en tekst die op een datum lijkt worden omgezet. Welke instellingen er zijn en hoe ze heten, hangt af van versie en platform, en ze gelden alleen voor je eigen Excel, niet voor dat van een collega.

Wat de waarden niet beschermt

• Aanhalingstekens rond de waarde ("00412") vertellen een csv-lezer alleen waar een waarde begint en eindigt. Excel zet getallen tussen aanhalingstekens gewoon om.
• Een UTF-8-bytevolgordemarkering (BOM) helpt Excel om letters als é, ë en ï en andere schriften goed te lezen. Over celtypen zegt ze niets.
• De kolom na het openen als tekst opmaken komt te laat, want de omzetting is dan al gebeurd.
• Formule-omhulsels zoals ="00412" werken alleen waar een spreadsheet formules uitrekent. Andere csv-lezers, databases en scripts zien de letterlijke tekst ="00412", zodat de gegevens in het bestand niet langer het ID zijn.
• Een apostrof vooraan ('00412) is geen formule: getypt in Excel markeert hij de invoer als tekst en wordt hij niet getoond. In een csv-bestand is het echter gewoon een teken, en andere programma's krijgen '00412 als waarde.
• Een echte tekstcel in een xlsx bevat de kale waarde 00412 zonder extra tekens. Dat schrijft de converter, dus ViewCSV voegt geen omhulsels en geen apostrofs toe.

Grenzen

De converter verwerkt bestanden tot 25 MB, 100.000 rijen, 250 kolommen en 2 miljoen cellen, met één werkblad per werkmap. Een bestand dat al met afgeronde ID's of verdwenen nullen is opgeslagen, wordt door omzetten niet hersteld; exporteer het opnieuw uit het bronsysteem. De tests van ViewCSV controleren de celtypen in de gemaakte xlsx met een onafhankelijke lezer; hoe een bepaalde Excel-versie ze op het scherm opmaakt, kan verschillen.