Pracuję nad funkcją eksportu wyników wyszukiwania do pliku CSV w celu otwarcia w programie Excel. Jedno z pól to pole tekstowe, które może zawierać podziały wierszy, przecinki, cudzysłowy itp. Aby temu przeciwdziałać, zawarłem je w podwójne cudzysłowy (").

Jednak po zaimportowaniu danych do programu Excel 2007, ustawieniu odpowiedniego separatora i ustawieniu kwalifikatora tekstu na podwójny cudzysłów, podziały wierszy nadal tworzą nowe rekordy na podziałach wierszy, w których spodziewałbym się zobaczyć całe pole tekstowe w pojedyncza komórka.

Próbowałem również zastąpić CR / LF (\ r \ n) tylko CR (\ r) i ponownie tylko LF (\ n), ale bez powodzenia.

Czy ktoś inny napotkał to zachowanie, a jeśli tak, to jak je naprawiłeś?

TIA,
-J

EDYCJA:
Oto szybki plik, który napisałem ręcznie, aby zduplikować problem.

ID, nazwa, opis
„12345”, „Smith, Joe”, „Hej.
Nazywam się Joe”.

Kiedy importuję to do Excela 2007, otrzymuję wiersz nagłówka i dwa rekordy. Zwróć uwagę, że przecinek w „Smith, Joe” jest poprawnie obsługiwany. Tylko przerwy w liniach powodują problemy.

Excel (przynajmniej w Office 2007 na XP) może zachowywać się różnie w zależności od tego, czy plik CSV jest importowany, otwierając go z menu Plik-> Otwórz, czy też klikając dwukrotnie plik w Eksploratorze.

Mam plik CSV zakodowany w UTF-8 i zawierający znaki nowej linii w niektórych komórkach. Jeśli otworzę ten plik z menu Plik-> Otwórz programu Excel, pojawi się kreator „importu CSV” i plik nie może zostać poprawnie zaimportowany: nowe linie rozpoczynają nowy wiersz, nawet gdy są cytowane. Jeśli otworzę ten plik, klikając go dwukrotnie w oknie Eksploratora, to otworzy się on poprawnie bez interwencji kreatora.

Żadne z proponowanych rozwiązań nie zadziałało.

Co faktycznie działa (z dowolnym kodowaniem):

Skopiuj / wklej dane z pliku csv (otwórz w Edytorze), a następnie wykonaj "tekst w kolumnach" -> nie działa, w porządku.

Przejdź do następnej zakładki i ponownie skopiuj / wklej (to samo, co masz już w schowku) -> automagicznie działa teraz.

Jeśli robisz to ręcznie, pobierz LibreOffice i użyj LibreOffice Calc, aby zaimportować plik CSV. Robi to o wiele lepiej niż jakakolwiek wersja Excela, którą wypróbowałem, i może zapisywać do XLS lub XLSX, jeśli potrzebujesz później przenieść się do Excela.

Ale jeśli utknąłeś w Excelu i potrzebujesz lepszej poprawki, wydaje się, że jest na to sposób. Wydaje się, że jest zależne od lokalizacji (co wydaje się idiotyczne, moim skromnym zdaniem). Nie mam programu Excel 2007, ale mam Excel 2010 i podany przykład:

ID,Name,Description
"12345","Smith, Joe","Hey.
My name is Joe."

nie działa. Napisałem go w Notatniku i wybrałem Zapisz jako ..., a obok przycisku Zapisz możesz wybrać kodowanie. Wybrałem UTF-8 zgodnie z sugestią, ale bez powodzenia. Jednak zmiana przecinków na średniki działała dla mnie. Nic więcej nie zmieniłem i po prostu zadziałało. Więc zmieniłem przykład, aby wyglądał tak, i wybrałem kodowanie UTF-8 podczas zapisywania w Notatniku:

ID;Name;Description
"12345";"Smith, Joe";"Hey.
My name is Joe."

Ale jest haczyk! Jedynym sposobem, w jaki to działa, jest dwukrotne kliknięcie pliku CSV, aby otworzyć go w programie Excel. Jeśli spróbuję zaimportować dane z tekstu i wybiorę ten plik CSV, nadal kończy się to niepowodzeniem w przypadku cytowanych nowych linii.

Ale jest inny haczyk! Separator pola roboczego (przecinek w oryginalnym przykładzie, w moim przypadku średnik) wydaje się zależeć od ustawień regionalnych systemu (ustawionych w Panelu sterowania -> Region i język). W Norwegii przecinek jest separatorem dziesiętnym. Wydaje się, że Excel unika tego znaku i zamiast tego preferuje średnik. Mam dostęp do innego komputera ustawionego na język angielski w Wielkiej Brytanii i na tym komputerze pierwszy przykład z separatorem przecinkiem działa dobrze (tylko po dwukrotnym kliknięciu), a ten ze średnikiem w rzeczywistości zawodzi! To tyle, jeśli chodzi o interoperacyjność. Jeśli chcesz opublikować ten plik CSV online, a użytkownicy mogą mieć Excela, myślę, że musisz opublikować obie wersje i zasugerować, aby ludzie sprawdzili, który plik daje poprawną liczbę wierszy.

Wszystkie szczegóły, które udało mi się zebrać, aby to zadziałało, to:

1. Plik musi być zapisany jako UTF-8 z BOM, co robi Notatnik po wybraniu UTF-8. Próbowałem UTF-8 bez BOM (można go łatwo przełączyć w Notepad ++), ale podwójne kliknięcie dokumentu kończy się niepowodzeniem.
2. Musisz użyć przecinka lub średnika, ale nie tego, który jest separatorem dziesiętnym w Ustawieniach regionalnych. Być może inne postacie działają, ale nie wiem które.
3. Musisz cytować pola zawierające nowy wiersz ze znakiem „.
4. Użyłem końcówek linii Windows (\ r \ n) zarówno w polu tekstowym, jak i jako separator rekordów, to działa.
5. Musisz dwukrotnie kliknąć plik, aby go otworzyć, importowanie danych z tekstu nie działa.

Mam nadzieję, że to komuś pomoże.

W końcu znalazłem problem!

Okazuje się, że piszemy plik przy użyciu kodowania Unicode, a nie ASCII czy UTF-8. Wydaje się, że zmiana kodowania w FileStream rozwiązuje problem.

Dziękuję wszystkim za wszystkie sugestie!

Krótka odpowiedź

Usuń znaki nowej linii / nowej linii ( \nz Notepad ++). Program Excel nadal będzie rozpoznawać znak powrotu karetki ( \r) w celu oddzielenia rekordów.

Długa odpowiedź

Jak wspomniano, znaki nowego wiersza są obsługiwane w polach CSV, ale program Excel nie zawsze obsługuje je z wdziękiem. Napotkałem podobny problem z plikiem CSV innej firmy, który prawdopodobnie miał problemy z kodowaniem, ale nie poprawił się dzięki zmianom w kodowaniu.

U mnie zadziałało usunięcie wszystkich znaków nowej linii ( \n). Powoduje to zwijanie pól do pojedynczego rekordu, przy założeniu, że rekordy są oddzielone kombinacją znaku powrotu karetki i nowej linii (CR / LF). Program Excel prawidłowo zaimportuje plik i rozpozna nowe rekordy po znaku powrotu karetki.

Oczywiście czystszym rozwiązaniem jest najpierw zastąpienie prawdziwych znaków nowej linii ( \r\n) tymczasową kombinacją znaków, zastępując znaki nowej linii ( \n) wybranym przez siebie znakiem oddzielającym (np. Przecinkiem w pliku średnika), a następnie ponownie zastępując znaki tymczasowe właściwymi znakami nowej linii.

+1 do komentarza J. Ashleya. Natknąłem się również na ten problem. Okazuje się, że Excel wymaga:

• Znak nowego wiersza („\ n”) w ciągu cytowanym

• Powrót karetki i nowa linia między każdym wierszem.

Np. „Test”, „Element wielowierszowy \ n Element wielowierszowy” \ r ​​\ n „Test2”, „Element wielowierszowy \ n Element wielowierszowy” \ r ​​\ n

Użyłem Notatnika ++ do prawidłowego rozgraniczenia każdego wiersza i używania tylko znaków nowej linii w ciągu. Odkryłem to, tworząc wielowierszowe wpisy w pustym dokumencie programu Excel i otwierając plik csv w notatniku ++.

Jeśli pole zawiera początkową spację, program Excel ignoruje podwójny cudzysłów jako kwalifikator tekstu. Rozwiązaniem jest wyeliminowanie początkowych spacji między przecinkiem (separatorem pól) a podwójnym cudzysłowem. Na przykład:

Uszkodzone:
imię i nazwisko, stanowisko, opis
„Jan”, „Pan”, „Mój szczegółowy opis”

Praca:
imię i nazwisko, stanowisko, opis
„Jan”, „Pan”, „Mój szczegółowy opis”

Użyj Arkuszy Google i zaimportuj plik CSV.

Następnie możesz wyeksportować to do użycia w programie Excel

Jeśli ktoś natknie się na ten wątek i szuka tutaj ostatecznej odpowiedzi (Podziękowania dla osoby wspominającej o LibreOffice:

1) Zainstaluj LibreOffice 2) Otwórz plik Calc i importuj 3) Mój plik txt miał pola oddzielone, a pola znakowe zawarte w "4) Zapisz jako plik ODS 5) Otwórz plik ODS w Excelu 6) Zapisz jako .xls (x) 7) Gotowe.8) To zadziałało idealnie dla mnie i zaoszczędziło mi DUŻO!

Wklej do Notepad ++, wybierz Kodowanie> Koduj w ANSI, skopiuj wszystko ponownie i wklej do Excela :)

Miałem podobny problem. Miałem trochę danych z Twittera w MySQL. Dane miały Line Feed (LF lub \ n) z danymi. Miałem wymóg eksportu danych MySQL do programu Excel. LF niszczył mój import pliku csv. Więc zrobiłem następujące -

1. From MySQL exported to CSV with Record separator as CRLF
2. Opened the data in notepad++ 
3. Replaced CRLF (\r\n) with some string I am not expecting in the Data. I used ###~###! as replacement of CRLF
4. Replaced LF (\n) with Space
5. Replaced ###~###! with \r\n, so my record separator are back.
6. Saved and then imported into Excel

UWAGA - Podczas zastępowania CRLF lub LF nie zapomnij o zaznaczeniu pola wyboru Check Excended (\ n, \ r, \ t ... Pole wyboru [spójrz na dolną część okna dialogowego po lewej stronie)

Moje doświadczenia z programem Excel 2010 na WinXP z francuskimi ustawieniami regionalnymi

• separator zaimportowanego pliku csv musi odpowiadać separatorowi listy ustawień regionalnych (; w moim przypadku)
• musisz dwukrotnie kliknąć plik w eksploratorze. nie otwieraj go z programu Excel

Excel jest niesamowicie zepsuty, gdy ma do czynienia z plikami CSV. LibreOffice wykonuje znacznie lepszą pracę. Tak więc dowiedziałem się, że:

• Plik musi być zakodowany w UTF-8 z BOM, więc rozważ to dla wszystkich poniższych punktów
• Zdecydowanie najlepszy wynik osiąga się, otwierając go z Eksploratora plików
• Jeśli otworzysz go z poziomu programu Excel, możliwe są dwa wyniki:
  • Jeśli ma tylko znaki ASCII, najprawdopodobniej zadziała
  • Jeśli zawiera znaki inne niż ASCII, zepsuje podziały wierszy
• Wydaje się, że jest mocno zależny od separatora dziesiętnego skonfigurowanego w ustawieniach regionalnych systemu operacyjnego, więc musisz wybrać właściwy
• Założę się, że może też zachowywać się inaczej w zależności od wersji systemu operacyjnego i Office

Przegląd

Prawie 10 lat po oryginalnym poście program Excel nie poprawił się pod względem importowania plików CSV. Jednak stwierdziłem, że importowanie tabel HTML jest znacznie lepsze. Można więc użyć Pythona do konwersji CSV do HTML, a następnie zaimportować wynikowy HTML do Excela.

Zalety tego podejścia to: (a) działa niezawodnie, (b) nie musisz przesyłać swoich danych do usługi innej firmy (np. Arkusze Google), (c) nie są wymagane żadne dodatkowe „grube” instalacje (LibreOffice, Liczby itp.) Dla większości użytkowników, (d) wyższy poziom niż wtrącanie się w znaki CR / LF i markery BOM, (e) nie ma potrzeby majstrowania przy ustawieniach regionalnych.

Kroki

Poniższe kroki można wykonać w dowolnej powłoce podobnej do basha, o ile jest zainstalowany Python 3. Chociaż Python może być używany do bezpośredniego odczytu CSV, csvkit jest używany do wykonania pośredniej konwersji do JSON. Pozwala nam to uniknąć konieczności zajmowania się zawiłościami CSV w naszym kodzie Pythona.

Najpierw zapisz następujący skrypt jako json2html.py. Skrypt odczytuje plik JSON ze standardowego wejścia i zrzuca go jako tabelę HTML:

#!/usr/bin/env python3
import sys, json, html

if __name__ == '__main__':
    header_emitted = False
    make_th = lambda s: "<th>%s</th>" % (html.escape(s if s else ""))
    make_td = lambda s: "<td>%s</td>" % (html.escape(s if s else ""))
    make_tr = lambda l, make_cell: "<tr>%s</tr>" % ( "".join([make_cell(v) for v in l]) )
    print("<html><body>\n<table>")
    for line in json.load(sys.stdin):
        lk, lv = zip(*line.items())
        if not header_emitted:
            print(make_tr(lk, make_th))
            header_emitted = True
        print(make_tr(lv, make_td))
    print("</table\n</body></html>")

Następnie zainstaluj csvkit w środowisku wirtualnym i użyj go csvjsondo przesłania pliku wejściowego do naszego skryptu. Dobrym pomysłem jest wyłączenie zgadywania typu komórki za pomocą -Iargumentu:

$ virtualenv -p python3 pyenv
$ . ./pyenv/bin/activate
$ pip install csvkit
$ csvjson -I input.csv | python3 json2html.py > output.html

Teraz output.htmlmożna zaimportować do programu Excel. Podziały linii w komórkach zostaną zachowane.

Opcjonalnie możesz wyczyścić swoje wirtualne środowisko Pythona:

$ deactivate
$ rm -rf pyenv

To dotyczy programu Excel 2016:

Właśnie miałem ten sam problem z podziałem wierszy w pliku csv za pomocą kreatora Excel.

Później próbowałem to z funkcją „Nowe zapytanie”: Dane -> Nowe zapytanie -> Z pliku -> Z CSV -> Wybierz plik -> Importuj -> Załaduj

Działało doskonale i było bardzo szybkim obejściem dla wszystkich, którzy mają ten sam problem.

To, co właśnie zadziałało, dla mnie, importowanie do programu Excel bezpośrednio pod warunkiem, że import jest wykonywany w formacie tekstowym, a nie w formacie csv. M /

po prostu utwórz nowy arkusz z komórkami z podziałem wierszy, zapisz go w csv, a następnie otwórz za pomocą edytora, który może wyświetlać znaki końca linii (np. Notatnik ++). W ten sposób zauważysz, że podział wiersza w komórce jest kodowany za pomocą LF, podczas gdy „prawdziwy” koniec wiersza to kod CR LF. Voilà, teraz wiesz, jak wygenerować „prawidłowy” plik CSV dla programu Excel.

Miałem też ten problem: tj. Pliki csv (ciągi rozdzielane przecinkami, podwójne cudzysłowy) z LF w cudzysłowach. Były to pobrane pliki Square. Zrobiłem import danych, ale zamiast importować jako pliki tekstowe, zaimportowałem jako „z HTML”. Tym razem zignorował LF w cytowanych ciągach.

To działało na Macu, używając csv i otwierając plik w Excelu.

Używanie języka Python do zapisywania pliku csv.

data = '"pierwsza linia komórki a1 \ r druga linia w komórce a1 \ r trzecia linia w komórce a1", "komórka b1", "pierwsza linia w komórce c1 \ r druga linia w komórce c1" \ n "pierwsza linia w komórka a2 "\ n '

file.write (dane)

Na MacOS spróbuj użyć Numbers

Jeśli masz dostęp do systemu Mac OS, zauważyłem, że arkusze kalkulacyjne Apple Numbers wykonują dobrą robotę, usuwając złożony wielowierszowy plik CSV, którego Excel nie mógł obsłużyć. Po prostu otwórz za .csvpomocą Numbers, a następnie wyeksportuj do programu Excel.

W moim przypadku otwarcie CSV w notatniku ++ i dodanie SEP=","jako pierwszej linii pozwala mi bez problemów otworzyć CSV z podziałami linii i utf-8 w Excelu

Zastąp separator TAB (\ t) zamiast przecinkiem (,). Następnie otwórz plik w swoim edytorze (Notatniku itp.), Skopiuj stamtąd zawartość, a następnie wklej ją do pliku Excel.

Podziały wierszy w cudzysłowach są idealnie zgodne ze standardem CSV. Analiza podziałów wierszy w programie Excel zależy od ustawienia systemu operacyjnego separatora listy:

1. Windows: musisz ustawić separator listy na przecinek (Region i język »Formaty» Zaawansowane) Źródło: /superuser/238944/how-to-force-excel-to-open-csv-files- z-danymi-uporządkowanymi-w-kolumnach # answer-633302

2. Mac: musisz zmienić region na USA (a następnie ręcznie zmienić inne ustawienia zgodnie z własnymi preferencjami) Źródło: https://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/line-separator-comma -semicolon-in-excel-2016-for / 7db1b1a0-0300-44ba-ab9b-35d1c40159c6 (patrz odpowiedź NewmanLee)

Nie zapomnij całkowicie zamknąć programu Excel przed ponowną próbą.

Z powodzeniem zreplikowałem problem i udało mi się go naprawić, korzystając z powyższego, zarówno w systemie Max, jak i Windows.