Csv какой разделитель

Язвы и грабли CSV и Excel: проблемы и решения

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». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.

И вот что случаеся, если мы открываем этот файлик в экселе:

выбрать кодировку. Как правило, важно UTF-8, UTF-16, Windows-1251, KOI8-R. Чаще всего, других вариантов нет. Одна из них должна идти по умолчанию. В случае, если данные содержат символы, не имеющие аналогов в целевой кодировке, нужно предупреждать пользователя, что данные будут битые;
выбрать разделитель между полями. Варианты — табуляция, запятая, точка с запятой. По умолчанию — точка с запятой. Не забыть, что если разделитель вводится в тексте, то будет очень непросто ввести туда табуляцию, это еще и непечатный символ;
выбрать разделитель между строками (CRLF 0×0D 0×0A или CR 0×0D);
выбрать разделитель целой и дробной части для числовых данных (точка или запятая).
выбрать, выводить ли строку заголовка;
выбрать, каким образом осуществлять квотинг спецсимволов (особенно переводов строк и кавычек). В принципе, можно отступиться от стандарта и квотировать их как \n и \», но нужно в этом случае не забыть квотировать сами \n, если они встретятся и не забыть сделать это опцией при экспорте-импорте. Но совместимость пойдет лесом, потому что любой RFC-стандартный парсер конструкцию . «abc\«»,… посчитает за ошибку;
совсем в идеале — поставить галочку «для Excel» и учитывать там те нестандартности, которые внесла Майкрософт. К примеру, заменять значения числовых полей, «похожих на дату», на конструкцию v-portal» style=»display:none;»>

CSV с запятой или точкой с запятой?

Как файл CSV построен в целом? Запятыми или точками с запятой? Есть советы, какой из них использовать?

10 ответов

в Windows это зависит от» региональные и языковые параметры » настроить экран, где вы найдете разделитель списка. Это char Windows приложения ожидают, чтобы быть разделителем CSV.

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

Если вы не написание программы для Windows, которая потребует импорта CSV в других приложениях, и вы знаете, что разделитель списка для ваших целевых компьютеров — , , тогда вперед, иначе я предпочитаю ; поскольку это вызывает меньше проблем с десятичными точками, группировка цифр и не отображается в большом тексте.

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

CSV-это стандартный формат, описанный в RFC 4180 (в 2005 году), поэтому нет недостатка в стандарте. https://www.ietf.org/rfc/rfc4180.txt

