Excel csv разделитель запятая
Я использую Excel и хотел бы экспортировать мой файл в виде листа .scsv (значения, разделенные точкой с запятой) вместо .csv. Есть какой-либо способ сделать это?
пс. Я не могу сделать .csv и искать и заменять запятые точками с запятой, потому что поля уже содержат запятые.
Если вы готовы немного заняться программированием, конвертер можно было бы довольно легко написать на языке программирования Python, используя его встроенный csv модуль, который позволит легко читать файлы Excel в формате CSV, а затем писать новые версии, которые были о любом разделителе желаемом. Это зависит от того, есть ли в записях запятые или нет.Как насчет выполнения Tab Delimited и замены вкладок точкой с запятой?
В Excel: Файл -> Сохранить как -> в поле Формат выберите «Tab с разделителями текста (.txt)», затем его сохранить.
Откройте файл в блокноте и откройте окно замены. Поскольку вы не можете использовать вкладку непосредственно в этом окне, скопируйте вкладку из документа, а затем вставьте ее в поле «Найти» и вставьте свою; в коробку замены. Тогда замени все.
Я предполагаю, что это будет работать, потому что очень редко иметь вкладки в документе Excel.
Несмотря на то, что он является базовым для большинства пользователей, не могли бы вы также предоставить набор инструкций? Можно ли это сделать полностью в Excel, и если да, то как? Я не думаю, что это можно сделать полностью в Excel. Excel скрывает форматирование (вкладки), поэтому вы не сможете их заменить. Заменив их точкой с запятой, вы также не сможете открыть с помощью Excel (это будет просто одна гигантская клетка) Я предполагаю, что использование регулярных выражений для замены и размещения \t будет работать Если в вашем файле есть специальные символы другого языка (например, греческого), это не работает, так как символы символов заменяются на «?»Я не думаю, что вы можете установить разделитель вывода непосредственно в Excel, но (при условии Windows) вы можете изменить разделитель списка ОС на ; - это сделано в Regional Settings->Customize . Excel будет использовать этот параметр для вывода ваших CSV-файлов.
Важное примечание: если вы сделаете это, все программы Windows, которые используют интернационализированные библиотеки Windows, примут это; является символом-разделителем для всех файлов CSV. Если это одноразовое решение, возможно, имеет смысл сохранить обычный CSV-файл и использовать редактор для замены запятых на точки с запятой Это изменение также повлияет на открытие файлов CSV в Excel. И, как упоминалось в комментарии выше, возможно, больше программ. Если вы хотите создать более совместимый CSV-файл, поместите его sep=; поверх CSV-файла, а затем, после сохранения файла, вы можете изменить разделитель списков на прежний.Используйте LibreOffice Calc (это бесплатная загрузка и работает на всех основных операционных системах).
Чтобы сохранить значения, разделенные точкой с запятой:
- Откройте файл xlsx / csv
- Файл -> Сохранить как .
- Выберите «Фильтр CSV» в качестве фильтра (должен быть по умолчанию, если имя вашего файла заканчивается .csv )
- Отметьте «Изменить настройки фильтра» и нажмите Сохранить.
- Когда он запрашивает подтверждение формата файла, нажмите «Использовать текстовый формат CSV»
- Измените «Разделитель полей» на точку с запятой (здесь вы можете ввести что угодно).
- Нажмите ОК
LO Calc будет правильно обрабатывать запятые и точки с запятой в значениях ячеек.
Спасибо, хорошо работает. Это должен быть принятый ответ.Если вам нужен скрипт, вставьте следующее в модуль.
Чтобы изменить запятую на точку с запятой в качестве разделителя Excel по умолчанию для CSV - перейдите в раздел «Регион» -> «Дополнительные параметры» -> вкладка «Числа» -> «Разделитель и тип списка»; вместо значения по умолчанию,
Вы можете изменить разделитель глобально через Настроить формат.
Перейдите в раздел «Регион и язык», откройте «Регион», на вкладке «Форматы» нажмите кнопку «Дополнительные настройки», а в таблице «Номера» измените значение для разделителя списка:
1.> Измените формат файла на .CSV (точка с запятой)
Для достижения желаемого результата нам нужно временно изменить настройку разделителя в настройках Excel.
Переместить в файл -> Параметры -> Дополнительно -> Редактирование раздела
Снимите флажок «Использовать системные разделители» и введите запятую в поле «Десятичный разделитель».
Теперь сохраните файл в формате .CSV, и он будет сохранен в формате точки с запятой .
Если ваш список значений находится в столбце A, попробуйте следующее: Ячейка B2 = A1 & ";" & Ячейка A2 B3 = B2 & ";" & A3 Скопируйте ячейку B3 в конец списка. Если вы скопируете и вставите последнюю ячейку в своем списке в качестве значений, у вас будет список, разделенный точкой с запятой. Затем вы можете скопировать это в Блокнот, Word или куда угодно.
Вы можете сделать это легко в 4 этапа с помощью функции конкатенации Excel.
В вашем файле добавьте столбец с точкой с запятой, где вы ожидаете, что они попадут в экспорт .csv.
Начните новый столбец в конце ваших данных и в первой строке используйте =concatenate() функцию. Обязательно выделите все ячейки, которые вы хотите отобразить в конечном файле. Ваша формула будет выглядеть примерно так =concatenate(A1,A2,A3. )
Скопируйте и вставьте полученную формулу вниз во все строки данных.
Выделите весь столбец формул и вставьте Специальные -> Значения в соседний столбец. Теперь все ваши данные разделены точками с запятой.
Наконец, экспортируйте файл в выбранный вами формат. Если вы экспортируете это как .txt в Windows, вы можете просто изменить расширение файла на .csv, потому что оба являются текстовыми форматами.
CSV является стандартом де-факто для связи между собой разнородных систем, для передачи и обработки объемных данных с «жесткой», табличной структурой. Во многих скриптовых языках программирования есть встроенные средства разбора и генерации, он хорошо понятен как программистам, так и рядовым пользователям, а проблемы с самими данными в нем хорошо обнаруживаются, как говорится, на глаз.
История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.
В этой статье я попробую обобщить существующие знания об этом формате, указать на типичные ошибки, а также проиллюстрировать описанные проблемы на примере кривой реализации импорта-экспорта в Microsoft Office 2007. Также покажу, как обходить эти проблемы (в т.ч. автоматическое преобразование типов Excel-ом в DATETIME и NUMBER) при открытии .csv.
Начнем с того, что форматом CSV на самом деле называют три разных текстовых формата, отличающихся символами-разделителями: собственно сам CSV (comma-separated values — значения, разделенные запятыми), TSV (tab-separated values — значения, разделенные табуляциями) и SCSV (semicolon separated values — значения, разделенные точкой с запятой). В жизни все три могут называться одним CSV, символ-разделитель в лучшем случае выбирается при экспорте или импорте, а чаще его просто «зашивают» внутрь кода. Это создает массу проблем в попытке разобраться.
Как иллюстрацию возьмем казалось бы тривиальную задачу: импортировать в Microsoft Outlook данные из таблицы в Microsoft Excel.
В Microsoft Excel есть средства экспорта в CSV, а в Microsoft Outlook — соответствующие средства импорта. Что могло быть проще — сделал файлик, «скормил» почтовой программе и — дело сделано? Как бы не так.
Создадим в Excel тестовую табличку:
… и попробуем экспортировать ее в три текстовых формата:
| «Текст Unicode» | Кодировка — UTF-16, разделители — табуляция, переводы строк — 0×0D, 0×0A, объем файла — 222 байт |
| «CSV (разделители — запятые)» | Кодировка — Windows-1251, разделители — точка с запятой (не запятая!), во второй строке значение телефонов не взято в кавычки, несмотря на запятую, зато взято в кавычки значение «01;02», что правильно. Переводы строк — 0×0D, 0×0A. Объем файла — 110 байт |
| «Текстовые файлы (с разделителями табуляции)» | Кодировка — Windows-1251, разделители — табуляция, переводы строк — 0×0D, 0×0A. Значение «01;02» помещено в кавычки (без особой нужды). Объем файла — 110 байт |
Какой вывод мы делаем из этого. То, что здесь Microsoft называет «CSV (разделители — запятые)», на самом деле является форматом с разделителями «точка с запятой». Формат у Microsoft — строго Windows-1251. Поэтому, если у вас в Excel есть Unicode-символы, они на выходе в CSV отобразятся в вопросительные знаки. Также то, что переводами строк является всегда пара символов, то, что Microsoft тупо берет в кавычки все, где видит точку с запятой. Также то, что если у вас нет Unicode-символов вообще, то можно сэкономить на объеме файла. Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.
Теперь посмотрим, как на это смотрит Outlook. Попробуем импортировать эти файлы из него, указав такие же источники данных. Outlook 2007: Файл -> Импорт и экспорт… -> Импорт из другой программы или файла. Далее выбираем формат данных: «Значения, разделенные запятыми (Windows)» и «Значения, разделенные табуляцией (Windows)».
| «Значения, разделенные табуляцией(Windows)» | Скармливаем аутлуку файл tsv, с разделенными табуляцией значениями и. — чтобы вы думали. Outlook склеивает поля и табуляцию не замечает. Заменяем в файле табуляцию на запятые и, как видим, поля уже разбирает, молодец. |
| «Значения, разделенные запятыми (Windows)» | А вот аутлук как раз понимает все верно. Comma — это запятая. Поэтому ожидает в качестве разделителя запятую. А у нас после экселя — точка с запятой. В итоге аутлук распознает все неверно. |
Два майкрософтовских продукта не понимают друг друга, у них напрочь отсутствует возможность передать через текстовый файл структурированные данные. Для того, чтобы все заработало, требуются «пляски с бубном» программиста.
Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.
Более того, одна и та же функция сохранения в CSV, вызванная через интерфейс и через макрос, работает по-разному. Вариант с макросом не смотрит в региональные настройки вообще.
Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.
Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:
- между строками — перевод строки CRLF [на мой взгляд, им не стоило ограничивать двумя байтами, т.е. как CRLF (0×0D, 0×0A), так и CR 0×0D]
- разделители — запятые, в конце строки не должно быть запятой,
- в последней строке CRLF не обязателен,
- первая строка может быть строкой заголовка (никак не помечается при этом)
- пробелы, окружающие запятую-разделитель, игнорируются.
- если значение содержит в себе CRLF, CR, LF (символы-разделители строк), двойную кавычку или запятую (символ-разделитель полей), то заключение значения в кавычки обязательно. В противном случае — допустимо.
- т.е. допустимы переводы строк внутри поля. Но такие значения полей должны быть обязательно закавычены,
- если внутри закавыченной части встречаются двойные кавычки, то используется специфический квотинг кавычек в CSV — их дублирование.
Вот в нотации ABNF описание формата:
Также при реализации формата нужно помнить, что поскольку здесь нет указателей на число и тип колонок, поскольку нет требования обязательно размещать заголовок, здесь есть условности, о которых необходимо не забывать:
- строковое значение из цифр, не заключенное в кавычки может быть воспринято программой как числовое, из-за чего может быть потеряна информация, например, лидирующие нули,
- количество значений в каждой строке может отличаться и необходимо правильно обрабатывать эту ситуацию. В одних ситуациях нужно предупредить пользователя, в других — создавать дополнительные колонки и заполнять их пустыми значениями. Можно определиться, что количество колонок задается заголовком, а можно добавлять их динамически, по мере импорта CSV,
- Квотить кавычки через «слэш» не по стандарту, делать так не надо.
- Поскольку типизации полей нет, нет и требования к ним. Разделители целой и дробной частей в разных странах разные, и это приводит к тому, что один и тот же CSV, сгенрированный приложением, в одном экселе «понимается», в другом — нет. Потому что Microsoft Office ориентируется на региональные настройки Windows, а там может быть что угодно. В России там указано, что разделитель — запятая,
- Если CSV открывать не через меню «Данные», а напрямую, то Excel лишних вопросов не задает, и делает как ему кажется правильным. Например, поле со значением 1.24 он понимает по умолчанию как «24 января»
- Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
- У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.
Пример валидного CSV, который можно использовать для тестов:
точно такой же SCSV:
Первый файлик, который реально COMMA-SEPARATED, будучи сохраненным в .csv, Excel-ом не воспринимается вообще.
Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:
- Учлись пробелы, окружающие разделители
- Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
- В поле индекс Excel «опустил» ведущие нули.
- в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол
Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:
С приведением типов сработало, но зато теперь не обрабатываются нормально переводы строк и осталась проблема с ведущими нулями, кавычками и лишними пробелами. Да и пользователям так открывать CSV крайне неудобно.
Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.
И вот что случаеся, если мы открываем этот файлик в экселе:
csv Расширение получает связано с Excel при установке Excel. Однако, если я открою один из этих файлов с помощью Excel, все будет помещено в один и тот же столбец, и запятая не будет интерпретироваться как разделитель.
Я могу вместо Файл -> Импорт . выбрать файл и выбрать точный способ интерпретации содержимого (разделители, типы данных и т. Д.). Но в большинстве случаев я просто хочу посмотреть на файл через табличное представление. Я не хочу обрабатывать его в Excel.
Есть ли способ заставить Excel автоматически интерпретировать разделитель и показывать CSV-файл как правильную таблицу, как только он открывается? Мне нужно это, чтобы я мог использовать Excel для быстрого просмотра таких файлов.
Я подозреваю, что должен быть способ, иначе Excel не связался бы с файлами CSV.
Я действительно понимаю, что выбранный ответ действительно отвечает на ваш вопрос, но второй должен быть выделен, потому что он предлагает глобальное решение, которое работает через общий доступ по электронной почте, по сети и т. Д.При открытии CSV-файлов Excel будет использовать системный региональный параметр, вызываемый List separator для определения используемого по умолчанию разделителя.
Microsoft Excel открывает файлы .csv, но, в зависимости от региональных настроек системы, он может ожидать точку с запятой в качестве разделителя вместо запятой, поскольку в некоторых языках запятая используется в качестве десятичного разделителя. (из Википедии )
В Windows вы можете изменить List separator настройки в соответствии Regional and Language Options с указаниями на веб-сайте поддержки Office :
- Нажмите меню Пуск в Windows.
- Нажмите Панель управления.
- Откройте диалоговое окно «Язык и региональные стандарты».
- Нажмите вкладку региональных параметров.
- Нажмите Настроить / Дополнительные настройки (Win10).
- Введите новый разделитель в поле Разделитель списка.
- Нажмите ОК дважды.
Примечание : это работает только в том случае, если десятичный символ также не обозначен как запятая (в соответствии с приведенной выше цитатой из Википедии). Если это так, Excel не будет использовать запятую в качестве разделителя списка, даже если он выбран. Для многих регионов за пределами США запятая является десятичным символом по умолчанию.
В Mac OS X этот параметр, по-видимому, выводится из параметра десятичного разделителя (на панели « Язык и регион» в Системных настройках перейдите к разделу «Дополнительно» ). Если десятичный разделитель является точкой, то разделитель CSV по умолчанию будет запятой, но если разделитель десятичных чисел является запятой, то разделитель CSV по умолчанию будет точкой с запятой.
Как вы сами сказали в комментарии, у пользователей Mac есть альтернатива - быстро просмотреть эти CSV-файлы. Это плагин для Quick Look, называемый quicklook-csv, который обрабатывает обнаружение разделителя.
Файлы CSV автоматически связываются с Excel, но когда я открываю их, все строки в основном находятся в первом столбце, например:
Это, вероятно, потому, что когда Excel считает «значения, разделенные запятыми», он на самом деле ищет какой-то другой разделитель (я думаю, что это точка с запятой, но это не важно).
Теперь, когда я уже открыл этот файл в Excel, есть ли кнопка или что-то, чтобы сказать ему «открыть этот файл и использовать запятую в качестве разделителя»?
Я знаю, что могу импортировать данные в новый лист и т. Д., Но я специально обращаюсь за помощью в ситуации, когда у меня уже есть файл CSV с запятыми в нем, и я хочу открыть его в Excel, не создавая новую книгу или не преобразовывая исходный файл.
Так трудно понять, что должно происходить внутри Microsoft, что Excel все еще не может открывать CSV-файлы просто так. Это также разбивает числа и т. Д. Удивительным образом. Даже JavaScript не явно конвертирует это плохо. Извините, что могу копать . но есть очень хороший шанс, что ваша первая строка не содержит запятых, что заставляет Excel отказаться от попыток Просто используйте в ; качестве разделителя и, открыв файл двойным щелчком, разделите его на столбцы в MS Excel.Перейдите на вкладку « Данные » и выберите текст для столбцов в разделе « Инструменты данных», затем выберите « Разграничить» и нажмите « Далее» . Затем выберите запятую в качестве разделителя и нажмите " Готово"
в качестве первой строки вашего CSV-файла будет автоматически указана Excel использовать этот символ в качестве разделителя вместо запятой по умолчанию.
приведет к тому, что труба (|) будет использоваться в качестве разделителя вместо запятой, без необходимости пользователю сначала запускать мастер импорта.
Для этого , чтобы работать в Excel 2013, вы должны снимите Use system separators в Options>Advanced Можете ли вы определить десятичный разделитель подобным образом? Я хочу, чтобы это было . , например, потому что 1.5 в моей системе . есть разделитель для тысяч. Это сработало для меня, даже если я проверил «Использовать разделители системы». Никакое другое решение не помогло мне. Подтверждение того, что это работает в Excel 2016 с установленным «Использовать системные разделители». Это здорово. Идеальное решение глупой проблемы! (Excel должен быть в состоянии распознать используемый разделитель или, по крайней мере, спросил меня, и позвольте мне решить!) Далее : возможно ли заставить Excel использовать также другой набор символов ? Все мои CSV находятся в UTF-8, но Excel пытается открыть их как «Macintosh», что создает много проблем для меня!Я не знаю, удалось ли вам решить эту проблему, но у меня тоже была такая же проблема, и я разобрался с ней после некоторых расследований.
Оказывается, это была проблема региональных условий;
Зайдите в панель управления -> Региональные настройки -> Расширенные настройки и измените разделитель списка на запятую .
У меня была установлена точка с запятой для Южной Африки после того, как я перешел на Windows 8. После того, как я изменил его на запятую, все мои CSV правильно открываются в Excel 2013.
Надеюсь это поможет.
Дополнительный комментарий:
я использовал те же шаги, что и Lèse majesté, но я также изменил символ десятичной дроби с запятой (,) на точку с запятой (.), И это решило мою проблему.
Это связано с тем, что по умолчанию Windows 8 использует запятую в качестве десятичного знака, а Excel запутывается, когда ему приходится использовать оба символа в качестве разделителя и десятичного символа.
Уже установлено, как это, и игнорируется Excel. По-прежнему не разделяется на запятые. Ir, вероятно, требует по крайней мере выхода из системы, чтобы войти в силу. Сработал у меня после смены десятичного знака, спасибо! Ну, я все еще думаю, что это возмутительно, что Excel использует региональные настройки для того, чтобы прочитать формат файла! Благодарю. Этот параметр решил огромную проблему для меня на работе. Excel такой глупый!(Примечание. Мой компьютер использует настройки чешского формата, которые форматируют числа следующим образом: 1 000,00 т. Е. Запятая используется как десятичный разделитель, а пробел - как разделитель тысяч. Разделитель списка по умолчанию в настройках системы ; .)
- Я изменил системный разделитель списка на запятую в регионе -> Дополнительные настройки
- В Excel 2013 я перешел к «Параметры» -> «Дополнительно» и не выбрал пункт « Использовать системные разделители» (в разделе «Параметры редактирования», который является первым разделом).
- Я установил десятичный разделитель в Excel, . а разделитель тысяч - , (разделитель тысяч, вероятно, не имеет значения, но я хотел, чтобы он соответствовал форматированию США)
Альтернативой шагам 2 + 3 было бы изменение этих настроек в системных настройках (шаг 1), но я обычно хочу, чтобы числа были отформатированы в соответствии с чешским языком.
Недостатки : в Excel мне теперь нужно вводить десятичные числа, используя локали США, т.е. используя точку вместо запятой. Это немного неестественно, но для меня это приемлемый компромисс. К счастью, клавиша запятой на моей цифровой клавиатуре превратилась в клавишу точки автоматически (и только в Excel - другие приложения по-прежнему выводят запятую).
@LeosLiterak: Я только что попробовал это в Excel 2013, и это сработало.Очевидно, Excel использует региональный параметр Windows 7 для разделителя по умолчанию для CSV (который по-видимому является вкладкой по умолчанию). Вы можете изменить эту настройку следующим образом .
Тем не менее, я не знаю, каковы будут другие последствия. Я полагаю, что если все файлы CSV на вашем компьютере разделены запятыми, то проблем быть не должно. Но если у вас есть другая (скорее всего, Microsoft) программа, которая также использует файлы CSV и обычно использует файлы CSV, отформатированные с другим разделителем, то это может быть проблематично.
Например, если Outlook использует, например, разделенные табуляцией CSV для импорта / экспорта контактов, и вы получаете такие CSV из внешнего источника (Gmail, другой компьютер и т. Д.), То изменение этого общесистемного параметра может помешать открытию Outlook эти разделенные табуляцией CSV.
Я попытался изменить «региональные и языковые настройки»> «Форматы»> «Дополнительные настройки»> «Разделитель списков», однако это не повлияло на открытие файлов CVS в Excel. Так что, к сожалению, это не решение проблемы. То же самое и с Excel 2010 и Windows 7. Это было уже, в моих настройках, и Excel по-прежнему по умолчанию на вкладке. Любая идея, если мы можем ввести 2 разных символа в этом поле на панели управления? Например, ;, разрешить оба символа в качестве списка разделителей?LibreOffice Calc имеет очень продвинутый CSV-фильтр, который позволяет выбирать разделители, форматы и кодировки. Я интенсивно работаю с данными в различных форматах, и очень часто мне нужно отправлять эти данные в формате Excel пользователям. Я использую LibreOffice Calc для преобразования csv-файлов в нужный формат, а затем сохраняю как xls.
Это может не отвечать на вопрос, как это легко сделать в Excel, но отвечает на вопрос, как это сделать легко, тем более, что OP в комментариях предлагает использовать что-то отличное от Excel, что будет приемлемым вариантом.
Excel 2010 - Помимо проверки расширенных региональных настроек, обязательно проверьте дополнительные настройки Excel:
Читайте также: