Ensure

Warum deine CSV in Excel falsch geöffnet wird

Kommas, Semikolons und Tabulatoren je nach Locale, dazu Einfassung, Escaping, das UTF-8-BOM und ein verlässlicher Weg herauszufinden, welches Trennzeichen eine Datei wirklich benutzt.

Excel sieht nicht in eine .csv-Datei hinein, um herauszufinden, wie sie getrennt ist. Wenn du doppelt darauf klickst, nimmt es das Listentrennzeichen aus den Regionseinstellungen deines Betriebssystems, und wenn das nicht zur Datei passt, landet jede Zeile in einer einzigen Spalte.

Im Vereinigten Königreich und in den USA ist das Listentrennzeichen ein Komma. In weiten Teilen Kontinentaleuropas und in großen Teilen Lateinamerikas, wo das Dezimaltrennzeichen ein Komma ist, ist das Listentrennzeichen ein Semikolon. Eine gültige, kommagetrennte Datei, die aus London gemailt wird, öffnet sich in Amsterdam, Berlin oder Madrid also als eine zusammengequetschte Spalte, und mit einer Semikolondatei passiert dasselbe umgekehrt. Nichts ist beschädigt; die Datei wird nur mit der falschen Annahme gelesen.

Drei Wege, sie richtig zu öffnen

  1. Importieren statt öffnen. In Excel unter Daten, dann Daten abrufen oder Aus Text/CSV, und wähle Trennzeichen und Codierung im Dialog. Das ist der einzige Weg, der dir volle Kontrolle gibt, und der einzige, der es übersteht, in ein anderes Land geschickt zu werden.
  2. Benenne die Datei in .txt um. Bei einer .txt-Datei weigert sich Excel zu raten und zeigt stattdessen den Importassistenten, der dich nach dem Trennzeichen fragt.
  3. Wandle die Datei auf das Trennzeichen um, das die Gegenseite erwartet, bevor du sie verschickst. Wenn du weißt, dass der Empfänger in einer Semikolon-Locale arbeitet, schick ihm eine Semikolondatei.

Die sep=-Zeile

Excel und LibreOffice verstehen beide eine besondere erste Zeile, die die Locale-Einstellung überschreibt:

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

Das funktioniert und ist eine verbreitete Abhilfe für „der Kunde kann meinen Export nicht öffnen“. Sei dir bewusst, dass es eine Konvention der Tabellenkalkulationen ist und in keiner CSV-Spezifikation steht. Gib dieselbe Datei an ein Skript oder einen Datenbankimport, und sep=; wird als gewöhnliche erste Zeile gelesen und meist zu einer unsinnigen Spaltenüberschrift. Nimm es für Dateien, die für einen Menschen mit einer Tabellenkalkulation bestimmt sind, nicht für den Austausch zwischen Maschinen.

Einfassung und Escaping

Die informelle Referenz für CSV ist RFC 4180, das die gängige Praxis eher beschreibt als vorschreibt. Seine Regeln sind kurz:

  • Ein Feld muss in doppelte Anführungszeichen gesetzt werden, wenn es das Trennzeichen, ein doppeltes Anführungszeichen oder einen Zeilenumbruch enthält.
  • Ein doppeltes Anführungszeichen innerhalb eines eingefassten Feldes wird doppelt geschrieben.
id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

Zeile 1 hat drei Felder, nicht vier, denn das Komma steht innerhalb der Anführungszeichen. Die verdoppelten Anführungszeichen fallen beim Parsen auf einfache zusammen. Zeile 2 enthält einen echten Zeilenumbruch in einem eingefassten Feld, was zulässig ist und der Grund dafür, dass du eine CSV nicht verlässlich durch Zählen von Zeilen aufteilen kannst.

