Artykuł
Dlaczego Excel psuje twoje pliki CSV
Numer pacjenta zaczynający się od trzech zer. Gen o nazwie SEPT2. Szesnastocyfrowy numer konta. Otwórz którekolwiek z nich w arkuszu kalkulacyjnym jako zwykły plik CSV, a importer Excela nie tylko je wyświetli, on je przepisze: zera znikają, nazwa genu zamienia się w datę, a numer konta traci kilka ostatnich cyfr. Żadne z tych zachowań nie jest właściwie błędem, to po prostu to, co się dzieje, gdy ogólny zgadywacz liczb i dat spotyka tekst, który tylko wygląda jak liczba lub data. Oto, co naprawdę dzieje się z plikiem CSV w chwili, gdy otwiera go Excel, i jak temu zapobiec, zanim to nastąpi.
Zera wiodące znikają
Wpisz 02134 do komórki, a domyślny typ kolumny Excela, Ogólny, odczyta to jako liczbę i odrzuci wiodące zero: 2134. Dotyka to każdego kodu przechowywanego jako cyfry, który tak naprawdę nie jest ilością: amerykańskich kodów pocztowych, francuskich kodów pocztowych, numerów pracowników czy faktur, numerów telefonów. Technicznie nic nie ginie z bazowego pliku CSV, tekst obok komórki pozostaje niezmieniony, dopóki nie zapiszesz skoroszytu, w tym momencie Excel zapisuje z powrotem to, co wyświetlał, i zera znikają na dobre. Rozwiązaniem jest powiedzieć Excelowi, że kolumna jest tekstem, zanim będzie miał okazję zgadywać: przez Dane > Z tekstu/CSV (lub Pobierz dane > Z pliku) możesz jawnie ustawić typ danych każdej kolumny, albo dodać apostrof przed wartością w pojedynczej komórce, by wymusić tekst.
Tekst zamienia się w daty bez pytania
Ten sam zgadywacz szuka też tekstu wyglądającego jak data. Komórka zawierająca MAR1 lub SEPT2, odczytana jako 1 marca lub 2 września, zostaje po cichu przekonwertowana na wartość daty i przeformatowana, bez żadnego ostrzeżenia. To nie jest hipotetyczne: w 2020 roku Komitet Nomenklatury Genów HUGO przemianował około 27 ludzkich symboli genów, w tym SEPT2 i MARCH1, konkretnie dlatego, że oprogramowanie arkuszy kalkulacyjnych wciąż zamieniało je w daty za każdym razem, gdy otwierano zbiór danych, problem na tyle poważny, że badanie prac genomicznych z 2016 roku znalazło błędy autokorekty tego rodzaju w mniej więcej co piątej publikacji zawierającej dodatkowe pliki Excela. Lekcja wykracza poza nazwy genów: każdy krótki kod alfanumeryczny, który przypadkiem wygląda jak dzień i miesiąc, jest zagrożony w chwili, gdy ktoś otworzy twój CSV w arkuszu kalkulacyjnym, nie tylko twój.
Bardzo długie liczby tracą swoje ostatnie cyfry
Excel przechowuje każdą liczbę jako wartość zmiennoprzecinkową z 15 cyframi znaczącymi precyzji, limit, który Microsoft wprost dokumentuje. Szesnastocyfrowy numer karty lub konta, taki jak 4111111111111111, zostaje zaokrąglony, by się zmieścić, więc ostatnia cyfra lub dwie po cichu zamieniają się w zero, a wartość przestaje być tą, którą wpisałeś. Sformatowanie komórki później nie przywróci brakujących cyfr, ponieważ bazowo przechowywana liczba została już zaokrąglona; jedynym rozwiązaniem jest zaimportowanie kolumny jako tekst albo poprzedzenie wartości apostrofem, zanim Excel w ogóle potraktuje ją jako liczbę.
Tekst z akcentami zamienia się w bełkot
Kodowanie to osobny rodzaj awarii, niezwiązany z autokorektą. Plik CSV bez znacznika kolejności bajtów to zwykłe bajty; jeśli zapisano go jako UTF-8, Excel na Windows zwykle otworzy go, zakładając zamiast tego własną stronę kodową systemu, więc litery z akcentami, symbole walut i emoji wychodzą jako bełkot, ciąg taki jak café pojawia się jako coś bliższego café. Bezpieczną ścieżką przy tworzeniu CSV dla Excela jest zapisanie go jako CSV UTF-8 albo dodanie znacznika kolejności bajtów UTF-8; bezpieczną ścieżką przy odczytywaniu takiego pliku jest zaimportowanie go przez Dane > Z tekstu/CSV, co pozwala jawnie wybrać kodowanie źródłowe, zamiast ufać, że podwójne kliknięcie zgadnie poprawnie.
Przenoszenie danych między formatami bez otwierania ich w Excelu
Żadne z tych zachowań nie jest właściwie błędem Excela, to po prostu do tego służy zgadywacz liczb i dat w arkuszu kalkulacyjnym, i dotyczy to sposobu otwarcia pliku niezależnie od programu, nie tylko Excela. Najbezpieczniejszym sposobem przekształcenia pliku CSV albo sprawdzenia, że kolumna naprawdę zostaje tekstem, zanim trafi do jakiegokolwiek arkusza, jest praca bezpośrednio na nim: nasz konwerter danych parsuje CSV standardowym parserem świadomym cudzysłowów i nigdy nie reinterpretuje typu wartości, więc kod pocztowy czy nazwa genu zostają dokładnie tym tekstem, który podałeś. Działa w całości w przeglądarce, nic, co wklejasz, nigdzie nie jest wysyłane. To chroni jednak tylko etap konwersji: jeśli wynik nadal ma być potem otwarty w arkuszu kalkulacyjnym, oznacz tam wrażliwe kolumny jako tekst również.
Narzędzia w tym artykule
Najczęściej zadawane pytania
Jak powstrzymać Excela przed usuwaniem zer wiodących z CSV?
Nie otwieraj pliku podwójnym kliknięciem. Zaimportuj go zamiast tego przez Dane > Z tekstu/CSV (lub Pobierz dane), co pokazuje wybór typu danych kolumna po kolumnie; ustaw kolumnę kodu pocztowego, ID lub numeru telefonu na Tekst przed zakończeniem importu. Szybką poprawką dla pojedynczej komórki jest wpisanie apostrofu przed wartością, jak '02134, co wymusza tekst bez zmiany tego, co jest wyświetlane.
Dlaczego Excel zamienił mój identyfikator lub kod w datę?
Domyślny typ kolumny Ogólny w Excelu aktywnie szuka tekstu wyglądającego jak data, wzorców takich jak krótkie słowo plus liczba albo dwie liczby oddzielone myślnikiem lub ukośnikiem, i konwertuje wszystko, co pasuje, bez pytania o potwierdzenie. To dokładnie to zmusiło do przemianowania symboli genów, takich jak SEPT2 i MARCH1, w 2020 roku. Zaimportuj kolumnę jako Tekst, tak samo jak przy zerach wiodących, by powstrzymać domysł, zanim nastąpi.
Czy konwersja mojego CSV w inny sposób całkowicie omija te problemy?
Omija je na tym etapie konwersji, nie na zawsze. Parser, który traktuje każde pole jako zwykły tekst, jak nasz konwerter danych, nie przepisze kodu pocztowego ani nazwy genu podczas zamiany CSV na JSON. Ale jeśli wynik i tak zostanie potem otwarty w arkuszu kalkulacyjnym, przez ciebie albo przez kogokolwiek, kto go otrzyma, własny importer tego programu dostaje kolejną szansę na reinterpretację wartości, więc wrażliwe kolumny trzeba tam też oznaczyć jako tekst.