Varför din CSV öppnas fel i Excel

Excel tittar inte inuti en .csv-fil för att lista ut hur den är avgränsad. När du dubbelklickar på en använder det listavgränsaren från operativsystemets regionala inställningar, och om den inte matchar filen hamnar varje rad i en enda kolumn.

I Storbritannien och USA är listavgränsaren ett komma. I större delen av kontinentaleuropa och stora delar av Latinamerika, där decimaltecknet skrivs som ett komma, är listavgränsaren ett semikolon. Så en giltig kommaavgränsad fil som mejlas från London öppnas som en ihoptryckt kolumn i Amsterdam, Berlin eller Madrid, och en semikolonfil gör detsamma åt andra hållet. Ingenting är trasigt; filen läses med fel antagande.

Tre sätt att öppna den rätt

1. Importera i stället för att öppna. I Excel: Data, sedan Hämta data eller Från text/CSV, och välj avgränsare och teckenkodning i dialogrutan. Det är den enda vägen som ger dig full kontroll, och den enda som överlever att filen skickas till ett annat land.
2. Byt filändelse till .txt. Excel vägrar gissa för en .txt-fil och visar importguiden i stället, som frågar dig vad avgränsaren är.
3. Konvertera filen till den avgränsare mottagaren förväntar sig innan du skickar den. Om du vet att mottagaren har en semikolonspråkinställning, skicka en semikolonfil.

Raden med sep=

Excel och LibreOffice förstår båda en särskild första rad som åsidosätter språkinställningen:

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

Det fungerar, och det är en vanlig lösning på "kunden kan inte öppna min export". Var medveten om att det är en kalkylbladskonvention, inte en del av någon CSV-specifikation. Mata in samma fil i ett skript eller en databasimport och sep=; läses som en helt vanlig första rad, oftast som en falsk kolumnrubrik. Använd det för filer som är på väg till en människa med ett kalkylprogram, inte för utbyte mellan maskiner.

Citattecken och escaping

Den informella referensen för CSV är RFC 4180, som beskriver vanlig praxis snarare än föreskriver den. Dess regler är korta:

• Ett fält måste omges av dubbla citattecken om det innehåller avgränsaren, ett dubbelt citattecken eller en radbrytning.
• Ett dubbelt citattecken inuti ett citerat fält skrivs två gånger.

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

Rad 1 har tre fält, inte fyra, eftersom kommat ligger innanför citattecken. De dubblerade citattecknen faller ihop till enkla vid tolkningen. Rad 2 innehåller en riktig radbrytning inuti ett citerat fält, vilket är tillåtet och är varför du inte tillförlitligt kan dela en CSV genom att räkna rader.

Escaping med omvänt snedstreck (\, eller \") är en annan konvention som används av vissa databasverktyg. Den är inte RFC 4180 och en strikt parser förstår den inte, så om din fil använder omvända snedstreck, säg det uttryckligen till det som ska läsa den.

Det vanligaste haveriet i det vilda är en fil som skrivits genom strängsammanfogning, där ett värde som innehöll ett komma eller ett vilset citattecken aldrig citerades alls. Det ger rader med fler fält än rubriken, vilket är precis vad en validerare pekar på.

BOM:en, och varför accenttecken blir mojibake

En byteordningsmarkering (BOM) är tre osynliga bytes allra först i en fil som märker den som UTF-8. Excel på Windows har historiskt förlitat sig på den: utan en BOM kan det falla tillbaka på en äldre regional teckenkodning, så att Müller dyker upp som Müller och café som café.

Avvägningen är att andra parsrar inte alltid tar bort den. Om ditt skript rapporterar en första kolumn som heter \ufeffid i stället för id, eller om en uppslagning på "id" mystiskt misslyckas bara på den första kolumnen, är en BOM boven. Läs filen med teckenkodningen utf-8-sig så försvinner den.

Tumregel: ta med BOM:en i filer som ska öppnas i Excel, utelämna den i filer som ska tolkas av programvara.

Radslut

Windows-verktyg skriver \r\n i slutet av varje rad, Unix-verktyg skriver \n. De flesta parsrar klarar båda, men en naiv uppdelning på \n lämnar en avslutande vagnretur fastklistrad vid det sista fältet på varje rad, vilket är varför ett värde som ser ut som UK vägrar vara lika med "UK" i jämförelser.

Hur du avgör vilken avgränsare en fil faktiskt använder

Öppna filen i en vanlig textredigerare, aldrig i ett kalkylprogram, eftersom kalkylprogrammet redan har gjort sin gissning. Sedan:

1. Titta på de första två eller tre raderna. Vilket kandidattecken (komma, semikolon, tabb, lodstreck) står mellan det som uppenbart är fältvärden?
2. Räkna varje kandidat på flera rader. Den verkliga avgränsaren ger samma antal på varje rad, ett mindre än antalet kolumner. Ett tecken som förekommer sju gånger på en rad och två på nästa är data, inte struktur.
3. Gör den räkningen utanför citerade avsnitt. I "Smith, Ada" är kommat innehåll, och att räkna det naivt är vad som får komman att se inkonsekventa ut i en fil som verkligen är kommaavgränsad.
4. Håll utkik efter decimalkomman. En europeisk fil har ofta både semikolonavgränsare och komman inuti tal, som i Ada;1.234,56;UK. Om du bara ser komman mellan siffror är de decimaltecken, inte avgränsare.
5. Kontrollera de första byten efter en sep=-rad eller en BOM innan du drar några slutsatser.

Tabbar förtjänar ett särskilt omnämnande. En tabbavgränsad fil är i allmänhet det tryggaste att räcka ett kalkylprogram, eftersom tabbar nästan aldrig förekommer inuti verkliga värden och Excel hanterar dem väl via importvägen. Haken är att tabbar är osynliga, så en fil där någon har radat upp kolumner med mellanslag ser identisk ut och tolkas till ingenting användbart.

När du väl vet avgränsaren är konverteringen av filen mekanisk: läs den med rätt avgränsare och citering, och skriv sedan ut den med den som din destination förväntar sig, eller konvertera den till JSON och slipp avgränsarfrågan helt.