Escaping mit Backslash (\, oder \") ist eine andere Konvention, die einige Datenbankwerkzeuge nutzen. Es ist nicht RFC 4180, und ein strenger Parser versteht es nicht; wenn deine Datei Backslashes verwendet, teile das dem lesenden Programm also ausdrücklich mit.

Der häufigste Defekt in freier Wildbahn ist eine Datei, die per Zeichenkettenverkettung geschrieben wurde und in der ein Wert mit einem Komma oder einem verirrten Anführungszeichen nie eingefasst wurde. Das erzeugt Zeilen mit mehr Feldern als die Kopfzeile, und genau darauf zeigt ein Validator.

Das BOM, und warum akzentuierte Zeichen zu Mojibake werden

Ein Byte Order Mark (BOM) sind drei unsichtbare Bytes ganz am Anfang einer Datei, die sie als UTF-8 kennzeichnen. Excel unter Windows hat sich historisch darauf verlassen: Ohne BOM fällt es womöglich auf eine alte regionale Codierung zurück, sodass Müller als Müller und café als café erscheint.

Der Haken ist, dass andere Parser es nicht immer entfernen. Wenn dein Skript eine erste Spalte namens \ufeffid statt id meldet oder eine Suche nach "id" auf rätselhafte Weise nur bei der ersten Spalte fehlschlägt, ist ein BOM der Übeltäter. Lies die Datei mit der Codierung utf-8-sig, und es verschwindet.

Faustregel: Nimm das BOM in Dateien auf, die in Excel geöffnet werden sollen, und lass es in Dateien weg, die von Software geparst werden sollen.

Zeilenenden

Windows-Werkzeuge schreiben \r\n ans Ende jeder Zeile, Unix-Werkzeuge \n. Die meisten Parser kommen mit beidem klar, aber ein naives Aufteilen an \n lässt in jeder Zeile einen Wagenrücklauf am letzten Feld kleben, und deshalb ist ein Wert, der wie UK aussieht, im Vergleich nicht gleich "UK".

Wie du erkennst, welches Trennzeichen eine Datei wirklich benutzt

Öffne die Datei in einem reinen Texteditor, nie in einer Tabellenkalkulation, denn die hat ihre Vermutung bereits angestellt. Dann:

  1. Sieh dir die ersten zwei oder drei Zeilen an. Welches Kandidatenzeichen (Komma, Semikolon, Tabulator, senkrechter Strich) steht zwischen dem, was eindeutig Feldwerte sind?
  2. Zähle jeden Kandidaten über mehrere Zeilen. Das echte Trennzeichen ergibt in jeder Zeile dieselbe Anzahl, nämlich eine weniger als die Zahl der Spalten. Ein Zeichen, das in einer Zeile siebenmal und in der nächsten zweimal auftaucht, sind Daten, keine Struktur.
  3. Zähle dabei außerhalb der eingefassten Abschnitte. In "Smith, Ada" ist das Komma Inhalt, und es naiv mitzuzählen ist der Grund, warum Kommas in einer wirklich kommagetrennten Datei uneinheitlich aussehen.
  4. Achte auf Dezimalkommas. Eine europäische Datei hat oft sowohl Semikolons als Trennzeichen als auch Kommas innerhalb von Zahlen, wie in Ada;1.234,56;UK. Wenn du Kommas ausschließlich zwischen Ziffern siehst, sind es Dezimaltrennzeichen und keine Feldtrenner.
  5. Prüfe die ersten Bytes auf eine sep=-Zeile oder ein BOM, bevor du irgendetwas folgerst.

Tabulatoren verdienen eine eigene Erwähnung. Eine tabulatorgetrennte Datei ist im Allgemeinen das Sicherste, was du einer Tabellenkalkulation geben kannst, denn Tabulatoren tauchen fast nie in echten Werten auf, und Excel kommt über den Importweg gut damit zurecht. Der Haken ist, dass Tabulatoren unsichtbar sind; eine Datei, in der jemand Spalten mit Leerzeichen ausgerichtet hat, sieht identisch aus und parst zu nichts Brauchbarem.

Sobald du das Trennzeichen kennst, ist die Umwandlung der Datei mechanisch: Lies sie mit dem richtigen Trennzeichen und der richtigen Einfassung und schreib sie dann mit dem Zeichen aus, das dein Ziel erwartet, oder wandle sie nach JSON um und umgehe die Trennzeichenfrage ganz.