Компания
546,49
рейтинг
30 сентября 2011 в 11:47

Разное → Язвы и грабли 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 2005-го года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.

Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:

  • между строками — перевод строки CRLF [на мой взгляд, им не стоило ограничивать двумя байтами, т.е. как CRLF (0×0D, 0×0A), так и CR 0×0D]
  • разделители — запятые, в конце строки не должно быть запятой,
  • в последней строке CRLF не обязателен,
  • первая строка может быть строкой заголовка (никак не помечается при этом)
  • пробелы, окружающие запятую-разделитель, игнорируются.
  • если значение содержит в себе CRLF, CR, LF (символы-разделители строк), двойную кавычку или запятую (символ-разделитель полей), то заключение значения в кавычки обязательно. В противном случае — допустимо.
  • т.е. допустимы переводы строк внутри поля. Но такие значения полей должны быть обязательно закавычены,
  • если внутри закавыченной части встречаются двойные кавычки, то используется специфический квотинг кавычек в CSV — их дублирование.


Вот в нотации ABNF описание формата:

 file = [header CRLF] record *(CRLF record) [CRLF]
   header = name *(COMMA name)
   record = field *(COMMA field)
   name = field
   field = (escaped / non-escaped)
   escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE
   non-escaped = *TEXTDATA
   COMMA = %x2C
   DQUOTE =  %x22
   LF = %x0A
   CRLF = CR LF
   TEXTDATA =  %x20-21 / %x23-2B / %x2D-7E
 


Также при реализации формата нужно помнить, что поскольку здесь нет указателей на число и тип колонок, поскольку нет требования обязательно размещать заголовок, здесь есть условности, о которых необходимо не забывать:

  • строковое значение из цифр, не заключенное в кавычки может быть воспринято программой как числовое, из-за чего может быть потеряна информация, например, лидирующие нули,
  • количество значений в каждой строке может отличаться и необходимо правильно обрабатывать эту ситуацию. В одних ситуациях нужно предупредить пользователя, в других — создавать дополнительные колонки и заполнять их пустыми значениями. Можно определиться, что количество колонок задается заголовком, а можно добавлять их динамически, по мере импорта CSV,
  • Квотить кавычки через «слэш» не по стандарту, делать так не надо.
  • Поскольку типизации полей нет, нет и требования к ним. Разделители целой и дробной частей в разных странах разные, и это приводит к тому, что один и тот же CSV, сгенрированный приложением, в одном экселе «понимается», в другом — нет. Потому что Microsoft Office ориентируется на региональные настройки Windows, а там может быть что угодно. В России там указано, что разделитель — запятая,
  • Если CSV открывать не через меню «Данные», а напрямую, то Excel лишних вопросов не задает, и делает как ему кажется правильным. Например, поле со значением 1.24 он понимает по умолчанию как «24 января»
  • Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
  • У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.


Пример валидного CSV, который можно использовать для тестов:

Фамилия, Имя, Адрес, Город/штат, индекс, просто строка
Иванов,Иван, Ленина 20, Москва, 08075, "1/3"
Tyler, John,110 terrace, PA,20121, "1.24"
"Петров 
""Кул""", Петя,120 Hambling St., NJ,08075, "1,24"
Смирнов,Вася,"7452 Street ""Near the Square"" road", York, 91234, "3-01"
,Миша,,Ленинград, 00123, "03-01"
"Джон ""Черная голова"", Клод",Рок,"", Маями бич,00111, "0000"
Сергей,,


точно такой же SCSV:

Фамилия; Имя; Адрес;  Город/штат; индекс; просто строка
Иванов;Иван; Ленина 20; Москва; 08075;"1/3"
Tyler; John;110 terrace; PA; 20121;"1.24"
"Петров 
""Кул"""; Петя;120 Hambling St.; NJ;08075;"1,24"
Смирнов;Вася;"7452 Street ""Near the Square"" road"; York; 91234;"3-01"
;Миша;;Ленинград; 00123;"03-01"
"Джон ""Черная голова""; Клод";Рок;""; Маями бич;00111; "0000"
Сергей;;


Первый файлик, который реально COMMA-SEPARATED, будучи сохраненным в .csv, Excel-ом не воспринимается вообще.



Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:



