Excel na CSV bez zepsutych znaków: UTF-8, BOM i separatory

Większość zepsutych znaków w pliku CSV wynika z niezgodności kodowania: plik zapisano jako UTF-8, a otwarto jako kodowanie Windows. Wyeksportuj arkusz jako CSV UTF-8, z znacznikiem kolejności bajtów (BOM), jeśli plik ma otworzyć Excel, i wybierz separator, którego oczekuje Twój język regionalny. Przed eksportem sformatuj kolumny, takie jak kody pocztowe i numery telefonów, jako tekst, aby zera się zachowały.

Excel na CSVPotrzebuję CSV, mam plik ExcelaZrób to za darmo

Dlaczego "é" zamienia się w "é"

Znak w pliku jest zapisany jako bajty. W UTF-8 litera é to dwa bajty: C3 i A9. Program, który odczytuje te bajty jako Windows-1252, popularne kodowanie w systemie Windows, widzi znak à dla C3 i © dla A9. Dlatego "café" wyświetla się jako "café". Możesz to sprawdzić samodzielnie: zakodowanie é jako UTF-8 i odczytanie wyniku jako Windows-1252 daje é. Tak samo psuje się ü, ñ czy ö.

Naprawa nie polega na edycji znaków. Trzeba powiedzieć programowi, jakiego kodowania plik używa.

UTF-8 i BOM

UTF-8 to standardowe kodowanie tekstu w sieci i w większości nowoczesnego oprogramowania. Zwykły plik CSV nie ma pola, które mówi, jakiego kodowania użyto, więc programy zgadują. Znacznik kolejności bajtów to trzy bajty na samym początku pliku, EF BB BF, które sygnalizują UTF-8 programom, które go szukają. Excel w systemie Windows często otwiera plik UTF-8 bez BOM za pomocą lokalnego kodowania, więc BOM jest częstą poprawką.

W nowszych wersjach Excela menu Zapisz jako oferuje opcję CSV UTF-8, która dodaje BOM. Możesz też zaimportować plik przez Dane, Z tekstu/CSV i ustawić Pochodzenie pliku na 65001, UTF-8. BOM ma jedną wadę: program, który go nie oczekuje, może potraktować go jako część nazwy pierwszej kolumny. Większość parserów i programów arkuszowych sobie z tym radzi, ale jeśli importer danych zgłasza dziwny nagłówek, usuń BOM.

Przecinek czy średnik

CSV oznacza wartości rozdzielone przecinkami, a specyfikacja RFC 4180 opisuje pola oddzielone przecinkami. W wielu krajach, w tym tych, które używają przecinka dziesiętnego, jak w 1,5 dla półtora, separatorem listy w Excelu jest średnik. Wtedy plik rozdzielony przecinkami otwarty w Excelu może rozpaść się w nieprawidłowy sposób, a liczba taka jak 1,5 zamieni się w dwa pola.

Wybierz separator, którego oczekuje docelowy program. Narzędzie Excel na CSV oferuje przecinek, średnik i tabulator. Jeśli plik ma trafić do programu w języku z przecinkiem dziesiętnym, średnik jest zwykle bezpieczniejszym wyborem.

Daty zamieniają się w numery seryjne

Excel przechowuje daty jako numery seryjne, liczące dni od punktu startowego. W systemie dat 1900 pierwszy stycznia 1900 roku to numer seryjny 1. Excel traktuje też rok 1900 jako przestępny, więc liczy 29 lutego 1900 roku, którego nigdy nie było, jako numer seryjny 60. Przez to 1 marca 1900 roku ma numer 61, a każda późniejsza data przesuwa się o jeden dzień. Błąd wziął się z Lotus 1-2-3, a Microsoft zachował ten system ze względu na zgodność. Inne oprogramowanie może odczytać daty sprzed 1 marca 1900 roku z przesunięciem o jeden dzień.

Po eksporcie daty możesz zobaczyć jako 44927 zamiast 2023-01-01 albo z jednodniowym przesunięciem w starych datach. Narzędzie zamienia daty na czytelny tekst. Jeśli eksportujesz ręcznie, sformatuj kolumnę dat jako tekst w formacie ISO yyyy-mm-dd, który każdy program odczytuje tak samo.

