Een rommelige CSV-export opschonen: de problemen in volgorde

Om een rommelige CSV-export op te schonen, pak je het in lagen aan: eerst de tekens, dan de indeling, en dan wat elke kolom betekent. Een CSV is platte tekst, dus de meeste problemen zijn verkeerde tekens, extra rijen of scheidingstekens, of waarden die een programma anders leest dan jij. Los eerst de tekens op. Een verborgen spatie kan ervoor zorgen dat een datum niet als datum wordt herkend, en dan falen de latere stappen zonder duidelijke reden.

CSV-opschonerRommelige CSV, lege en dubbele rijenGratis doen

Tekens die goed lijken en het niet zijn

Overtollige spaties. Een spatie aan het begin of eind van een cel, of twee spaties midden in een waarde, zorgt ervoor dat „Berlin ” en „Berlin” voor een lookup, filter of join twee verschillende waarden zijn.

Onzichtbare tekens. Sommige tekens nemen geen ruimte in op het scherm:

• Een harde spatie (U+00A0) ziet eruit als een gewone spatie, maar is een ander teken. Webpagina's gebruiken die om woorden bij elkaar te houden.
• Een nulbreedte-spatie (U+200B) heeft helemaal geen breedte.
• Een byte order mark (U+FEFF) kan vooraan in een bestand staan, vóór de eerste kop. De kolom lijkt „id” maar is in werkelijkheid „id”, dus een lookup naar „id” mislukt.

Verborgen tekens verwijderen laat zien wat het vindt voordat er iets verandert.

Kapotte letters. „é” waar je „é” verwacht, is een encodingprobleem dat mojibake heet. Het bestand is opgeslagen als UTF-8, waar é twee bytes krijgt, en daarna gelezen als Windows-1252, waar die twee bytes twee ongerelateerde letters worden. Een apostrof „’” wordt op dezelfde manier „’”. De oplossing is het bestand als UTF-8 lezen, of de export opnieuw maken vanuit de bron met UTF-8 geselecteerd. Tekst die al verkeerd is opgeslagen repareren is wankel, dus bewaar het origineel en werk met een kopie.

Indeling: titelrijen, lege regels en duplicaten

Rapporten beginnen vaak met een titel, een datum en een lege regel boven de echte kop. Verwijder alles boven de kopregel, zodat de eerste regel de kolomnamen bevat. Elke kolom heeft één naam nodig, en geen enkele naam mag leeg zijn.

Elke rij moet hetzelfde aantal velden hebben. Een losse komma binnen een waarde die niet tussen aanhalingstekens staat, voegt een veld toe, en elke waarde daarna schuift één kolom naar rechts. Tel de velden per rij. Een rij met een afwijkend aantal is kapot.

Duplicaten komen in twee soorten. Rijen die in elke kolom identiek zijn, ontstaan meestal doordat je twee keer exporteert of plakt, en die kun je veilig verwijderen. Rijen die een sleutel delen maar elders verschillen, kunnen updates zijn. Als je de verkeerde verwijdert, kun je de nieuwste versie kwijtraken, dus controleer ze eerst.

Datums: 03/04/2026 kan twee verschillende dagen zijn

03/04/2026 is 3 april in het ene land en 4 maart in het andere, en de tekst zegt niet welke. Kijk naar de andere datums in dezelfde kolom. Als een eerste getal hoger is dan 12, is de kolom dag-eerst. Als een tweede getal hoger is dan 12, is de kolom maand-eerst. Als elke waarde beide getallen op 12 of lager heeft, kan het bestand het niet zeggen en moet je het bronsysteem vragen. De Datatype-checker markeert zo'n kolom als dubbelzinnig in plaats van te gokken.

Zodra je de volgorde kent, zet je elke datum om naar ISO 8601, de vorm jaar-maand-dag zoals 2026-04-03. Die is ondubbelzinnig en sorteert correct als tekst. Zet gemengde formaten één voor één om, want de meeste programma's lezen een kolom met beide stijlen onbetrouwbaar.

Getallen opgeslagen als tekst

