Ensure

Dlaczego twój plik CSV otwiera się w Excelu niepoprawnie

Przecinki, średniki i tabulatory zależne od ustawień regionalnych, a do tego cudzysłowy, znaki ucieczki, BOM w UTF-8 oraz pewny sposób na ustalenie, jakiego separatora plik naprawdę używa.

Excel nie zagląda do środka pliku .csv, żeby ustalić, czym jest rozdzielony. Kiedy klikniesz taki plik dwa razy, program używa separatora listy z ustawień regionalnych systemu operacyjnego, a jeśli ten nie pasuje do pliku, każdy wiersz ląduje w jednej kolumnie.

W Wielkiej Brytanii i w Stanach Zjednoczonych separatorem listy jest przecinek. W większości Europy kontynentalnej i w dużej części Ameryki Łacińskiej, gdzie separatorem dziesiętnym jest przecinek, separatorem listy jest średnik. Dlatego poprawny plik rozdzielony przecinkami wysłany z Londynu otwiera się w Amsterdamie, Berlinie albo Madrycie jako jedna zgnieciona kolumna, a plik ze średnikami robi to samo w drugą stronę. Nic nie jest uszkodzone; plik jest po prostu czytany przy błędnym założeniu.

Trzy sposoby na poprawne otwarcie

  1. Zaimportuj zamiast otwierać. W Excelu wybierz Dane, potem Pobierz dane albo Z tekstu/CSV, i wskaż w oknie dialogowym separator oraz kodowanie. To jedyna droga, która daje pełną kontrolę, i jedyna, która przetrwa wysyłkę do innego kraju.
  2. Zmień rozszerzenie pliku na .txt. Excel odmawia zgadywania dla pliku .txt i zamiast tego pokazuje kreatora importu, który pyta o separator.
  3. Przekonwertuj plik na separator, którego oczekuje odbiorca, zanim go wyślesz. Jeśli wiesz, że odbiorca pracuje w ustawieniach ze średnikiem, wyślij mu plik ze średnikami.

Wiersz sep=

Excel i LibreOffice rozumieją specjalny pierwszy wiersz, który nadpisuje ustawienie regionalne:

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

To działa i jest częstym rozwiązaniem problemu "klient nie może otworzyć mojego eksportu". Pamiętaj jednak, że to konwencja arkuszy kalkulacyjnych, a nie część jakiejkolwiek specyfikacji CSV. Podaj ten sam plik skryptowi albo importowi do bazy danych, a sep=; zostanie odczytane jako zwykły pierwszy wiersz i zwykle stanie się fałszywym nagłówkiem kolumny. Używaj go do plików przeznaczonych dla człowieka z arkuszem kalkulacyjnym, a nie do wymiany między maszynami.

Cudzysłowy i znaki ucieczki

Nieformalnym punktem odniesienia dla CSV jest RFC 4180, które opisuje powszechną praktykę, zamiast ją narzucać. Jego reguły są krótkie:

  • Pole musi być ujęte w cudzysłowy podwójne, jeśli zawiera separator, cudzysłów podwójny albo znak końca wiersza.
  • Cudzysłów podwójny wewnątrz pola w cudzysłowach zapisuje się dwukrotnie.
id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

Wiersz 1 ma trzy pola, a nie cztery, ponieważ przecinek siedzi wewnątrz cudzysłowów. Podwojone cudzysłowy zwijają się przy parsowaniu do pojedynczych. Wiersz 2 zawiera prawdziwy znak końca wiersza wewnątrz pola w cudzysłowach, co jest dozwolone i dlatego nie da się rzetelnie podzielić pliku CSV, licząc wiersze.