Zera wiodące znikają

Kod pocztowy, taki jak 02134, to tekst, ale Excel traktuje wpisaną liczbę jako liczbę, więc wyświetli 2134. Zero znika z arkusza, a CSV zapisany z tego arkusza też go nie będzie miał. Tak samo dzieje się z numerami telefonów zaczynającymi się od 0 i długimi numerami identyfikacyjnymi. Bardzo długie liczby mogą też tracić cyfry, bo Excel zachowuje tylko około 15 cyfr znaczących.

Naprawą jest zapisanie tych kolumn jako tekst, zanim wpiszesz lub zaimportujesz dane. Gdy otwierasz CSV, który już ma zera, użyj Dane, Z tekstu/CSV i ustaw kolumnę na Tekst, zamiast klikać dwukrotnie plik.

Zasady stosowania cudzysłowów

RFC 4180 definiuje popularny format CSV. Jego główne zasady cudzysłowów to:

• Pole zawierające przecinek, cudzysłów lub znak końca wiersza musi być ujęte w cudzysłowy.
• Cudzysłów wewnątrz ujętego pola zapisuje się jako dwa cudzysłowy.
• W specyfikacji wiersze kończą się znakami CRLF, chociaż większość narzędzi akceptuje LF.

Na przykład wiersz "Müller, Anna","She said ""ok""" ma dwa pola: Müller, Anna i She said "ok". Typ MIME zarejestrowany w IANA dla CSV to text/csv.

RFC 4180 nie dopuszcza cudzysłowu wewnątrz pola niecytowanego, a wiele parserów odrzuca taki wiersz albo dzieli go błędnie. Poprawnie ujmij pole w cudzysłowy albo usuń przypadkowy znak.

Lista kontrolna przed eksportem

1. Wyeksportuj jako CSV UTF-8, z BOM, jeśli plik ma otworzyć Excel.
2. Wybierz separator, którego oczekuje docelowy program.
3. Sformatuj kolumny z identyfikatorami, takie jak kody pocztowe i numery telefonów, jako tekst.
4. Zapisz daty w formacie yyyy-mm-dd.
5. Otwórz plik w zwykłym edytorze tekstu i sprawdź pierwszy wiersz oraz próbkę imion z akcentami.

Ukryte znaki, na przykład spacje niełamliwe, też mogą powodować błędy importu. Narzędzie usuwania ukrytych znaków je znajduje i usuwa, a ukryte znaki w tekście wyjaśniają, skąd się biorą. Gdy plik jest czysty, CSV na JSON może go przekonwertować dla programisty, jak opisano w CSV na JSON i z powrotem.

Przed eksportem warto sprawdzić same komórki: sprawdzanie formuł Excel wylicza formuły, które pokazują błąd lub odwołują się do innego pliku, a to właśnie komórki, które trafią do CSV puste. Po eksporcie porównaj dwa arkusze, aby zobaczyć, co zmieniło się między wersjami.

Szybkie odpowiedzi

Dlaczego mój plik wygląda dobrze w jednym programie, a w Excelu jest zepsuty? Gdy plik UTF-8 nie ma BOM, Excel w systemie Windows może odczytać go lokalnym kodowaniem. Dodanie BOM albo import z wybranym UTF-8 to naprawia.

Czy wersje Excela na Maca potrzebują BOM? Niektóre źródła twierdzą, że tak, więc przed wysłaniem pliku sprawdź go na przykładzie imienia z akcentem.

Czy mogę poprawić daty po eksporcie? Tak, jeśli nadal są numerami seryjnymi, ale lepiej wyeksportować je od razu jako yyyy-mm-dd.

Czy mój arkusz jest wysyłany na serwer podczas konwersji? Narzędzie działa w przeglądarce, więc plik jest przetwarzany na Twoim urządzeniu. Możesz to sprawdzić w przewodniku po sprawdzaniu wysyłania plików.