Een getal is als tekst opgeslagen als het een valutateken, een procentteken of een spatie erin heeft, zoals „€ 12”, „45%” of „1 234” met een harde spatie. Duizendtalscheidingstekens, zoals in „1,234.50”, zorgen er vaak ook voor dat een programma de waarde als tekst leest. Een spreadsheet toont tekstgetallen links uitgelijnd, en sommen slaan ze over.

Decimale komma's zijn de valkuil. In „1.234,50” is de komma het decimaalteken en groepeert de punt de duizendtallen. In „1,234.50” is het andersom. Dus „1,234” kan in de ene export 1234 zijn en in een andere 1.234. Achterhaal de locale van de bron voordat je converteert, verwijder daarna de duizendtalscheidingstekens en maak van de decimale komma een punt.

Voorloopnullen gaan verloren

Postcodes, productcodes en rekeningnummers beginnen vaak met nullen. Wanneer een CSV in een spreadsheet wordt geopend, wordt „01234” gelezen als het getal 1234, en de nullen zijn weg. Opslaan houdt dat verlies vast, dus het bronsysteem is de enige betrouwbare kopie.

Een identificatie is tekst, ook als die op een getal lijkt, omdat je er nooit mee rekent. Zet de getalconversie uit voor zulke kolommen wanneer je de CSV opschoont, en open het bestand opnieuw vanuit de bron in plaats van vanuit een kopie die een spreadsheet heeft opgeslagen. Controleer het datatype van elke kolom daarna, zodat een postcodekolom niet stilletjes weer naar getallen springt. Excel kan waarden ook veranderen wanneer het een CSV opent, dus controleer het resultaat in een gewone tekstweergave.

Controleer de types en profileer daarna

Controleer na het opschonen het type van elke kolom. Eén „N/A” in een kolom met prijzen maakt de hele kolom tekst, en het totaal klopt dan niet. De Datatype-checker geeft elke kolom een type en een betrouwbaarheidsscore, en somt de cellen op die er niet in passen.

Profileer daarna de kolommen. De CSV-kolomprofiler toont lege plekken en uitschieters per kolom. Een uitschieter kan een echte waarde zijn, of een fout, zoals een prijs die duizend keer groter is dan de rest. Dat komt vaak door een verkeerd geplaatst decimaalteken of scheidingsteken.

Randgevallen

• Regeleindes binnen aanhalingstekens. Een aangehaalde waarde kan een regeleinde bevatten. Een tool die het bestand per regel splitst, breekt die rij in tweeën, dus gebruik een lezer die aangehaalde velden begrijpt.
• Puntkomma-bestanden. Veel Europese spreadsheets slaan CSV op met puntkomma's, omdat de komma in hun locale het decimaalteken is. Controleer het scheidingsteken voordat je de kolommen leest.
• Bewaar het origineel. Schrijf het opgeschoonde resultaat naar een nieuw bestand en bewaar de export zoals hij was.

De volgorde, als checklist

1. Bewaar een kopie van de originele export.
2. Verwijder overtollige spaties en onzichtbare tekens. Los kapotte letters bij de bron op als dat kan.
3. Verwijder de titelrijen, controleer de kopnamen en bevestig dat elke rij hetzelfde aantal velden heeft.
4. Zoek de duplicaten en beslis welke versie je bewaart.
5. Zet datums om naar jaar-maand-dag, verwijder scheidingstekens en maak identificaties tekst.
6. Controleer het type van elke kolom en profileer die daarna op lege plekken en uitschieters.

Korte antwoorden

Waarom mislukt mijn lookup terwijl de waarden identiek lijken?

Meestal zit er een spatie, een harde spatie of een byte order mark in een van de cellen. Verwijder de verborgen tekens en probeer het opnieuw.

Waarom veranderen mijn datums in iets anders?

Een programma heeft een dag-eerst- of maand-eerst-datum in de verkeerde volgorde gelezen, of het heeft de tekst omgezet naar zijn eigen datumformaat. Zet de datums eerst zelf om naar jaar-maand-dag.