Ошибки Excel-я при импорте:
  1. Учлись пробелы, окружающие разделители
  2. Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
  3. В поле индекс Excel «опустил» ведущие нули.
  4. в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол


Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:



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

Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.

Фамилия;Имя;Адрес;Город/штат;индекс;просто строка
Иванов;Иван;Ленина 20;Москва;="08075";="1/3"
Tyler; John;110 terrace;PA;="20121";="1.24"
"Петров 
""Кул""";Петя;120 Hambling St.;NJ;="08075";="1,24"
Смирнов;Вася;"7452 Street ""Near the Square"" road";York;="91234";="3-01"
;Миша;;Ленинград;="00123";="03-01"
"Джон ""Черная голова"";Клод";Рок;"";Маями бич;="00111";="0000"
Сергей;;


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



Резюмирую.

Чтобы сгенерировать такой CSV, которым можно было бы пользоваться, пользователю нужно дать возможность сделать следующие настройки перед экспортом:
  1. выбрать кодировку. Как правило, важно UTF-8, UTF-16, Windows-1251, KOI8-R. Чаще всего, других вариантов нет. Одна из них должна идти по умолчанию. В случае, если данные содержат символы, не имеющие аналогов в целевой кодировке, нужно предупреждать пользователя, что данные будут битые;
  2. выбрать разделитель между полями. Варианты — табуляция, запятая, точка с запятой. По умолчанию — точка с запятой. Не забыть, что если разделитель вводится в тексте, то будет очень непросто ввести туда табуляцию, это еще и непечатный символ;
  3. выбрать разделитель между строками (CRLF 0×0D 0×0A или CR 0×0D);
  4. выбрать разделитель целой и дробной части для числовых данных (точка или запятая).
  5. выбрать, выводить ли строку заголовка;
  6. выбрать, каким образом осуществлять квотинг спецсимволов (особенно переводов строк и кавычек). В принципе, можно отступиться от стандарта и квотировать их как \n и \", но нужно в этом случае не забыть квотировать сами \n, если они встретятся и не забыть сделать это опцией при экспорте-импорте. Но совместимость пойдет лесом, потому что любой RFC-стандартный парсер конструкцию ...,"abc\«",… посчитает за ошибку;
  7. совсем в идеале — поставить галочку «для Excel» и учитывать там те нестандартности, которые внесла Майкрософт. К примеру, заменять значения числовых полей, «похожих на дату», на конструкцию ="<значение поля>«.
  8. определиться, нужно ли оставлять «хвост» из пустых разделителей, если он образуется. Например, из 20 полей только первое содержит данные, а остальные пустые. В итоге, в строке можно либо ставить после первого 19 разделителей, либо не ставить. Для больших объемов данных это может спасти миллисекунды обработки и уменьшить размер файла.


Чтобы построить хороший и удобный импортер CSV, необходимо помнить о следующем:

  1. разбор файла нужно делать по лексемам в соответствии с грамматикой выше или пользоваться хорошо зарекомендовавшими себя готовыми библиотеками (Excel работает иначе, потому с импортом проблема);
  2. предоставлять пользователю возможность выбрать кодировку (топ 4 достаточно);
  3. предоставлять пользователю возможность выбрать разделитель между полями (запятая, табуляция, точка с запятой достаточно);
  4. предоставлять пользователю возможность выбрать разделитель между строками, но кроме вариантов CR и CRLF нужно предусмотреть «CR или CRLF». Это связано с тем, что, например, Excel при экспорте таблицы с переводами строк внутри ячеек экспортирует эти переводы строк как CR, а остальные строки разделяет CRLF. При этом при импорте файла ему все равно, CR там или CRLF;
  5. предоставлять пользователю возможность выбрать разделитель между целой и дробной частей (запятая или точка);
  6. определиться с методом разбора — сначала читаем все в память, потом обрабатываем или обрабатываем строку за строкой. В первом случае может понадобиться больше памяти, во втором случае — ошибка в середине вызовет только частичный импорт, что может вызвать проблемы. Предпочительнее первый вариант.


Рауф Алиев,
заместитель технического директора Mail.Ru Group
Автор: @raliev

Комментарии (42)

  • 0
    забавно, значит мне везло и проблем не было :)
    • +4
      Так тут специальный пример на проверку соответствия стандарту по RFC, поэтому и проблемы. Микрософт даже свой собственный OpenXML не соблюдает, а тут всего лишь какой-то RFC.
      • 0
        RFC носит лишь рекомендательный характер.
        если строго следовать RFC, то никакие «неанглийские» символы не могут присутствовать в файле:

        TEXTDATA = %x20-21 / %x23-2B / %x2D-7E
  • –1
    В TAoUP есть глава про CSV. Там вывод, что все плохо и по-хорошему оставить нужно только 2 специальных знака — разделитель полей и квотирование (следующий символ оставлять как есть).
    • 0
      А разделитель строк?
      • –1
        \n или \r\n как и все остальное на платформе (к кодировке тоже относится).
        Вообще, удивительно, читаем там же:
        Microsoft имеет несовместимые версии CSV-файлов между собственными приложениями, а в некоторых случаях между различными версиями одного приложения (очевидный пример — программа Excel).
        • –1
          Ну что за привычка отвечать на вопрос вне контекста? :(

          Вы пишете: «вывод, что все плохо и по-хорошему оставить нужно только 2 специальных знака — разделитель полей и квотирование».

          Я спрашиваю: «а разделитель строк?»
          • –1
            www.faqs.org/docs/artu/ch05s02.html

            Дословно: «one record per line», т.е. да, разделитель записей тоже нужен, но как разделять строки все же не наше дело.
  • 0
    Для преобразования «автопреобразованных» колонок назад помогает указать формат ячейки «Дополнительный». По крайней мере для «длинных» чисел работает.
  • –1
    «CSV является стандартом де-факто» и «стандарта CSV как такового, к сожалению, нет» — как-то грустно вместе звучит. Избегаю CSV везде, где могу.
    • +1
      К сожалению, не всегда это возможно. Многие Прикладные программы выгружают данные именно в этом формате. Всякие там банк-клиенты и прочие. :(
      • +2
        Почему «Прикладные» с большой буквы — не знаю, само получилось.
      • 0
        Слышал, Apple отчёты о продажах присылает в tsv. Их очень удобно AWK'ом обрабатывать.
  • +2
    Получилась статья не про CSV, а про то, какие плохие продукты Microsoft. Но ведь можно ими просто не пользоваться! Им есть менее кривые (хотя бы в деле работы с CSV) аналоги.
    • 0
      ага, Excel прайсы лучше всего переводить в csv OOo. Тогда просто пропадают многие проблемы. Есть sdk, благодаря, которому это все автоматизируется (не только в windows). Остается только решить мелкие проблемы типа разделение цифр запятой, лишние пробельные символы (причем разные). Ну и экранирование что бы пихать в БД
    • +2
      На мой взгляд офис для виндов майкрософта — это шедевр, который еще никто не повторил. Старофисы и опенофисы, как и маковский воркс — это все сильно слабее, даже не функционально, а вкупе с удобством, в деталях… Просто у него есть минусы и нужно понимать, как с ними жить. Раз приходится с ними жить.
      • +1
        Собственно косяки с csv вынудили держать вместе с MS Office еще и ООо. Для таблиц всегда пользуюсь calc (хватает для всех обычных задач, включая анализы и графики для отчетов), а вот word пока существенно лучше работает чем writer.
  • 0
    Хех, OpenOffice.org и тут рулит: при импорте/экспорте можно явно задать настройки кодировки, разделителей и экранирования, а в MS Office разделитель запросто может взяться из локали, сколько бубнов уже порвали на эту тему :(
    • –1
      Это все просто от незнания продукта :) В Office это тоже есть :)
      • 0
        Это не очень помогает, когда CSV-файл генерируется сайтом, а клиенты с MS Office'ом находятся в разных странах…
        • –1
          что не помогает? при экспорте из текстового файла, мастере выбрать разделитель и тут же в окне предпросмотра понять что вы выбрали неправильный разделитель?
          • 0
            Ничего, что я сейчас про импорт?
            • 0
              Сори, я оговорился — при ИМПОРТЕ из текстового файла. Ведь Сайт экспортирует файл CSV, а потом клиент в MS Office Excel ИМПОРТирует его через мастер. В мастере при импорте можно сразу до открытия файла выбрать разделитель полей, и увидеть в предпросмотре результат импорта при указанном разделителе.
              • 0
                Понятно. Теперь объяснить бы то же самое современному пользователю. Бизнес-пользователю. Они выше всего этого :(
                • 0
                  Ну вообще решения тут как минимум два. С одной стороны при выгрузке можно посмотреть User Agent и выбрать соответствующий разделитель. С другой стороны, на странице загрузки или в файле архиве вложить инструкцию по экспорту.

                  Но к слову, если ваши клиенты вче равно читают файл в Excel выгружайте им в Excel (HTML + правильные свойства у ячеек в таблице)
  • 0
    Не подскажете название продукта (а такой есть), который позволяет понизить «безопасность» Outlook путем разрешения макросам доступа к контактам и отсылке писем. Можно было бы тогда написать «костыль» для переброски контактов туда-сюда (в Excel «причесывать» контакты сподручнее…
  • –2
    Вообще что бы не устраивать танцев с бубнами при импорте CSV файла в Excel линейке инструментов, есть таб «Данные» (Data), и в ней кнопка — «из текстового файла» (From text). При этом поднимается Мастер который по шагам вам поможет импортировать файл. Где в том числе спрашивается какой знак является разделителем. Все остальное — от не знания :)
    • +3
      Если вы немножко внимательнее посмотрите на статью, там про это написано. И написано, какая недоработка есть в этом импорте. Например, он не понимает переводов строк внутри полей. Проблема с ведущими нулями и кавычками также остается.
      • 0
        Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.



        Ошибки Excel-я при импорте:
        1.Учлись пробелы, окружающие разделители
        2.Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
        3.В поле индекс Excel «опустил» ведущие нули.
        4.в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол

        Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:


        и дальше идет текст где вы называете все это "импорт". Когда вы все это разобрали, вы потом вспоминаете что еще есть «еще один способ» которые тоже назывется импортом. Как то все в кучу смешано, вы не находите?
        • 0
          Стас, хватит издеваться, я тебя узнал :)
          • +1
            Да я и не прятался.
  • +3
    отдел Системной интеграции нашей компании разделяет вашу боль :)
  • 0
    В англоязычной версии Excel разделителями являются именно запятые.
    • 0
      Дело же не в версии Excel, а в региональных настройках в вашей ОС
  • 0
    Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.
    ходят слухи, что ms office понимает csv в utf-8 когда в начале файла есть нужный BOM (так-ли это на самом деле не могу проверить за неимением сего чуда).
  • –1
    забавные вещи эксплуатируют свободные память и другие ресурсы зам-CTO Mail.ru Group )
  • +1
    Спасибо за решение проблемы с 11-12-ти значными числами…
    Excel превращал число в запись вида 3.1E+11))
    Теперь собственно пишу так: =«12312313313»;=«123123434233434»;…
    • 0
      Ещё способы обхода этой проблемы: 1) апостроф перед числом 2) пометить (заранее) формат ячеек как «текст».
      При импорте из файла, кстати, тоже полезно бывает импортируемые колонки поментить как «текст», чтобы строка типа «01.01» не превращалась в «1 января» :)
  • 0
    Office 2013, проблемы так и остались. Где написано «разделители — запятые» будет точка с запятой, двойной клик по CSV откроет его в Excel, не разделяя на колонки и ни о чём не спрашивая.
    • 0
      Классический workaround — изменить расширение файла в ".txt", тогда поднимается полноценный визард.
      Но то, что до сих пор сделали по уму — это очень странно, мягко говоря :)
  • 0
    Еще несколько хинтов, коллеги:

    1) Чтобы Excel начал спрашивать о параметрах CSV — переименуйте предварительно файл с расширением .txt

    2) При импорте из Excel в CSV — если вам нужны разделители в качестве ",", а не ";" как это делается по-умолчанию — то нужно временно изменить региональные настройки:
    а) В операционной системе Microsoft Windows нажмите кнопку Пуск и выберите пункт Панель управления.
    б) Откройте диалоговое окно изменения региональных и языковых параметров.
    в) Введите новый разделитель в поле Разделитель элементов списка. Кстати в этом же окне можно изменить и дробную часть с запятой на точку.
    г) Дважды нажмите кнопку ОК.
    Теперь при импортировании будут учитываться новые значения разделителей.
    • 0
      Спасибо!

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.

Самое читаемое Разное