Poprzedzanie odwrotnym ukośnikiem (\, albo \") to inna konwencja, używana przez niektóre narzędzia bazodanowe. Nie jest to zgodne z RFC 4180 i ścisły parser tego nie zrozumie, więc jeśli twój plik używa odwrotnych ukośników, powiedz o tym wprost temu, co go czyta.

Najczęstszą usterką spotykaną w praktyce jest plik zapisany przez sklejanie ciągów znaków, w którym wartość zawierająca przecinek albo zabłąkany cudzysłów w ogóle nie została ujęta w cudzysłowy. Powstają z tego wiersze z większą liczbą pól niż nagłówek, czyli dokładnie to, na co wskaże walidator.

BOM i dlaczego znaki diakrytyczne zamieniają się w krzaki

Znacznik kolejności bajtów (BOM) to trzy niewidoczne bajty na samym początku pliku, które oznaczają go jako UTF-8. Excel w systemie Windows od dawna na nim polega: bez znacznika BOM potrafi wrócić do dawnego kodowania regionalnego, więc Müller wyświetla się jako Müller, a café jako café.

Kosztem jest to, że inne parsery nie zawsze go usuwają. Jeśli twój skrypt zgłasza pierwszą kolumnę o nazwie \ufeffid zamiast id albo wyszukiwanie po "id" w tajemniczy sposób zawodzi wyłącznie na pierwszej kolumnie, winowajcą jest BOM. Wczytaj plik z kodowaniem utf-8-sig, a problem zniknie.

Reguła praktyczna: dołączaj BOM do plików przeznaczonych do otwarcia w Excelu, a pomijaj go w plikach przeznaczonych do parsowania przez oprogramowanie.

Znaki końca wiersza

Narzędzia windowsowe zapisują na końcu każdego wiersza \r\n, narzędzia uniksowe \n. Większość parserów radzi sobie z jednym i drugim, ale naiwne dzielenie po \n zostawia powrót karetki przyklejony do ostatniego pola każdego wiersza i właśnie dlatego wartość, która wygląda jak UK, odmawia równości z "UK" w porównaniach.

Jak rozpoznać, jakiego separatora plik naprawdę używa

Otwórz plik w zwykłym edytorze tekstu, nigdy w arkuszu kalkulacyjnym, bo arkusz już podjął decyzję za ciebie. Następnie:

  1. Spójrz na pierwsze dwa albo trzy wiersze. Który znak kandydat (przecinek, średnik, tabulator, kreska pionowa) pojawia się pomiędzy tym, co ewidentnie jest wartościami pól?
  2. Policz każdego kandydata w kilku wierszach. Prawdziwy separator daje tę samą liczbę w każdym wierszu, o jeden mniejszą niż liczba kolumn. Znak, który w jednym wierszu pojawia się siedem razy, a w kolejnym dwa, jest danymi, a nie strukturą.
  3. Licz poza fragmentami ujętymi w cudzysłowy. W "Smith, Ada" przecinek jest treścią, a naiwne liczenie go sprawia, że przecinki wyglądają na niekonsekwentne w pliku, który jest naprawdę rozdzielony przecinkami.
  4. Uważaj na przecinki dziesiętne. Plik europejski często ma zarówno separatory w postaci średników, jak i przecinki wewnątrz liczb, jak w Ada;1.234,56;UK. Jeśli widzisz przecinki wyłącznie pomiędzy cyframi, to separatory dziesiętne, a nie separatory pól.
  5. Zanim cokolwiek stwierdzisz, sprawdź pierwsze bajty pod kątem wiersza sep= albo znacznika BOM.

Na osobną wzmiankę zasługują tabulatory. Plik rozdzielony tabulatorami jest zwykle najbezpieczniejszą rzeczą, jaką można podać arkuszowi kalkulacyjnemu, ponieważ tabulatory prawie nigdy nie występują wewnątrz prawdziwych wartości, a Excel dobrze je obsługuje przy imporcie. Haczyk polega na tym, że tabulatory są niewidoczne, więc plik, w którym ktoś wyrównał kolumny spacjami, wygląda identycznie i parsuje się do niczego użytecznego.

Gdy już znasz separator, konwersja pliku jest czynnością mechaniczną: wczytaj go z właściwym ogranicznikiem i sposobem cytowania, a potem zapisz z tym, którego oczekuje miejsce docelowe, albo przekonwertuj go na JSON i całkowicie pomiń kwestię ogranicznika.