и даже до этого C в CSV всегда означал запятую, а не точку с запятой : (

жаль, что Microsoft продолжает игнорировать это и по-прежнему придерживается чудовищности, в которую они превратили его десятилетия назад (да, я признаю, что это было до создания RFC).

одна запись на строку, если a новая строка возникает в кавычках (см. ниже).
запятая как разделитель столбцов. Никогда точка с запятой.
период как десятичная точка в числах. Ни одной запятой.
текст, содержащий запятые, точки и/или строки заключены в «двойные кавычки».

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

1, «этот текст содержит «»кавычки»»», 3

1, этот текст содержит «кавычки», 3

стандарт не охватывает значения даты и времени, лично я стараюсь придерживаться формата ISO 8601, чтобы избежать путаницы день/месяц/год-месяц/день/год.

также актуально, но специально для excel, посмотрите на ответ и этот это предполагает, вставив строку в начале CSV с

сообщить excel, какой разделитель ожидать

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

CSV-это разделенный запятыми файл. Обычно разделителем является запятая, но я видел много других символов, используемых в качестве разделителей. Они просто не так часто используется.

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

1.> Изменить формат файла на .CSV (точка с запятой)

для достижения желаемого результата нам необходимо временно изменить delimiter настройка в Excel варианты:

снимите флажок» Использовать системные разделители «и поставьте запятую в поле» десятичный разделитель».

теперь сохраните файл в папке .CSV формат, и он будет сохранен в формате с запятой.

чтобы изменить запятую на точку с запятой в качестве разделителя Excel по умолчанию для CSV-перейдите в регион — > дополнительные настройки — > вкладка номера — > разделитель списка и введите ; вместо значения по умолчанию

ну, чтобы просто сказать о точке с запятой. Во многих странах запятая-это то, что используется для десятичного периода. В основном колонии ЕС, которые состоят из половины мира, другая половина следуют британскому стандарту (как, черт возьми, Великобритания так велика O_O), поэтому, в свою очередь, используют запятую для базы данных, которые включают номер, создают большую часть головной боли, потому что Excel отказывается признать его в качестве разделителя.

как мудрый в моей стране, Вьетнам, следовать стандартом Франции, нашим стандартом Великобритании пользы Гонконга партнера так запятая делает CSV непригодным, и мы используем \t или ; вместо этого для международного использования, но он все еще не «стандарт» для документа CSV.

Импорт содержимого CSV-файла в Excel

CSV – популярное расширение файлов, которые используются, в основном, для обмена данными между различными компьютерными программами. Чаще всего необходимости в открытии и редактировании таких документов нет. Однако в некоторых случаях перед пользователями может встать такая задача. Программа Excel позволяет это сделать, но в отличие от стандартных файлов в формате XLS и XLSX, простое открытие документа двойным щелчком мыши не всегда дает качественный результат, что может выражаться в некорректном отображении информации. Давайте посмотрим, каким образом можно открыть файлы с расширением CSV в Экселе.

Открываем CSV-файлы
- Метод 1: двойным щелчком или через контекстное меню
- Метод 2: применяем Мастер текстов
- Метод 3: через меню “Файл”
Открываем CSV-файлы

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

CSV – аббревиатура, которая расшифровывается как “Comma-Separated Values” (на русском языке означает “значения, разделенные запятыми”).

Как следует из названия, в таких документах используются разделители:
- запятая – в англоязычных версиях;
- точка с запятой – в русскоязычных версиях программы.
Во время открытия документа в Excel основная задача (проблема) заключается в выборе способа кодировки, примененного при сохранении файла. Если будет выбрана не та кодировка, скорее всего, пользователь увидит множество нечитаемых символов, и полезность информации будет сведена к минимуму. Помимо этого, ключевое значение имеет используемый разделитель. Например, если документ был сохранен в англоязычной версии, а затем его пытаются открыть в русскоязычной, скорее всего, качество отображаемой информации пострадает. Причина, как мы ранее отметили, заключается в том, что в разных версиях используются разные разделители. Давайте посмотрим, как избежать этих проблем и как правильно открывать файлы CSV.

Метод 1: двойным щелчком или через контекстное меню

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

Excel установлена как программа по умолчанию для открытия CSV-файлов

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

Для открытия CSV-фалов назначена другая программа или не назначена вовсе

Алгоритм действия в таких ситуациях следующий (на примере Windows 10):
Независимо от того, какой из описанных выше способов был выбран, результатом будет открытие CSV-файла. Как мы упомянули выше, корректно отображаться содержимое будет только при соответствии кодировки и разделителей.

В остальных случаях может показываться нечто подобное:

Поэтому описанный метод подходит не всегда, и мы переходим к следующим.

Метод 2: применяем Мастер текстов

Воспользуемся интегрированным в программу инструментом – Мастером текстов:
- в первом случае следует указать адрес ячейки (или оставить значение по умолчанию), которая будет являться самым верхним левым элементом импортируемого содержимого. Сделать это можно вручную, прописав координаты с помощью клавиатуры, или просто щелкнув по нужной ячейке на листе (курсор при этом должен находится в соответствующем поле для ввода информации).
- при выборе варианта импорта на новом листе координаты указывать не нужно.
Метод 3: через меню “Файл”

И последний метод, которым можно воспользоваться заключается в следующем:
1. Запустив программу выбираем пункт “Отрыть”.Если программа уже ранее была открыта и ведется работа на определенном листе, переходим в меню “Файл”.Щелкаем по команде “Открыть” в списк команд.
2. Жмем кнопку “Обзор”, чтобы перейти к окну Проводника.
3. Выбираем формат “Все файлы”, переходим к месту хранения нашего документа, отмечаем его и щелкаем кнопку “Открыть”.
4. На экране появится уже знакомый нам Мастер импорта текстов. Далее руководствуемся шагами, описанными в Методе 2.
Заключение

Таким образом, несмотря на кажущуюся сложность, программа Эксель вполне позволяет открывать и работать с файлами в формате CSV. Главное – определиться с методом реализации. Если при обычном открытии документа (двойным щелчком мыши или через контекстное меню) его содержимое содержит непонятные символы, можно воспользоваться Мастером текста, который позволяет выбрать подходящую кодировку и знак разделителя, что напрямую влияет на корректность отображаемой информации.
Похожие публикации: