Czy można zmusić program Excel do automatycznego rozpoznawania plików CSV UTF-8?

Tworzę część aplikacji odpowiedzialną za eksport niektórych danych do plików CSV. Aplikacja zawsze używa UTF-8 ze względu na jego wielojęzyczny charakter na wszystkich poziomach. Ale otwarcie takich plików CSV (zawierających np. Znaki diakrytyczne, litery cyrylicy, litery greckie) w programie Excel nie daje oczekiwanych rezultatów pokazujących coś podobnego Г„/Г¤, Г–/Г¶. I nie wiem, jak zmusić Excela do zrozumienia, że ​​otwarty plik CSV jest zakodowany w UTF-8. Próbowałem też podać BOM UTF-8 EF BB BF, ale Excel to ignoruje.

Czy istnieje jakieś obejście?

PS Jakie narzędzia mogą potencjalnie zachowywać się tak jak Excel?

AKTUALIZACJA

Muszę powiedzieć, że pomyliłem społeczność z sformułowaniem pytania. Kiedy zadawałem to pytanie, poprosiłem o sposób otwarcia pliku CSV UTF-8 w programie Excel bez żadnych problemów dla użytkownika, w sposób płynny i przejrzysty. Jednak użyłem złego sformułowania, prosząc o zrobienie tego automatycznie . Jest to bardzo mylące i koliduje z automatyzacją makr VBA. Są dwie odpowiedzi na te pytania, które doceniam najbardziej: pierwsza odpowiedź Alexa https://stackoverflow.com/a/6002338/166589 i zaakceptowałem tę odpowiedź; a drugi autorstwa Marka https://stackoverflow.com/a/6488070/166589które pojawiły się nieco później. Z punktu widzenia użyteczności Excel wydawał się nie mieć dobrej, przyjaznej dla użytkownika obsługi CSV UTF-8, więc uważam, że obie odpowiedzi są poprawne, i najpierw zaakceptowałem odpowiedź Alexa, ponieważ naprawdę stwierdziłem, że Excel nie był w stanie tego zrobić to przejrzyście. Z tym myliłem się tutaj automatycznie . Odpowiedź Marka promuje bardziej skomplikowany sposób dla bardziej zaawansowanych użytkowników, aby osiągnąć oczekiwany wynik. Obie odpowiedzi są świetne, ale odpowiedź Alexa nieco lepiej pasuje do mojego nieokreślonego pytania.

AKTUALIZACJA 2

Pięć miesięcy później po ostatniej edycji zauważyłem, że odpowiedź Alexa z jakiegoś powodu zniknęła. Naprawdę mam nadzieję, że to nie był problem techniczny i mam nadzieję, że nie będzie już dyskusji, która odpowiedź jest teraz lepsza. Przyjmuję więc odpowiedź Marka jako najlepszą.

Alex ma rację, ale ponieważ musisz wyeksportować do pliku csv, możesz dać użytkownikom tę poradę podczas otwierania plików csv:

1. Zapisz wyeksportowany plik jako plik csv
2. Otwórz program Excel
3. Zaimportuj dane, używając Dane -> Importuj dane zewnętrzne -> Importuj dane
4. Wybierz typ pliku „csv” i przejdź do swojego pliku
5. W kreatorze importu zmień File_Origin na „65001 UTF” (lub wybierz poprawny identyfikator znaku języka)
6. Zmień ogranicznik na przecinek
7. Wybierz, gdzie chcesz zaimportować i Zakończ

W ten sposób znaki specjalne powinny wyświetlać się poprawnie.

Znacznik kolejności bajtów UTF-8 wskaże programowi Excel 2007+, że używasz UTF-8. (Zobacz ten post SO ).

W przypadku, gdy ktoś ma takie same problemy jak ja, klasa kodowania UTF8 .NET nie wyświetla znacznika kolejności bajtów w GetBytes()wywołaniu. Musisz użyć strumieni (lub obejścia ), aby uzyskać zestawienie komponentów.

Wydaje się, że błąd związany z ignorowaniem BOM został rozwiązany w programie Excel 2013. Miałem ten sam problem z literami cyrylicy, ale dodanie znaku BOM \uFEFFpomogło.

To niesamowite, że jest tak wiele odpowiedzi, ale żadna nie odpowiada na pytanie:

„Kiedy zadawałem to pytanie, poprosiłem o sposób otwarcia pliku CSV UTF-8 w programie Excel bez żadnych problemów dla użytkownika, ...”

Odpowiedź oznaczona jako zaakceptowana z ponad 200 głosami wyższymi jest dla mnie bezużyteczna, ponieważ nie chcę dać użytkownikom instrukcji, jak skonfigurować program Excel. Poza tym: ten podręcznik będzie dotyczył jednej wersji programu Excel, ale inne wersje programu Excel mają inne menu i okna dialogowe konfiguracji. Będziesz potrzebował instrukcji dla każdej wersji programu Excel.

Pytanie brzmi: jak sprawić, by Excel wyświetlał dane UTF8 za pomocą jednego podwójnego kliknięcia?

Przynajmniej w programie Excel 2007 nie jest to możliwe, jeśli używasz plików CSV, ponieważ BOM UTF8 jest ignorowany i zobaczysz tylko śmieci. To już część pytania Lyubomyr Shaydariv:

„Próbowałem też podać UTF-8 BOM EF BB BF, ale Excel to ignoruje.”

Robię to samo: zapisywanie rosyjskich lub greckich danych w pliku CSV UTF8 z BOM powoduje śmieci w Excelu:

Zawartość pliku CSV UTF8:

Colum1;Column2
Val1;Val2
Авиабилет;Tλληνικ

Wynik w programie Excel 2007:

Rozwiązaniem jest w ogóle nie używanie CSV. Ten format jest tak głupio zaimplementowany przez Microsoft, że zależy od ustawień regionu w panelu sterowania, jeśli jako separator zostanie użyty przecinek lub średnik . Tak więc ten sam plik CSV może otwierać się poprawnie na jednym komputerze, ale na innym komputerze nie. „CSV” oznacza „ Wartości oddzielane przecinkami ”, ale na przykład w niemieckim systemie Windows domyślnie jako separator należy użyć średnika, gdy przecinek nie działa. (Tutaj powinno się nazywać SSV = Wartości oddzielone średnikiem). Pliki CSV nie mogą być wymieniane między różnymi wersjami językowymi systemu Windows. Jest to dodatkowy problem w stosunku do problemu UTF-8.

Excel istnieje od dziesięcioleci. Szkoda, że ​​Microsoft nie był w stanie wdrożyć tak podstawowej rzeczy jak import CSV przez te wszystkie lata.

Jeśli jednak wstawisz te same wartości do pliku HTML i zapiszesz ten plik jako plik UTF8 z BOM z rozszerzeniem XLS , otrzymasz poprawny wynik.

Zawartość pliku XLS UTF8:

<table>
<tr><td>Colum1</td><td>Column2</td></tr>
<tr><td>Val1</td><td>Val2</td></tr>
<tr><td>Авиабилет</td><td>Tλληνικ</td></tr>
</table>

Wynik w programie Excel 2007:

Możesz nawet używać kolorów w HTML, które Excel wyświetli poprawnie.

<style>
.Head { background-color:gray; color:white; }
.Red  { color:red; }
</style>
<table border=1>
<tr><td class=Head>Colum1</td><td class=Head>Column2</td></tr>
<tr><td>Val1</td><td>Val2</td></tr>
<tr><td class=Red>Авиабилет</td><td class=Red>Tλληνικ</td></tr>
</table>

Wynik w programie Excel 2007:

W tym przypadku tylko sama tabela ma czarną ramkę i linie. Jeśli chcesz, aby WSZYSTKIE komórki wyświetlały linie siatki, jest to również możliwe w HTML:

<html xmlns:x="urn:schemas-microsoft-com:office:excel">
    <head>
        <meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>
        <xml>
            <x:ExcelWorkbook>
                <x:ExcelWorksheets>
                    <x:ExcelWorksheet>
                        <x:Name>MySuperSheet</x:Name>
                        <x:WorksheetOptions>
                            <x:DisplayGridlines/>
                        </x:WorksheetOptions>
                    </x:ExcelWorksheet>
                </x:ExcelWorksheets>
            </x:ExcelWorkbook>
        </xml>
    </head>
    <body>
        <table>
            <tr><td>Colum1</td><td>Column2</td></tr>
            <tr><td>Val1</td><td>Val2</td></tr>
            <tr><td>Авиабилет</td><td>Tλληνικ</td></tr>
        </table>
    </body>
</html>

Ten kod pozwala nawet określić nazwę arkusza roboczego (tutaj „MySuperSheet”)

Wynik w programie Excel 2007:

Użyliśmy tego obejścia:

1. Konwertuj CSV na UTF-16 LE
2. Wstaw BOM na początku pliku
3. Użyj tab jako separatora pól

Miał te same problemy z plikami CSV generowanymi przez PHP. Excel zignorował BOM, gdy Separator był zdefiniowany "sep=,\n"na początku treści (ale oczywiście po BOM).

Tak więc dodanie BOM ( "\xEF\xBB\xBF") na początku treści i ustawienie średnika jako separatora za pomocą tego fputcsv($fh, $data_array, ";");rozwiązuje.

W przeszłości miałem ten sam problem (jak tworzyć pliki, które Excel może odczytać, a inne narzędzia mogą również odczytać). Używałem TSV zamiast CSV, ale pojawił się ten sam problem z kodowaniem.

Nie udało mi się znaleźć sposobu, aby Excel automatycznie rozpoznał UTF-8 i nie chciałem / nie mogłem narzucać konsumentom skomplikowanych instrukcji otwierania plików. Więc zakodowałem je jako UTF-16le (z BOM) zamiast UTF-8. Dwa razy większy, ale Excel może rozpoznać kodowanie. I dobrze się kompresują, więc rozmiar rzadko (ale niestety nie zawsze) ma znaczenie.

Stare pytanie, ale do cholery, najprostszym rozwiązaniem jest:

1. Otwórz CSV w Notatniku
2. Zapisz jako -> wybierz właściwe kodowanie
3. Otwórz nowy plik

Jak pisałem na http://thinkinginsoftware.blogspot.com/2017/12/correctly-generate-csv-that-excel-can.html :

Poinformuj programistę odpowiedzialnego za wygenerowanie pliku CSV, aby go poprawił. Jako szybkie obejście można użyć gsed, aby wstawić zestawienie komponentów UTF-8 na początku łańcucha:

gsed -i '1s/^\(\xef\xbb\xbf\)\?/\xef\xbb\xbf/' file.csv

To polecenie wstawia LM UTF-4, jeśli nie jest obecny. Dlatego jest to idempotentne polecenie. Teraz powinieneś być w stanie kliknąć dwukrotnie plik i otworzyć go w programie Excel.

Możesz przekonwertować plik .csv do UTF-8 z BOM za pomocą Notepad ++:

1. Otwórz plik w Notepad ++ .
2. Przejdź do menu Encoding→ Convert to UTF-8.
3. Przejdź do menu File→ Save.
4. Zamknij Notepad ++.
5. Otwórz plik w programie Excel.

Pracował w Microsoft Excel 2013 (15.0.5093.1000) MSO (15.0.5101.1000) 64-bit z Microsoft Office Professional Plus 2013 na Windows 8.1 z ustawieniami regionalnymi dla programów nieobsługujących kodu Unicode ustawionymi na „niemiecki (Niemcy)”.

Proste makro vba do otwierania plików tekstowych i csv w formacie utf-8

Sub OpenTextFile()

   filetoopen = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
   If filetoopen = Null Or filetoopen = Empty Then Exit Sub

   Workbooks.OpenText Filename:=filetoopen, _
   Origin:=65001, DataType:=xlDelimited, Comma:=True

End Sub

Pochodzenie: = 65001 to UTF-8. Przecinek: Prawda dla plików .csv dystrybuowanych w kolumnach

Zapisz go w Personal.xlsb, aby zawsze był dostępny. Personalizuj pasek narzędzi programu Excel, dodając przycisk wywołania makra i stamtąd otwieraj pliki. Możesz dodać więcej formatowania do makra, takie jak automatyczne dopasowanie kolumny, wyrównanie itp.

Tylko dla pomocy użytkownikom zainteresowanym otwarciem pliku w programie Excel, którzy osiągnęli ten wątek, jak ja.

Użyłem poniższego kreatora i działał dla mnie dobrze, importując plik UTF-8. Nieprzezroczyste, ale przydatne, jeśli masz już plik.

1. Otwórz Microsoft Excel 2007.
2. Kliknij opcję paska menu Dane.
3. Kliknij ikonę Z tekstu.
4. Przejdź do lokalizacji pliku, który chcesz zaimportować. Kliknij nazwę pliku, a następnie przycisk Importuj. Kreator importu tekstu - okno Krok 1 lub 3 pojawi się teraz na ekranie.
5. Wybierz typ pliku, który najlepiej opisuje Twoje dane - Ograniczona lub Stała szerokość.
6. Wybierz 65001: Unicode (UTF-8) z rozwijanej listy, która pojawia się obok Początek pliku.
7. Kliknij przycisk Dalej, aby wyświetlić okno Kreatora importu tekstu - krok 2 lub 3.
8. Umieść znacznik wyboru obok ogranicznika, który został użyty w pliku, który chcesz zaimportować do programu Microsoft Excel 2007. Okno podglądu danych pokaże, jak będą wyglądać Twoje dane na podstawie wybranego ogranicznika.
9. Kliknij przycisk Dalej, aby wyświetlić Kreatora importu tekstu - Krok 3 z 3.
10. Wybierz odpowiedni format danych dla każdej kolumny danych, którą chcesz zaimportować. Możesz również nie importować jednej lub więcej kolumn danych, jeśli chcesz.
11. Kliknij przycisk Zakończ, aby zakończyć importowanie danych do programu Microsoft Excel 2007.

Źródło: https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0

Tak, jest to możliwe. Jak wcześniej zauważyło wielu użytkowników, wydaje się, że występuje problem z czytaniem poprawnego Znaku Kolejności Bajtów, gdy plik jest zakodowany w UTF-8. W przypadku UTF-16 nie ma problemu, więc jest endemiczny dla UTF-8. Rozwiązaniem, którego używam do tego jest dodanie BOM, DWUKROTNIE. W tym celu dwukrotnie wykonuję następujące polecenie sed:

sed -I '1s/^/\xef\xbb\xbf/' *.csv

, w którym symbol zastępczy można zastąpić dowolną nazwą pliku. Prowadzi to jednak do mutacji sep = na początku pliku .csv. Plik .csv otworzy się wtedy normalnie w programie Excel, ale z dodatkowym wierszem z „sep =” w pierwszej komórce. „Sep =” można również usunąć w samym źródłowym pliku .csv, ale podczas otwierania pliku za pomocą VBA należy określić ogranicznik:

Workbooks.Open(name, Format:=6, Delimiter:=";", Local:=True)

Format 6 to format .csv. Ustaw Local na true, na wypadek gdyby w pliku były daty. Jeśli wartość Local nie jest ustawiona na true, daty zostaną zamerykanizowane, co w niektórych przypadkach spowoduje uszkodzenie formatu .csv.

Oto moje działające rozwiązanie:

vbFILEOPEN = "your_utf8_file.csv"
Workbooks.OpenText Filename:=vbFILEOPEN, DataType:=xlDelimited, Semicolon:=True, Local:=True, Origin:=65001

Kluczem jest pochodzenie: = 65001

Tak to mozliwe. Podczas pisania strumienia tworzącego plik csv, pierwszą rzeczą do zrobienia jest:

myStream.Write(Encoding.UTF8.GetPreamble(), 0, Encoding.UTF8.GetPreamble().Length)

Naprawdę niesamowita lista odpowiedzi, ale ponieważ brakuje jeszcze jednego całkiem dobrego, wspomnę o tym tutaj: otwórz plik csv z arkuszami google i zapisz go z powrotem na komputerze lokalnym jako plik programu Excel.

W przeciwieństwie do Microsoft, Google zdążył obsługiwać pliki csv UTF-8, więc po prostu działa tam otworzyć plik. Działa także eksport do formatu Excel. Nawet jeśli nie jest to preferowane rozwiązanie dla wszystkich, jest dość bezpieczne w razie awarii, a liczba kliknięć nie jest tak wysoka, jak może się wydawać, zwłaszcza gdy i tak jesteś już zalogowany w Google.

Nie odpowiada to dokładnie na pytanie, ale ponieważ natknąłem się na to, a powyższe rozwiązania nie działały dla mnie lub miałem wymagania, których nie mogłem spełnić, oto inny sposób na dodanie BOM, gdy masz dostęp do vima:

vim -e -s +"set bomb|set encoding=utf-8|wq" filename.csv

cześć używam ruby ​​na szynach do generowania csv. W naszej aplikacji planujemy wybrać wersję wielojęzyczną (I18n) i napotkaliśmy problem podczas przeglądania zawartości I18n w pliku CSV programu Excel.

W porządku z Linuksem (Ubuntu) i Mac.

Zidentyfikowaliśmy, że Windows Excel musi ponownie zaimportować dane, aby wyświetlić rzeczywiste dane. Podczas importu otrzymamy więcej opcji wyboru zestawu znaków.

Ale nie można tego edukować dla każdego użytkownika, dlatego szukamy rozwiązania, które można otworzyć tylko podwójnym kliknięciem.

Następnie zidentyfikowaliśmy sposób wyświetlania danych w trybie otwartym i BOM w Windowsie Excel za pomocą aghuddleston gist . Dodano w referencji.

Przykład treści I18n

W systemie Mac i Linux

Szwedzki: Förnamn angielski: imię

W systemie Windows

Szwedzki: Förnamn Angielski: Imię

def user_information_report(report_file_path, user_id)
    user = User.find(user_id)
    I18n.locale = user.current_lang
    open_mode = "w+:UTF-16LE:UTF-8"
    bom = "\xEF\xBB\xBF"
    body user, open_mode, bom
  end

def headers
    headers = [
        "ID", "SDN ID",
        I18n.t('sys_first_name'), I18n.t('sys_last_name'), I18n.t('sys_dob'),
        I18n.t('sys_gender'), I18n.t('sys_email'), I18n.t('sys_address'),
        I18n.t('sys_city'), I18n.t('sys_state'), I18n.t('sys_zip'),
        I18n.t('sys_phone_number')
    ]
  end

def body tenant, open_mode, bom
    File.open(report_file_path, open_mode) do |f|
      csv_file = CSV.generate(col_sep: "\t") do |csv|
        csv << headers
        tenant.patients.find_each(batch_size: 10) do |patient|
          csv <<  [
              patient.id, patient.patientid,
              patient.first_name, patient.last_name, "#{patient.dob}",
              "#{translate_gender(patient.gender)}", patient.email, "#{patient.address_1.to_s} #{patient.address_2.to_s}",
              "#{patient.city}", "#{patient.state}",  "#{patient.zip}",
              "#{patient.phone_number}"
          ]
        end
      end
      f.write bom
      f.write(csv_file)
    end
  end

Ważną rzeczą do odnotowania tutaj jest tryb otwarty i bom

open_mode = "w +: UTF-16LE: UTF-8"

bom = "\ xEF \ xBB \ xBF"

Przed napisaniem CSV wstaw BOM

f. napisz bom

f.write (plik csv)

Windows i Mac

Plik można otworzyć bezpośrednio, klikając dwukrotnie.

Linux (ubuntu)

Podczas otwierania pliku zapytaj o opcje separatora -> wybierz „TAB”

Kilka dni temu napotkałem ten sam problem i nie mogłem znaleźć żadnego rozwiązania, ponieważ nie mogę użyć tej import from csvfunkcji, ponieważ sprawia, że ​​wszystko ma być stylizowane na ciąg znaków.

Moim rozwiązaniem było najpierw otwarcie pliku za pomocą notpad ++ i change the encode to ASCII. Następnie właśnie otworzyłem plik w programie Excel i działał zgodnie z oczekiwaniami.

W php po prostu dodajesz $ bom do $ csv_string:

$bom = sprintf( "%c%c%c", 239, 187, 191); // EF BB BF
file_put_contents( $file_name, $bom . $csv_string );

Testowane z MS Excel 2016, php 7.2.4

To stare pytanie, ale właśnie napotkałem podobny problem i rozwiązanie może pomóc innym:

Miał ten sam problem, gdy zapisywanie danych tekstowych CSV do pliku, a następnie otwarcie wynikowego pliku .csv w programie Excel przesuwa cały tekst do jednej kolumny. Po przeczytaniu powyższych odpowiedzi wypróbowałem następujące, co wydaje się rozwiązać problem.

Zastosuj kodowanie UTF-8 podczas tworzenia StreamWriter. Otóż ​​to.

Przykład:

using (StreamWriter output = new StreamWriter(outputFileName, false, Encoding.UTF8, 2 << 22)) {
   /* ... do stuff .... */
   output.Close();
}

Jeśli chcesz uczynić go w pełni automatycznym, jednym kliknięciem lub automatycznie ładować się do Excela ze strony internetowej, ale nie możesz wygenerować odpowiednich plików Excela, sugerowałbym alternatywne użycie formatu SYLK. OK, nie jest tak prosty jak CSV, ale jest oparty na tekście i bardzo łatwy do wdrożenia i obsługuje UTF-8 bez żadnych problemów.

Napisałem klasę PHP, która odbiera dane i wysyła plik SYLK, który otworzy się bezpośrednio w programie Excel po prostu klikając plik (lub uruchomi program Excel automatycznie, jeśli zapiszesz plik na stronie internetowej z odpowiednim typem MIME. Możesz nawet dodaj formatowanie (np. pogrubienie, formatuj liczby w określony sposób itp.) i zmień rozmiary kolumn lub kolumny z automatycznym rozmiarem tekstu w kolumnach, a wszystko w całym kodzie prawdopodobnie nie będzie więcej niż około 100 linii.

Bardzo trudno jest odtworzyć SYLK, tworząc prosty arkusz kalkulacyjny i zapisując go jako SYLK, a następnie czytając go za pomocą edytora tekstu. Pierwszy blok to nagłówki i standardowe formaty liczb, które rozpoznasz (które po prostu regurgitujesz w każdym tworzonym pliku), a następnie dane są po prostu współrzędną X / Y i wartością.

1. Pobierz i zainstaluj LibreOffice Calc
2. Otwórz wybrany plik csv w LibreOffice Calc
3. Dzięki niebiosom, że pojawia się kreator importu tekstu ...
4. ... wybierz opcje ogranicznika i kodowania znaków
5. Wybierz dane wynikowe w Calc i skopiuj wklej do Excela

Generuję pliki csv z prostej aplikacji w języku C # i miałem ten sam problem. Moim rozwiązaniem było upewnienie się, że plik jest zapisany z kodowaniem UTF8, na przykład:

// Use UTF8 encoding so that Excel is ok with accents and such.
using (StreamWriter writer = new StreamWriter(path, false, Encoding.UTF8))
{
    SaveCSV(writer);
}

Pierwotnie miałem następujący kod, z którym akcenty wyglądają dobrze w Notepad ++, ale były zniekształcone w Excelu:

using (StreamWriter writer = new StreamWriter(path))
{
    SaveCSV(writer);
}

Twój przebieg może się różnić - używam .NET 4 i Excel z Office 365.

Działające rozwiązanie dla Office 365

• zapisz w UTF-16(bez LE, BE)
• użyj separatora \t

Kod w PHP

$header = ['číslo', 'vytvořeno', 'ěščřžýáíé'];
$fileName = 'excel365.csv';
$fp = fopen($fileName, 'w');
fputcsv($fp, $header, "\t");
fclose($fp);

$handle = fopen($fileName, "r");
$contents = fread($handle, filesize($fileName));
$contents = iconv('UTF-8', 'UTF-16', $contents);
fclose($handle);

$handle = fopen($fileName, "w");
fwrite($handle, $contents);
fclose($handle);

Najpierw zapisz arkusz kalkulacyjny Excel jako tekst Unicode. Otwórz plik TXT za pomocą Internet Explorera i kliknij „Zapisz jako” Kodowanie TXT - wybierz odpowiednie kodowanie, np. Dla Win Cyrillic 1251

Próbowałem wszystkiego, co mogłem znaleźć w tym wątku i tym podobnych, nic nie działało w pełni. Jednak importowanie do arkuszy Google i po prostu pobieranie jako csv działało jak urok. Wypróbuj, jeśli dojdziesz do mojej frustracji.