Создание файла CSV для Excel, как добавить новую строку внутри значения

Мне нужно создать файл для Excel, некоторые значения в этом файле содержат несколько строк.

там также есть неанглийский текст, поэтому файл должен быть в формате Unicode.

Файл, который я сейчас генерирую, выглядит так: (в UTF8, с смешанным неанглийским текстом и с большим количеством строк)

Header1,Header2,Header3
Value1,Value2,"Value3 Line1
Value3 Line2"

Обратите внимание, что многострочное значение заключено в двойные кавычки с обычным повседневным символом новой строки.

Согласно тому, что я нашел в Интернете, это должно работать, но это не так, по крайней мере, не выигрывает файлы Excel 2007 и UTF8, Excel обрабатывает 3-ю строку как вторую строку данных, а не как вторую строку первой строки данных. .

Это должно работать на компьютерах моих клиентов, и я не могу контролировать их версию Excel, поэтому мне нужно решение, которое будет работать с Excel 2000 и более поздними версиями.

Спасибо

РЕДАКТИРОВАТЬ: Я «решил» свою проблему, имея два варианта CSV, один для Excel (Unicode, разделенные табуляцией, без символов новой строки в полях) и один для остального мира (UTF8, стандартный CSV).

Не то, что я искал, но, по крайней мере, это работает (пока)


csv excel newline
person Nir    schedule 06.08.2009    source источник
comment
К вашему сведению: все это отлично работает в LibreOffice, и в первую очередь импортировать CSV намного проще.   -  person user2061057    schedule 30.12.2016
comment
Принятый ответ о лишних пробелах невероятно сбивает с толку теперь, когда вы отредактировали свои вопросы и удалили пробелы ...   -  person Matti Virkkunen    schedule 28.07.2017

Ответы (19)


arrow_upward
72
arrow_downward

У вас должны быть пробелы в начале полей ТОЛЬКО там, где пробелы являются частью данных. Excel не удаляет начальные пробелы. Вы получите нежелательные пробелы в заголовках и полях данных. Хуже того, ", который должен «защищать» этот разрыв строки в третьем столбце, будет проигнорирован, потому что он находится не в начале поля.

Если в файле есть символы, отличные от ASCII (закодированные в UTF-8), в начале файла должна быть спецификация UTF-8 (3 байта, hex EF BB BF). В противном случае Excel будет интерпретировать данные в соответствии с кодировкой по умолчанию для вашего языкового стандарта (например, cp1252) вместо utf-8, и ваши символы, отличные от ASCII, будут удалены.

Следующие комментарии относятся к Excel 2003, 2007 и 2013; не тестировалось в Excel 2000

Если вы откроете файл, дважды щелкнув его имя в проводнике Windows, все будет работать нормально.

Если вы откроете его из Excel, результаты будут разными:

  1. У вас есть только символы ASCII в файле (и нет спецификации): работает.
  2. У вас есть символы, отличные от ASCII (закодированные в UTF-8), в файле со спецификацией UTF-8 в начале: он распознает, что ваши данные закодированы в UTF-8, но игнорирует расширение csv и переводит вас в < em> Text Import not-a-Wizard, к сожалению, в результате возникает проблема с разрывом строки.

Варианты включают:

  1. Обучите пользователей не открывать файлы из Excel :-(
  2. Рассмотрите возможность написания файла XLS напрямую ... для этого доступны пакеты / библиотеки в Python / Perl / PHP / .NET / и т. Д.
person John Machin    schedule 09.08.2009
comment
Спасибо, я исправил проблему с ведущими пробелами в вопросе, я набрал пример CSV вручную и не копировал и не вставлял из реального файла, настоящий файл не включает эти пробелы, хороший улов. - person Nir; 10.08.2009
comment
@Nir: А теперь давайте поговорим о вашей реальной проблеме. Это означает, что у вас была спецификация UTF-8, вы открыли файл из Excel и получили мастер импорта текста, не распознающий, что ваша новая строка Value3 должна быть защищена - правильно? Или, возможно, у вас не было спецификации UTF-8, и вы должны были сообщить TIW, что ваши данные были закодированы в UTF-8, и что это все еще не соответствует новой строке? - person John Machin; 10.08.2009
comment
Что, если я хочу использовать | в качестве разделителя полей, новая строка в качестве разделителя записей, используется для защиты содержимого текстовых полей, а текстовые поля могут содержать |, и новую строку. Это возможно? - person Giorgio; 27.10.2011
comment
К вашему сведению: у меня есть Excel2007 и CSV, экспортированные из системы Redmine. После добавления спецификации UTF-8 (EFBBBF) в начале Excel отлично открыл файл. Новые строки, встроенные в столбец описания проблемы, обрабатываются правильно, структура строк не повреждена, и все национальные символы читаются правильно (они были удалены при чтении без спецификации UTF8). В Excel даже не отображается мастер импорта текста. В настоящее время этот CSV имеет заголовок EFBBBF, использует 0A в качестве разделителя строк и 0D0A в качестве новой строки внутри строк в текстовых ячейках. - person quetzalcoatl; 07.01.2015
comment
Если вы пытаетесь заставить Excel для OS X правильно читать CSV, а также Excel для Windows, вот отличный ресурс: stackoverflow.com/questions/4348802/ - person Alexandre R. Janini; 25.02.2015
comment
В macOS добавление метки спецификации в начале файла в кодировке UTF-8 сделало это за меня. Теперь Excel отображает все символы правильно, и новые строки больше не вызывают проблем. Огромное спасибо! - person Lukas Petr; 26.09.2016
comment
Поработал для меня, чтобы добавить спецификацию UTF-8 в начале, мой файл CSV отлично читается в Excel 16.2 на Mac, который содержит символы, отличные от ASCII, закодированные в UTF-8, alson содержит обратные строки - person Zikofs; 24.01.2020

arrow_upward
30
arrow_downward

После множества настроек вот конфигурация, которая работает, генерируя файлы в Linux, читая в Windows + Excel, хотя встроенный формат новой строки не соответствует стандартный:

  • Новые строки в поле должны быть \ n (и, очевидно, заключены в двойные кавычки)
  • Конец записи: \ r \ n
  • Убедитесь, что вы не начинаете поле с равенства, иначе оно будет обработано как формула и усечено.

В Perl я использовал Text :: CSV, чтобы сделать это следующим образом:

use Text::CSV;

open my $FO, ">:encoding(utf8)", $filename or die "Cannot create $filename: $!";
my $csv = Text::CSV->new({ binary => 1, eol => "\r\n" });

#for each row...:
$csv -> print ($FO, \@row);
person Ian    schedule 01.03.2012
comment
Да, это \ r \ n. Я могу подтвердить, что это работает с Windows + Excel, OSX + Numbers и Google Docs. - person Yorick Sijsling; 05.08.2012
comment
Использование \ n (также пробовал \ n) в поле, заключенном в, и использование \ r \ n для разделения строк. Я все еще не решил эту проблему в Excel 2010. Я пробовал ANSI и UTF8-with-BOM. Безуспешно - person nl-x; 15.05.2013
comment
Но это потому, что я использую | как разделитель полей. Если я использую; в качестве разделителя полей проблема все еще существует при импорте данных CSV, но проблема исчезает при открытии CSV двойным щелчком по нему в проводнике. - person nl-x; 15.05.2013
comment
Ответ Яна не работает для меня в Excel 2003/2010 в Windows 7. Я попытался использовать шестнадцатеричный редактор для редактирования моего файла спецификации UTF-8 и удалил 0D (\ r) из битов '0D0A' (\ r \ n) для новых строк внутри полей. Но не работает. - person Dan W; 01.06.2013
comment
Этот ответ сработал для меня (без изменений!) С использованием Excel 2010 и WIndows 7; также используется perl v5.14.2, который поставляется с cygwin. Все мои встроенные символы новой строки были \n. Спасибо - person ardnew; 01.07.2015
comment
У меня не работает в Excel 2013 на Windows 8.1. Использовал шестнадцатеричный редактор, такой как @DanW, чтобы изменить разрывы строк в ячейке на 0D, сохранив 0D0A в качестве разделителя строк. Также добавлена ​​спецификация и, конечно же, все поле заключено в двойные кавычки. Независимо от того, открываю ли я CSV-файл двойным щелчком или использую мастер импорта из текста, возврат cariage обрабатывается неправильно, и текст переносится в дополнительную строку. - person CodeManX; 16.11.2015
comment
Убедитесь, что вы развернули строку формул публикации Excel 2007, чтобы можно было увидеть лишнюю строку, или увеличьте высоту ячейки. Глупо, я знаю, но после долгого дня он отправил меня в крысиную нору ... - person Tuntable; 20.04.2019

arrow_upward
21
arrow_downward

Недавно у меня была аналогичная проблема, я решил ее, импортировав файл HTML, базовый пример будет таким:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
  <head>
    <style>
      <!--
      br {mso-data-placement:same-cell;}
      -->
    </style>
  </head>
  <body>
    <table>
      <tr>
        <td>first line<br/>second line</td>
        <td style="white-space:normal">first line<br/>second line</td>
      </tr>
    </table>
  </body>
</html>

Я знаю, что это не CSV, и он может работать по-разному в разных версиях Excel, но я думаю, что стоит попробовать.

Надеюсь, это поможет ;-)

person dtldarek    schedule 06.06.2013
comment
@GusDeCooL Формулировка первого предложения OP Мне нужно сгенерировать файл для Excel, некоторые значения в этом файле содержат несколько строк. предполагает, что, возможно, это не должен быть файл CSV. Кроме того, формат вопросов и ответов применим и к другим читателям, и он может быть жизнеспособным выбором для некоторых из них (даже если OP должен использовать CSV). Я считаю ваш голос против неразумным (тем не менее, спасибо за объяснение почему). - person dtldarek; 26.11.2013
comment
Фактически, это был лучший вариант для меня; спасибо за предложение! - person Jordan Gray; 17.03.2020

arrow_upward
8
arrow_downward

Стоит отметить, что если в файле .CSV есть поля, заключенные в двойные кавычки, содержащие разрывы строк, Excel не будет правильно импортировать файл .CSV, если файл .CSV записан в формате UTF-8. Excel обрабатывает разрыв строки как CR / LF и начинает новую строку. Таблица искажена. Это кажется правдой, даже если точки с запятой используются в качестве разделителей полей (вместо запятых).

Проблему можно решить, используя Блокнот Windows для редактирования файла .CSV, используя Файл> Сохранить как ... для сохранения файла и перед сохранением файла изменив кодировку файла с UTF-8 на ANSI. Как только файл будет сохранен в формате ANSI, я обнаружил, что Microsoft Excel 2013, работающий в Windows 7 Professional, будет правильно импортировать файл.

person OneSkyWalker    schedule 28.04.2015

arrow_upward
8
arrow_downward

В Excel 365 при импорте файла:

Данные - ›Из текста / CSV: Из текста / CSV

- ›Выбрать файл› Преобразовать данные:

«Выбрать

В редакторе Power Query, справа в разделе «Параметры запроса», в разделе «ПРИЛОЖЕННЫЕ ШАГИ» в строке «Источник» щелкните значок «Параметры».

Значок настроек источника

- ›В раскрывающемся списке разрывов строк выберите Игнорировать разрывы строк внутри кавычек.

Игнорировать перенос строки в кавычки

Затем нажмите ОК - ›Файл -› Закрыть и загрузить.

person Clodoaldo Neto    schedule 11.09.2019
comment
Я счастлив, что прокрутил это вниз! Я также добавил скриншоты с шагов и добавил термины из английской версии Excel. - person np8; 14.09.2020

arrow_upward
7
arrow_downward

Новая строка внутри значения, похоже, работает, если вы используете точку с запятой в качестве разделителя вместо запятой или табуляции и используете кавычки.

У меня это работает как в Excel 2010, так и в Excel 2000. Однако, что удивительно, это работает только тогда, когда вы открываете файл как новую электронную таблицу, а не когда вы импортируете его в существующую электронную таблицу с помощью функции импорта данных.

person Esben    schedule 22.09.2011
comment
да, но тогда я не нашел возможности сделать конечную строку с точкой с запятой в excel - person GusDeCooL; 25.11.2013
comment
Что, если некоторые из фактических текстовых данных содержат точку с запятой? Это не сработает. - person htm11h; 24.07.2017

arrow_upward
4
arrow_downward

На ПК символ ASCII # 10 - это то, что вы хотите поместить в новую строку внутри значения.

Однако после того, как вы загрузите его в Excel, вам необходимо убедиться, что для многострочных ячеек включен перенос слов, иначе новая строка будет отображаться в виде квадратного поля.

person devuxer    schedule 06.08.2009

arrow_upward
3
arrow_downward

Это не сработает, если вы попытаетесь импортировать файл в EXCEL.

Свяжите расширение файла csv с EXCEL.EXE, чтобы вы могли вызвать EXCEL, дважды щелкнув файл csv.

Здесь я помещаю текст, за которым следует символ NewLine, за которым следует еще текст, И заключая всю строку в двойные кавычки.

Не используйте CR, так как EXCEL поместит часть строки в следующую ячейку.

""text" + NL + "text""

Когда вы запустите EXCEL, вы увидите это. Возможно, вам придется автоматически изменить высоту, чтобы увидеть все. Место разрыва строки будет зависеть от ширины ячейки.

2

ДАТА

Вот код на Basic

CHR$(34,"2", 10,"DATE", 34)
person Peabody    schedule 15.09.2013

arrow_upward
2
arrow_downward

Я нашел это, и это сработало для меня

$delimiter = ',';
$enc1 = '"';
$enc2 = '""';

Тогда где вам нужно что-то вложить

$myfile = ('/path/to/myfile.csv');
//erase any previous contents
$fp = fopen($myfile, 'w+');
fwrite($fp, $enc1 .  'Column Heading 1' . $enc1 . $delimiter );
//append to new file
$fp2 = fopen($myfile, 'a');
fwrite($fp2, $enc1 .  'Column Heading 2' . $enc1 . $delimiter );

.....

fwrite($fp2, $enc1 .  'Last Column Heading' . $enc1 . $delimiter. PHP_EOL );

Затем, когда вам нужно что-то написать - например, HTML, который включает "вы можете сделать это

fwrite($fp2, $enc2 .  $myhtmlstring . $enc2 . $delimiter);

Новые строки заканчиваются на . PHP_EOL

В конце скрипта выводится ссылка, по которой пользователь может скачать файл.

echo 'Click <a href="myfile.csv">here</a> to download file';
person Lisa Simpson    schedule 22.07.2011

arrow_upward
1
arrow_downward

Файлы UTF, содержащие спецификацию, заставят Excel обрабатывать новые строки буквально, даже если это поле заключено в кавычки. (Протестировано в Excel 2008 Mac)

Решение состоит в том, чтобы делать любые новые строки возвратом каретки (CHR 13), а не переводом строки.

person Stephen    schedule 09.12.2011
comment
Кажется, что Excel 2016 правильно обрабатывает мой CSV-файл, даже если он имеет спецификацию UTF8. Однако все, что имело значение, - это использование ';' в качестве разделителя полей (это то, что Excel делает для всех языков, в которых в качестве десятичного разделителя используется ','). - person Ale; 07.04.2019

arrow_upward
1
arrow_downward

Проверьте это: у меня он полностью работает: поместите следующие строки в файл xxxx.csv

hola_x,="este es mi text1"&CHAR(10)&"I sigo escribiendo",hola_a

hola_y,="este es mi text2"&CHAR(10)&"I sigo escribiendo",hola_b

hola_z,="este es mi text3"&CHAR(10)&"I sigo escribiendo",hola_c

Открыть с помощью Excel.

в некоторых случаях открывается напрямую, в противном случае нужно будет использовать столбец для преобразования данных. увеличьте ширину столбца и нажмите кнопку переноса текста. или отформатируйте ячейки и активируйте перенос текста.

и спасибо за другие предложения, но они не сработали для меня. Я нахожусь в чистом окружении Windows и не хочу играть с юникодом или другими забавными вещами.

Таким образом, вы помещаете формулу из csv в excel. У этого метода работы может быть много применений. (обратите внимание на = перед кавычками)

pd: В свои предложения, пожалуйста, помещайте образцы данных, а не только код.

person Ivan Cev    schedule 28.01.2016

arrow_upward
1
arrow_downward

размещение "\ r" в конце каждой строки на самом деле имело эффект разрывов строк в Excel, но в .csv он исчез и оставил уродливый беспорядок, где каждая строка была сдавлена ​​соседней без пробела и без разрывов строк

person Duncan Wallace    schedule 04.02.2017

arrow_upward
1
arrow_downward

Только для открытия файла синтаксис

 ,"one\n
 two",...

Важно то, что после первого символа "," нет пробела. Обычно можно использовать пробелы, они обрезаются, если строка не заключена в кавычки. Но в остальном мерзко. Мне потребовалось время, чтобы понять это.

Кажется, не имеет значения, заканчивается ли строка \ n или \ c \ n.

Убедитесь, что вы развернули строку формул, чтобы вы могли видеть текст в ячейке (получил меня после долгого дня ...)

Теперь, конечно, File Open не будет поддерживать UTF-8 должным образом (если не использовать уловки).

Excel> Данные> Получить внешние данные> Из текста

Может быть установлен в режим UTF-8 (это самый нижний по списку шрифтов). Однако в этом случае новые строки, похоже, не работают, и я не знаю, как это исправить.

(Можно подумать, что через 30 лет М.С. все поймет правильно.)

person Tuntable    schedule 20.04.2019

arrow_upward
0
arrow_downward

То, как мы это делаем (мы используем VB.Net), заключается в том, чтобы заключить текст в новые строки в Chr (34), который представляет собой символ, представляющий двойные кавычки, и заменить все символы CR-LF на LF.

person Sebastian    schedule 06.10.2009

arrow_upward
0
arrow_downward

Обычно новая строка - "\ r \ n". В моем CSV я заменил "\ r" пустым значением. Вот код в Javascript:

cellValue = cellValue.replace(/\r/g, "")

Когда я открываю CSV в MS Excel, все работает хорошо. Если значение состоит из нескольких строк, оно останется в пределах одной ячейки на листе Excel.

person Tam Tran    schedule 14.05.2015

arrow_upward
-1
arrow_downward

вы можете сделать следующий "\"Value3 Line1 Value3 Line2\"". Он работает для меня, создавая файл csv в java

person Fredy Chica    schedule 06.12.2013

arrow_upward
-2
arrow_downward

Вот интересный подход с использованием JavaScript ...

  String.prototype.csv = String.prototype.split.partial(/,\s*/);  

  var results = ("Mugan, Jin, Fuu").csv();                        

  console.log(results[0]=="Mugan" &&                                   
         results[1]=="Jin" &&                                     
         results[2]=="Fuu",                                       
         "The text values were split properly");
person ddeloy    schedule 02.06.2013

arrow_upward
-4
arrow_downward

Печать новой строки HTML <br/> в содержимом и открытие в Excel будет нормально работать в любом Excel.

person Shashi    schedule 25.10.2013

arrow_upward
-4
arrow_downward

Вы можете использовать сочетание клавиш ALT + Enter.

  1. Выберите ячейку, которую хотите отредактировать
  2. войдите в режим редактирования, дважды щелкнув по нему или нажав F2 3. Нажмите Alt + Enter. Это создаст новую строку в ячейке
person Meghana Chamarthy    schedule 07.02.2018
comment
Как это интегрировать в создание файлов CVS? - person jikuja; 10.08.2018