Waarom je CSV verkeerd opent in Excel

Excel kijkt niet in een .csv bestand om te bepalen hoe het gescheiden is. Als je erop dubbelklikt, gebruikt het het lijstscheidingsteken uit de regio-instellingen van je besturingssysteem, en als dat niet bij het bestand past, belandt elke rij in één kolom.

In het Verenigd Koninkrijk en de Verenigde Staten is het lijstscheidingsteken een komma. In het grootste deel van continentaal Europa en een groot deel van Latijns-Amerika, waar het decimaalteken als komma geschreven wordt, is het lijstscheidingsteken een puntkomma. Een geldig door komma's gescheiden bestand dat vanuit Londen gemaild wordt, opent in Amsterdam, Berlijn of Madrid dus als één samengeperste kolom, en met een puntkommabestand gebeurt hetzelfde andersom. Er is niets beschadigd; het bestand wordt met de verkeerde aanname gelezen.

Drie manieren om het goed te openen

1. Importeren in plaats van openen. In Excel: Gegevens, dan Gegevens ophalen of Van tekst/CSV, en kies het scheidingsteken en de codering in het venster. Dit is de enige route die je volledige controle geeft, en de enige die overleeft wanneer het bestand naar een ander land gaat.
2. Hernoem het bestand naar .txt. Excel weigert bij een .txt bestand te gokken en toont in plaats daarvan de importwizard, die je vraagt wat het scheidingsteken is.
3. Zet het bestand om naar het scheidingsteken dat de lezer verwacht voordat je het verstuurt. Als je weet dat de ontvanger een puntkomma-landinstelling heeft, stuur hem dan een puntkommabestand.

De sep= regel

Excel en LibreOffice begrijpen allebei een speciale eerste regel die de landinstelling overschrijft:

sep=;
name;email;country
Ada;ada@example.com;UK

Het werkt, en het is een gangbare oplossing voor "de klant kan mijn export niet openen". Houd er rekening mee dat het een spreadsheetconventie is en geen onderdeel van enige CSV-specificatie. Geef hetzelfde bestand aan een script of een database-import en sep=; wordt gelezen als een gewone eerste rij, meestal met een onzinnige kolomkop tot gevolg. Gebruik het voor bestanden die bij een mens met een spreadsheet terechtkomen, niet voor uitwisseling tussen machines.

Aanhalingstekens en escapen

De informele referentie voor CSV is RFC 4180, die de gangbare praktijk beschrijft in plaats van hem voor te schrijven. De regels zijn kort:

• Een veld moet tussen dubbele aanhalingstekens staan als het het scheidingsteken, een dubbel aanhalingsteken of een regeleinde bevat.
• Een dubbel aanhalingsteken binnen een aangehaald veld wordt twee keer geschreven.

id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

Rij 1 heeft drie velden, niet vier, omdat de komma tussen aanhalingstekens staat. De verdubbelde aanhalingstekens worden bij het parsen weer enkele. Rij 2 bevat een echt regeleinde binnen een aangehaald veld, wat is toegestaan en de reden is dat je een CSV niet betrouwbaar kunt splitsen door regels te tellen.

Escapen met backslashes (\, of \") is een andere conventie die sommige databasetools gebruiken. Het is geen RFC 4180 en een strikte parser begrijpt het niet, dus als je bestand backslashes gebruikt, zeg dat dan expliciet tegen wat het leest.

De meest voorkomende breuk in de praktijk is een bestand dat met stringconcatenatie is geschreven, waarbij een waarde met een komma of een verdwaald aanhalingsteken nooit tussen aanhalingstekens is gezet. Dat levert rijen op met meer velden dan de kop, en dat is precies het soort ding waar een validator naar wijst.

De BOM, en waarom letters met accenten in mojibake veranderen

Een byte order mark (BOM) bestaat uit drie onzichtbare bytes helemaal aan het begin van een bestand die het als UTF-8 markeren. Excel op Windows heeft er van oudsher op vertrouwd: zonder BOM kan het terugvallen op een oude regionale codering, waardoor Müller als Müller verschijnt en café als café.

De keerzijde is dat andere parsers hem niet altijd weghalen. Als je script een eerste kolom meldt die \ufeffid heet in plaats van id, of als een lookup op "id" op raadselachtige wijze alleen bij de eerste kolom mislukt, is een BOM de boosdoener. Lees het bestand met codering utf-8-sig en hij verdwijnt.

Vuistregel: neem de BOM op in bestanden die in Excel geopend moeten worden, en laat hem weg uit bestanden die door software geparsed worden.

Regeleindes

Windows-tools schrijven \r\n aan het eind van elke rij, Unix-tools schrijven \n. De meeste parsers kunnen met beide overweg, maar een naïeve split op \n laat op elke rij een carriage return aan het laatste veld geplakt, en daarom weigert een waarde die eruitziet als UK bij vergelijkingen gelijk te zijn aan "UK".

Hoe je bepaalt welk scheidingsteken een bestand echt gebruikt

Open het bestand in een gewone teksteditor, nooit in een spreadsheet, want de spreadsheet heeft zijn gok al gedaan. Vervolgens:

1. Kijk naar de eerste twee of drie regels. Welk kandidaat-teken (komma, puntkomma, tab, pipe) staat er tussen wat duidelijk veldwaarden zijn?
2. Tel elke kandidaat op een aantal regels. Het echte scheidingsteken geeft op elke regel hetzelfde aantal, één minder dan het aantal kolommen. Een teken dat op de ene regel zeven keer voorkomt en op de volgende twee keer, is data en geen structuur.
3. Doe dat tellen buiten de aangehaalde stukken. In "Smith, Ada" is de komma inhoud, en die naïef meetellen is wat komma's inconsistent laat lijken in een bestand dat wel degelijk door komma's gescheiden is.
4. Let op decimale komma's. Een Europees bestand heeft vaak zowel puntkomma's als scheidingsteken als komma's binnen getallen, zoals in Ada;1.234,56;UK. Als je komma's alleen tussen cijfers ziet staan, zijn het decimaaltekens en geen scheidingstekens.
5. Controleer de eerste bytes op een sep= regel of een BOM voordat je iets concludeert.

Tabs verdienen een aparte vermelding. Een door tabs gescheiden bestand is over het algemeen het veiligste dat je een spreadsheet kunt geven, omdat tabs vrijwel nooit binnen echte waarden voorkomen en Excel er via de importroute goed mee omgaat. Het addertje is dat tabs onzichtbaar zijn, dus een bestand waarin iemand kolommen met spaties heeft uitgelijnd ziet er identiek uit en parst tot niets bruikbaars.

Zodra je het scheidingsteken kent, is het bestand omzetten mechanisch werk: lees het met het juiste scheidingsteken en de juiste aanhalingsregels, en schrijf het weer weg met wat je bestemming verwacht, of zet het om naar JSON en sla de vraag over scheidingstekens helemaal over.