Импорт CSV с переносами строк в Excel 2007
Я работаю над функцией экспорта результатов поиска в файл CSV, который будет открыт в Excel. Одним из полей является поле свободного текста, которое может содержать разрывы строк, запятые, цитаты и т. Д. Чтобы противодействовать этому, я заключил поле в двойные кавычки (").
Однако, когда я импортирую данные в Excel 2007, устанавливаю соответствующий разделитель и устанавливаю для текстового квалификатора двойные кавычки, разрывы строк по-прежнему создают новые записи в разрывах строк, где я ожидаю увидеть все текстовое поле в одиночная клетка
Я также попытался заменить CR/LF (\r\n) просто CR (\r), и снова просто LF (\n), но не повезло.
Кто-нибудь еще сталкивался с таким поведением, и если да, то как вы это исправили?
ТИА,
-J
РЕДАКТИРОВАТЬ:
Вот быстрый файл, который я написал вручную, чтобы продублировать проблему.
ID, название, описание
"12345","Смит, Джо", "Привет.
Меня зовут Джо."
Когда я импортирую это в Excel 2007, я получаю строку заголовка и две записи. Обратите внимание, что запятая в "Смит, Джо" обрабатывается правильно. Это просто разрывы строк, которые вызывают проблемы.
26 ответов
Я наконец нашел проблему!
Оказывается, мы писали файл, используя кодировку Unicode, а не ASCII или UTF-8. Изменение кодировки в FileStream, кажется, решает проблему.
Спасибо всем за все ваши предложения!
Excel (по крайней мере, в Office 2007 на XP) может вести себя по-разному в зависимости от того, импортирован ли файл CSV, открыв его из меню "Файл" -> "Открыть" или дважды щелкнув файл в проводнике.
У меня есть файл CSV, который находится в кодировке UTF-8 и содержит новые строки в некоторых ячейках. Если я открою этот файл из меню "Файл"> "Открыть" в Excel, появится окно "Импорт CSV", и файл не сможет быть правильно импортирован: новые строки начинаются с новой строки, даже если они заключены в кавычки. Если я открою этот файл, дважды щелкнув по нему в окне проводника, он откроется правильно, без вмешательства мастера.
Ни одно из предложенных решений не помогло мне.
Что на самом деле работает (с любой кодировкой):
Скопируйте / вставьте данные из csv-файла (откройте в редакторе), затем выполните "текст в столбцах" -> не работает, все в порядке.
Перейдите на следующую вкладку и снова скопируйте / вставьте (то же самое, что у вас уже есть в буфере обмена) -> теперь работает автоматически.
Если вы делаете это вручную, загрузите LibreOffice и используйте LibreOffice Calc для импорта CSV. Он гораздо лучше справляется с подобными вещами, чем любая пробная версия Excel, и может сохранять в XLS или XLSX по мере необходимости, если вам нужно потом перейти в Excel.
Но если вы застряли в Excel и нуждаетесь в лучшем исправлении, похоже, есть выход. Кажется, это зависит от локали (что, по моему скромному мнению, кажется идиотским). У меня нет Excel 2007, но у меня есть Excel 2010, и приведенный пример:
ID,Name,Description
"12345","Smith, Joe","Hey.
My name is Joe."
не работает Я написал это в Блокноте и выбрал Сохранить как..., а рядом с кнопкой Сохранить вы можете выбрать кодировку. Я выбрал UTF-8, как было предложено, но безуспешно. Однако смена запятых на точки с запятой сработала. Я ничего не изменил, и это просто сработало. Поэтому я изменил пример, чтобы он выглядел следующим образом, и выбрал кодировку UTF-8 при сохранении в Блокноте:
ID;Name;Description
"12345";"Smith, Joe";"Hey.
My name is Joe."
Но есть подвох! Единственный способ это работает, если дважды щелкнуть файл CSV, чтобы открыть его в Excel. Если я пытаюсь импортировать данные из текста и выбираю этот CSV, то он все равно не работает на заключенных в кавычки новых строках.
Но есть еще одна загвоздка! Разделитель рабочего поля (запятая в исходном примере, точка с запятой в моем случае), по-видимому, зависит от региональных настроек системы (установленных в Панели управления -> Регион и язык). В Норвегии запятая является десятичным разделителем. Excel, кажется, избегает этого символа и предпочитает точку с запятой. У меня есть доступ к другому компьютеру с английской локализацией в Великобритании, и на этом компьютере первый пример с разделителем запятых работает нормально (только при двойном щелчке), а тот, где точка с запятой, действительно не работает! Так много для совместимости. Если вы хотите опубликовать этот CSV в Интернете, и у пользователей может быть Excel, я думаю, вы должны опубликовать обе версии и предложить людям проверить, какой файл дает правильное количество строк.
Итак, все детали, которые я смог собрать, чтобы заставить это работать:
- Файл должен быть сохранен как UTF-8 с спецификацией, что делает Блокнот, когда вы выбираете UTF-8. Я пробовал UTF-8 без спецификации (можно легко переключать в Notepad++), но дважды щелкнуть документ не удастся.
- Вы должны использовать запятую или точку с запятой, но не ту, которая является десятичным разделителем в ваших региональных настройках. Возможно, другие персонажи работают, но я не знаю, какие именно.
- Вы должны заключить в кавычки поля, содержащие новую строку с символом ".
- Я использовал окончания строк Windows (\r\n) как в текстовом поле, так и в качестве разделителя записей, это работает.
- Вы должны дважды щелкнуть файл, чтобы открыть его, импорт данных из текста не работает.
Надеюсь, это кому-нибудь поможет.
Используйте Google Sheets и импортируйте файл CSV.
Затем вы можете экспортировать это для использования в Excel
Многострочный CSV можно легко импортировать в версии Excel с помощью Power Query, выполнив следующие действия (проверено в Excel 365 версии 2207):
- Перейти на вкладку Данные
- Нажмите «Из текста/CSV» на ленте.
- Выберите файл и нажмите Импорт
- Нажмите «Преобразовать данные», чтобы открыть редактор Power Query.
- Нажмите «Параметры источника данных» на ленте редактора Power Query.
- Нажмите «Изменить источник».
- Выберите «Игнорировать разрывы строк в кавычках» в раскрывающемся списке «Разрывы строк».
- Нажмите «ОК» -> «Закрыть» -> «Закрыть и загрузить».
Короткий ответ
Удалить символы перевода строки / перевода строки (\n с Блокнотом ++). Excel все равно распознает символ возврата каретки (\r) для разделения записей.
Длинный ответ
Как уже упоминалось, символы новой строки поддерживаются в полях CSV, но Excel не всегда обрабатывает их изящно. Я столкнулся с подобной проблемой с CSV стороннего производителя, который, возможно, имел проблемы с кодированием, но не улучшился с изменениями кодировки.
Для меня сработало удаление всех символов новой строки (\n). Это приводит к тому, что поля объединяются в одну запись, предполагая, что ваши записи разделены комбинацией возврата каретки и перевода строки (CR/LF). Затем Excel правильно импортирует файл и распознает новые записи по возврату каретки.
Очевидно, что более чистое решение - сначала заменить настоящие символы новой строки (\r\n) с временной комбинацией символов, заменяя символы новой строки (\n) с выбранным разделительным символом (например, запятая в файле с запятой), а затем снова замените временные символы соответствующими символами новой строки.
Если поле содержит начальный пробел, Excel игнорирует двойную кавычку в качестве квалификатора текста. Решением является устранение начальных пробелов между запятой (разделитель полей) и двойными кавычками. Например:
Разбитая:
Имя, название, описание
"Джон", "Мистер", "Мое подробное описание"
За работой:
Имя, название, описание
"Джон", "Мистер", "Мое подробное описание"
+1 на комментарий J Эшли. Я столкнулся с этой проблемой также. Оказывается, Excel требует:
Символ новой строки ("\n") в строке в кавычках
Возврат каретки и перевод строки между каждой строкой.
Например,"Тест", "Многострочный элемент \ n Многострочный элемент" \ r \ n "Тест2", "Многострочный элемент \ n Многострочный элемент" \ r \ n
Я использовал notepad ++, чтобы правильно разделять каждую строку и использовать только новые строки в строке. Обнаружил это, создав многострочные записи в пустом документе Excel и открыв CSV в Notepad ++.
Если кто-то наткнется на эту тему и ищет точный ответ, то здесь (спасибо человеку, упоминающему LibreOffice:
1) Установите LibreOffice 2) Откройте Calc и импортируйте файл 3) Мой текстовый файл имел разделенные поля и символьные поля, заключенные в " 4) сохранить как файл ODS 5) Открыть файл ODS в Excel 6) Сохранить как.xls(x) 7) Готово. 8) Это отлично сработало для меня и спасло меня БОЛЬШОЙ!
Обзор
Спустя почти 10 лет после первоначальной публикации Excel не улучшил импорт файлов CSV. Однако я обнаружил, что он намного лучше импортирует таблицы HTML. Итак, можно использовать Python для преобразования CSV в HTML, а затем импортировать полученный HTML в Excel.
Преимущества этого подхода: (а) он работает надежно, (б) вам не нужно отправлять свои данные в стороннюю службу (например, таблицы Google), (в) не требуется дополнительных "жирных" установок (LibreOffice, Числа и т. Д.) Для большинства пользователей, (d) более высокий уровень, чем вмешательство с символами CR/LF и маркерами спецификации, (e) нет необходимости возиться с настройками локали.
Шаги
Следующие шаги можно выполнить в любой оболочке, подобной bash, если установлен Python 3. Хотя Python можно использовать для прямого чтения CSV, csvkit используется для промежуточного преобразования в JSON. Это позволяет нам избежать тонкостей CSV в нашем коде Python.
Сначала сохраните следующий сценарий как json2html.py. Скрипт считывает файл JSON со стандартного ввода и выводит его в виде таблицы 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>")
Затем установите csvkit в виртуальной среде и используйте csvjsonчтобы передать входной файл нашему скрипту. Рекомендуется отключить определение типа ячейки с помощью-I аргумент:
$ virtualenv -p python3 pyenv
$ . ./pyenv/bin/activate
$ pip install csvkit
$ csvjson -I input.csv | python3 json2html.py > output.html
Сейчас output.htmlможно импортировать в Excel. Разрывы строк в ячейках будут сохранены.
При желании вы можете очистить виртуальную среду Python:
$ deactivate
$ rm -rf pyenv
С Excel 2019 у меня была похожая проблема при работе с файлами CSV через Данные -> Импорт из текстового файла/CSV. После того, как соединение установлено и данные синхронизированы, он сообщил о xx ошибках из-за смещенных столбцов, вызванных разрывами строк.
Мне удалось решить это с помощью
Отредактируйте запрос (Запрос -> Изменить)
Откроется редактор Power Query.
Перейдите в Пуск -> Расширенный редактор.
Это открывает запрос в текстовом формате, где в строке № 2 есть инструкция вроде
Источник = Csv.Document(File.Contents("my.csv"),[Delimiter=",", .... , QuoteStyle=QuoteStyle.None]),
Замените QuoteStyle.None на QuoteStyle.Csv.
Нажмите Готово
Применить и закрыть
Документация находится здесь: https://docs.microsoft.com/en-us/powerquery-m/csv-document
NB. С тех пор я нашел, где это «спрятано» в пользовательском интерфейсе. В редакторе Power Query щелкните Параметры источника данных, Изменить источник (внизу слева), и в списке Разрывы строк должно быть написано Игнорировать разрывы строк между кавычками.
НБ2. Здесь я работаю с голландским Excel, поэтому мои вышеупомянутые переводы заголовков кнопок и т. д. могут немного отличаться.
На MacOS попробуйте использовать Numbers
Если у вас есть доступ к Mac OS, я обнаружил, что электронные таблицы Apple Numbers отлично справляются со сложными многострочными CSV-файлами, которые Excel не может обработать. Просто откройте .csv с номерами, а затем экспортировать в Excel.
Excel невероятно сломан при работе с CSV. LibreOffice работает намного лучше. Итак, я узнал, что:
- Файл должен быть закодирован в UTF-8 с помощью спецификации, поэтому рассмотрите это для всех пунктов ниже
- Наилучший результат, безусловно, достигается при открытии его из проводника
- Если вы откроете его из Excel, возможны два результата:
- Если он имеет только символы ASCII, он, скорее всего, будет работать
- Если он содержит символы не ASCII, он испортит ваши разрывы строк
- Кажется, что он сильно зависит от десятичного разделителя, настроенного в региональных настройках ОС, поэтому вы должны выбрать правильный
- Могу поспорить, что он также может вести себя по-разному в зависимости от ОС и версии Office
Вставьте в Notepad++, выберите Кодировка> Кодировать в ANSI, снова скопируйте все и вставьте в Excel:)
У меня была похожая проблема. У меня были некоторые данные Twitter в MySQL. Данные имели перевод строки ( LF или \n) с данными. У меня было требование экспортировать данные MySQL в Excel. LF испортил мой импорт CSV-файла. Итак, я сделал следующее -
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
ПРИМЕЧАНИЕ. При замене CRLF или LF не забудьте установить флажок Excended (\n,\r,\t... флажок [посмотрите на левую нижнюю часть диалогового окна)
Мой опыт работы с Excel 2010 на WinXP с французскими региональными настройками
- разделитель вашего импортированного CSV должен соответствовать разделителю списка ваших региональных настроек (в моем случае)
- Вы должны дважды щелкнуть файл из проводника. не открывайте его из Excel
Это для Excel 2016:
Просто была такая же проблема с разрывами строк внутри файла csv с мастером Excel.
Впоследствии я пробовал это с помощью функции "Новый запрос": Данные -> Новый запрос -> Из файла -> Из CSV -> Выбрать файл -> Импорт -> Загрузить
Он работал отлично, и это было очень быстрое решение для всех вас, у которых есть одна и та же проблема.
Похоже, что это намного проще в более поздних версиях Excel:
- Перейдите в «Данные» -> «Получить данные (Power Query)».
- В открывшемся диалоге выберите справа "Текст/CSV"
- Найдите файл, а затем нажмите «Далее» и следуйте рекомендациям (в моем случае Excel теперь правильно понял, что это UTF8, и что ячейки были разделены «;», а текстовый идентификатор был двойными кавычками (")
- Готово!
Загрузка заняла немного времени, но потом у меня была таблица с автоматическим форматированием, которая выглядела очень красиво и понимала, что многострочные записи по-прежнему являются частью одной и той же записи.
Если вы хотите, чтобы многострочные строки отображались правильно, просто отформатируйте ячейки и в разделе «Выравнивание» установите флажок «Переносить текст». Это должно решить последнюю из ваших проблем.
Удачи! ;-)
В моем случае открываем CSV в notepad++ и добавляем SEP="," так как первая строка позволяет мне открывать CSV с разрывами строк и utf-8 в Excel без проблем
Это работало на Mac, используя CSV и открывая файл в Excel.
Использование Python для записи файла CSV.
data = '"первая строка ячейки a1\r 2-я строка в ячейке a1\r 3-я строка в ячейке a1","cell b1","1-я строка в ячейке c1\r 2-я строка в ячейке c1" \ n "первая строка в ячейка a2 "\ n '
file.write (данные)
Замените разделитель на TAB(\t) вместо запятой (,). Затем откройте файл в редакторе ("Блокнот" и т. Д.), Скопируйте содержимое оттуда и вставьте его в файл Excel.
Что только сработало для меня, импорт в Excel напрямую, при условии, что импорт выполняется в текстовом формате, а не в формате CSV. M/
У меня также была эта проблема: т.е., CSV-файлы (разделенные запятыми, строки с двойными кавычками) с LF в строках в кавычках. Это были скачанные квадратные файлы. Я сделал импорт данных, но вместо того, чтобы импортировать как текстовые файлы, импортированные как "из HTML". На этот раз он проигнорировал LF в цитируемых строках.
Просто создайте новый лист с ячейками с переносом строки, сохраните его в csv, затем откройте его в редакторе, который может отображать символы конца строки (например, notepad++). Сделав это, вы заметите, что разрыв строки в ячейке кодируется с помощью LF, а "реальный" конец строки - это код с CR LF. Вуаля, теперь вы знаете, как создать "правильный" CSV-файл для Excel.
Разрывы строк внутри двойных кавычек идеально подходят в соответствии со стандартом CSV. Разбор разрывов строк в Excel зависит от настройки ОС разделителя списков:
Windows: вам нужно установить разделитель списка на запятую (Регион и язык "Форматы" Дополнительно) Источник: https://superuser.com/questions/238944/how-to-force-excel-to-open-csv-files-with-data-arranged-in-columns
Mac: необходимо изменить регион на США (затем вручную изменить другие настройки по своему усмотрению). Источник: https://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/line-separator-comma-semicolon-in-excel-2016-for/7db1b1a0-0300-44ba-ab9b-35d1c40159c6 (см. ответ NewmanLee)
Не забудьте полностью закрыть Excel, прежде чем пытаться снова.
Я успешно воспроизвел проблему и смог исправить ее, используя вышеупомянутые функции как в Max, так и в Windows.