Чем 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». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.
И вот что случаеся, если мы открываем этот файлик в экселе:
CSV и Excel или xls — это два разных типа расширений файлов, которые оба содержат данные, разница между ними заключается в том, что в CSV или значениях, разделенных запятыми, данные находятся в текстовом формате, разделенном запятыми, в то время как в Excel или Xls данные находятся в табличный формат, или мы говорим, что в строках и столбцах и в расширении файла CSV нет форматирования данных, тогда как в Excel мы можем форматировать данные в соответствии с нашими требованиями.
CSV и Excel — это два формата, которые разработаны для хранения данных в базе данных и помогают бизнес-организации вести свой бизнес.
Что такое CSV (значения, разделенные запятыми)?
CSV — это формат текстового файла, в котором значения разделяются запятыми, и, соответственно, данные будут храниться целиком. Данные CSV можно легко открыть в различных текстовых редакторах, таких как блокнот, и проанализировать их для извлечения и анализа необходимых деталей.
CSV-файл в Блокноте
Что такое Excel?
В нынешнюю эпоху любой корпоративный профессионал вряд ли сможет выжить без превосходства, поскольку Excel позволяет им хранить, обрабатывать, анализировать и экспортировать данные в нужном порядке. Это хорошо структурированный и организованный формат файла, который специально разработан для табличных данных и для сопоставления деталей из различных независимых таблиц.
Данные Excel в Microsoft Excel
CSV против инфографики Excel
Ключевые различия между CSV и Excel
Ключевые отличия заключаются в следующем:
- Полная форма CSV — это значения, разделенные запятыми, а MS Excel — это Microsoft Excel.
- Расширение файла CSV — «.csv», а расширение файла Excel — «.xls / .xlsx».
- В файле CSV, поскольку все данные необходимо сохранить в текстовом формате, данные, относящиеся к изображениям (JPEG, PNG, JPG и т. Д.), Сохранить невозможно. Хотя Excel является двоичным форматом, поэтому все данные, связанные с изображениями, можно легко сохранить в формате Excel.
- CSV — это простой текстовый файл, следовательно, это обычный файл без какой-либо стандартизации или структурирования. В то время как Excel очень стандартизирован и структурирован с учетом требований современного корпоративного мира.
- Файлы CSV можно открывать в любом текстовом редакторе, например в блокноте, а также в MS Excel, в то время как Excel можно открывать только в MS Excel или таблицах Google.
- CSV-файл представляет собой простой текстовый файл, поэтому все данные будут храниться с использованием запятой в качестве разделителя между двумя блоками данных; следовательно, диаграммы невозможно сохранить в формате CSV. В то время как Excel сохраняет данные в двоичной форме; поэтому все данные, относящиеся к диаграммам, можно сохранить в формате Excel.
- Файлы CSV не могут быть напрямую связаны с внешними источниками, в то время как файл Excel можно легко интегрировать с внешними источниками таким образом, что ввод может поступать из внешних источников, а извлечение данных также может быть напрямую связано с внешними источниками.
- Анализ и обработка данных могут быть выполнены эффективно и структурированно в Excel, в то время как то же самое невозможно сделать в формате CSV, поскольку данные в таком формате не могут быть связаны с другими данными.
- Хранить файлы CSV очень легко, поскольку их размер всегда будет меньше, в то время как файлы Excel с обширной базой данных очень сложно хранить и поддерживать, поскольку существует высокая вероятность повреждения или сбоя.
- Файлы CSV в основном используются профессионалами в области анализа или визуализации данных, в то время как Excel используется неспециалистами, а также профессионалами в зависимости от их требований и целей работы.
Сравнительная таблица
Основа | CSV | MS Excel |
Полная форма | Полная форма CSV — это значения, разделенные запятыми. | Полная форма MS Excel — это Microsoft Excel. |
Расширение | CSV-файл с расширением .csv. | Файл Excel с расширением, обозначенным как .xls / .xlsx |
Запущен в | Формат CSV выпущен в 2005 г. | MS Excel выпущен в 1987 г. |
Сохранение данных таблицы | Поскольку CSV сохраняет данные в текстовом формате, сохранить данные, связанные с изображениями, невозможно. | Поскольку Excel сохраняет данные в формате двоичного файла, данные изображений могут быть легко сохранены в том же формате. |
Стандартизация | CSV — это просто текстовый файл; следовательно, он не стандартизирован. | Excel очень стандартизирован в отношении хранения данных и связанных с ним операций. |
вид | CSV — это формат, в котором хранятся данные | MS Excel — это инструмент, в котором данные сохраняются, и может быть проведен анализ данных. |
Платформа | Файлы CSV можно открывать в различных текстовых редакторах, а также в Excel. | Файлы Excel можно открывать только в MS excel. |
Диаграммы и изображения | Поскольку он сохраняет данные в текстовом формате, он не может сохранять данные, как диаграмма. | MS Excel может легко сохранять данные в виде диаграмм. |
Связь с внешними источниками | Для файлов CSV невозможна какая-либо связь с внешними данными и обновление данных. | Файлы MS Excel могут быть связаны с внешними источниками данных, и могут быть включены надстройки. |
Манипуляции | Файлы CSV не допускают никаких манипуляций с данными. | MS Excel позволяет выполнять любые операции с данными и анализировать связанные с ними данные. |
Место хранения | CSV-файл требует меньше места и может храниться в малом объеме памяти. | Для файла Excel требуется больше места для хранения и большой объем памяти. |
Применение | Основное применение в анализе и визуализации данных. | Excel можно использовать в повседневных операциях до принятия сложных организационных решений. |
Использован | Большинство — профессионалы. | Используется непрофессионалами, а также профессионалами в зависимости от их требований. |
Заключение
CSV и Excel — это своеобразные форматы для хранения данных, которые широко используются разными людьми. У обоих есть свои плюсы и минусы. Но оба могут быть разумно использованы в настоящее время для загрузки данных, визуализации данных, анализа данных и манипулирования.
Привет, Хабр! Эта статья про плагин Rainbow CSV, который я написал для 5 текстовых редакторов:
Синтаксис для такой подсветки, как ни странно, задается с помощью всего лишь одной (хоть и длинной) строчки-регулярного выражения:
Правило подсветки целиком можно найти, например, здесь (версия для VS Code), но, кроме самого регулярного выражения, там совершенно не на что смотреть.
Для сравнения, синтаксис файлы для языков общего назначения, таких как Python, JS, C++ и т.д. занимают обычно несколько сотен строчек весьма эзотерического кода.
Чтобы не загружать деталями статью, угадать из каких основных частей состоит и как работает это регулярное выражение предлагается читателям.
Подсказка: Вот такое простое выражение ([^,]*,)?([^,]*,)? — подсветит CSV файл в 2 разных чередующихся цвета, но будет неправильно работать на запятых внутри полей, экранированных кавычками.
Кстати, здесь и далее описывается версия Rainbow CSV для Visual Studio Code, т.к. это вариант плагина в данный момент самый технически продвинутый и популярный (больше 500K загрузок).
Итак, помимо того, что Rainbow CSV подсвечивает столбцы, он также может:
- Сказать на какую колонку в данный момент указывает курсор: номер колонки + название из первой строчки-заголовка. Если в начале файла строчки-заголовка нет (сразу идут данные), то пользователь может задать свой "Виртуальный" заголовок.
- Автоматически проверить файл на предмет разного числа записей в строке или неправильного использования символов экранирования — "CSV Lint".
- Выполнить SQL-like запрос с помощью встроенного в плагин интерпретатора RBQL, который позволяет применять очень широкий класс текстовых преобразований к входной таблице.
RBQL поддерживает почти все операторы SQL (SELECT, UPDATE, WHERE, ORDER BY, TOP/LIMIT, JOIN, GROUP BY) а также все стандартные функции и операторы из JavaScript и Python.
RBQL это отдельная технология, но она очень удачно вписывается в концепцию Rainbow CSV, и поэтому такая интеграция дает много преимуществ.
Одним из важнейших свойств Rainbow CSV плагинов является автоматическое обнаружение CSV файлов по их контенту. Эта функциональность крайне необходима, т.к. зачастую CSV (или TSV) файлы имеют файловое расширение отличное от .csv (.tsv). Также можно встретить файлы с расширением .csv в которых реально в качестве разделителя используется точка с запятой ; . Алгоритм определения табличного файла по контенту очень прост — достаточно проверить, что количество ячеек в каждой строчке при split'e по данному разделителю это постоянная > 1.
Сравнение Rainbow CSV с графическим выравниванием
Вообще, традиционный способ просмотра CSV данных это импорт их в какой-либо графический редактор, например Excel.
По сравнению с этим способом, у Rainbow CSV есть как преимущества, так и недостатки:
Преимущества:
- What You See Is What You Get — можно быть уверенным в том, что то, что видно на экране — это реальное содержимое файла.
- Знакомое окружение любимого текстового редактора
- Zero-cost abstraction: Синтаксическая подсветка очень "дешева" с вычислительной точки зрения по сравнению с графическим выравниванием.
- Более высокая плотность информации: На один экран влезает больше данных — графическое выравнивание "съедает" много места за счет выравнивающих пробелов.
- Возможность визуально связать одну колонку (подсвеченную одним цветом) из разных окон
Недостатки:
- В стандартной реализации используется 10 разных цветов, поэтому когда число колонок больше 10, цвета начинают повторяться и эффективность цветового кодирования колонок снижается.
- Отсутствует поддержка переноса строк в ячейках, заэкранированных двойными кавычками. Здесь можно почитать подробности этой проблемы. Впрочем, я считаю, что CSV с переносом строки внутри ячеек это крайне непрактичный формат.
Сравнение с текстовым выравниванием
Еще один способ повысить читаемость CSV файлов, это выравнивание с помощью пробелов, но данный способ модифицирует содержимое файла, и поэтому его применимость весьма ограниченна.
Так же, на мой взгляд, читаемость файла после синтаксической Rainbow подсветки лучше, чем у файла, который был выравнен пробелами.
Немного о проекте
Первая версия Rainbow CSV была написана 5 лет назад для Vim на основе плагина rainbow_parentheses Как можно заметить, от этого проекта я позаимствовал не только часть кода, но и половину названия =)
Версии для VSCode, Atom, и Sublime Text 3 появились год назад.
Многие критически важные фичи и улучшения были предложены пользователями плагина.
Сравнение процесса разработки плагина для разных редакторов
В заключение могу провести небольшое сравнение API популярных текстовых редакторов.
API для плагинов у VSCode, Atom и Sublime Text 3 довольно похожи между собой, основное различие в том, что расширения для VS Code и Atom пишутся на JavaScript, а для Sublime Text 3 на Python.
Все 3 редактора используют один и тот же движок регулярных выражений для синтаксической подсветки, поэтому перенос Rainbow CSV между этими редакторами потребовал лишь минимальной адаптации регулярок.
В целом могу сказать, что самый приятный и удобный процесс разработки плагинов предоставляет VS Code. С другой стороны именно в нем по каким-то причинам изначально отсутствовала некоторая функциональность, необходимая для полноценной работы Rainbow CSV, но команда VS Code с радостью приняла и улучшила мой PR, который добавлял необходимый мне метод.
Написание плагинов для Vim очень сильно отличается от этих 3 более новых редакторов. В Vim используется свой собственный язык VimScript, а также разнообразные команды для манипуляции содержимым открытых файлов. Синтаксическая модель, которую Vim использует для подсветки, также довольно сильно отличается от того, что предоставляют VSCode, Atom и Sublime.
С момента появления персональных компьютеров он использовался для обработки документов. Обычные документы, такие как буквы, хранились в текстовом формате, который содержал не более чем список символов. Таблицы немного сложнее, потому что значения расположены в табличной форме. CSV (Comma Separated Value) - формат файла, предназначенный для хранения табличных данных. Он использует запятые, чтобы отделить каждую запись в строке и символ новой строки, чтобы перейти к следующей строке. Excel - это последнее приложение электронной таблицы от Microsoft, которое сохраняет данные в собственном формате.
Excel намного лучше по сравнению с CSV, поскольку он способен делать гораздо больше для табличных данных. Форматирование - одна из самых больших функций, которая поставляется с новыми приложениями для электронных таблиц. Вы можете изменять шрифты, цвета и размеры каждой ячейки, чтобы таблица была намного легче читать и более приятна для глаз читателей. У вас также есть возможность встраивать таблицы в файлы Excel, которые дают визуальное представление данных. CSV не имеет положений для сохранения таблицы информации сам по себе.
Преимущество CSV-файлов исходит из его возраста и его широкой совместимости. Вы можете открыть файл CSV практически в любом приложении, даже в текстовом редакторе, поскольку сам формат представляет собой текстовый файл, который использует специальные символы для определения того, где заканчивается одна ячейка, а другая начинается. Благодаря расширенным функциям, которые были добавлены в excel, и большему количеству хранимых данных, которые напрямую не связаны с тем, что хранится в каждой ячейке, сохранение в текстовом файле с разделителями, например CSV, становится утомительным и непрактичным. Файлы Excel следуют своему алгоритму в сохранении данных и больше не читаются в другом приложении, которые его не распознают.
Большинство людей найдут, что Excel достаточно для большинства потребностей в электронных таблицах. Нет необходимости использовать CSV, если у вас нет специализированного приложения, которое незаменимо и не распознает данные в файле Excel.
Резюме: 1.Excel - приложение для электронных таблиц, которое сохраняет файлы в свой собственный формат, в то время как CSV - это стандарт сохранения табличной информации в текстовый файл с разделителями 2.CSV - очень старый метод сохранения таблиц по сравнению с Excel 3.CSV-файлы не могут хранить другую информацию, такую как форматирование, в то время как стандарт поставляется в Excel 4.Файлы, сохраненные в excel, не могут быть открыты или отредактированы текстовыми редакторами, в то время как файлы CSV могут
Продукты HFLabs в промышленных объемах обрабатывают данные: адреса, ФИО, реквизиты компаний и еще вагон всего. Естественно, тестировщики ежедневно с этими данными имеют дело: обновляют тест-кейсы, изучают результаты очистки. Часто заказчики дают «живую» базу, чтобы тестировщик настроил сервис под нее.
Первое, чему мы учим новых QA — сохранять данные в первозданном виде. Все по заветам: «Не навреди». В статье я расскажу, как аккуратно работать с CSV-файлами в Excel и Open Office. Советы помогут ничего не испортить, сохранить информацию после редактирования и в целом чувствовать себя увереннее.
Материал базовый, профессионалы совершенно точно заскучают.
Что такое CSV-файлы
Формат CSV используют, чтобы хранить таблицы в текстовых файлах. Данные очень часто упаковывают именно в таблицы, поэтому CSV-файлы очень популярны.
CSV-файл состоит из строк с данными и разделителей, которые обозначают границы столбцов
CSV расшифровывается как comma-separated values — «значения, разделенные запятыми». Но пусть название вас не обманет: разделителями столбцов в CSV-файле могут служить и точки с запятой, и знаки табуляции. Это все равно будет CSV-файл.
У CSV куча плюсов перед тем же форматом Excel: текстовые файлы просты как пуговица, открываются быстро, читаются на любом устройстве и в любой среде без дополнительных инструментов.
Из-за своих преимуществ CSV — сверхпопулярный формат обмена данными, хотя ему уже лет 40. CSV используют прикладные промышленные программы, в него выгружают данные из баз.
Одна беда — текстового редактора для работы с CSV мало. Еще ничего, если таблица простая: в первом поле ID одной длины, во втором дата одного формата, а в третьем какой-нибудь адрес. Но когда поля разной длины и их больше трех, начинаются мучения.
Следить за разделителями и столбцами — глаза сломаешь
Еще хуже с анализом данных — попробуй «Блокнотом» хотя бы сложить все числа в столбце. Я уж не говорю о красивых графиках.
Поэтому CSV-файлы анализируют и редактируют в Excel и аналогах: Open Office, LibreOffice и прочих.
Ветеранам, которые все же дочитали: ребята, мы знаем об анализе непосредственно в БД c помощью SQL, знаем о Tableau и Talend Open Studio. Это статья для начинающих, а на базовом уровне и небольшом объеме данных Excel с аналогами хватает.
Как Excel портит данные: из классики
Все бы ничего, но Excel, едва открыв CSV-файл, начинает свои лукавые выкрутасы. Он без спроса меняет данные так, что те приходят в негодность. Причем делает это совершенно незаметно. Из-за этого в свое время мы схватили ворох проблем.
Большинство казусов связано с тем, что программа без спроса преобразует строки с набором цифр в числа.
Округляет. Например, в исходной ячейке два телефона хранятся через запятую без пробелов: «5235834,5235835». Что сделает Excel? Лихо превратит номера́ в одно число и округлит до двух цифр после запятой: «5235834,52». Так мы потеряем второй телефон.
Приводит к экспоненциальной форме. Excel заботливо преобразует «123456789012345» в число «1,2E+15». Исходное значение потеряем напрочь.
Проблема актуальна для длинных, символов по пятнадцать, цифровых строк. Например, КЛАДР-кодов (это такой государственный идентификатор адресного объекта: го́рода, у́лицы, до́ма).
Потеря плюса критична, например, если данные пойдут в стороннюю систему, а та при импорте жестко проверяет формат.
Разбивает по три цифры. Цифровую строку длиннее трех символов Excel, добрая душа, аккуратно разберет. Например, «8 495 5235834» превратит в «84 955 235 834».
Форматирование важно как минимум для телефонных номеров: пробелы отделяют коды страны и города от остального номера и друг от друга. Excel запросто нарушает правильное членение телефона.
Удаляет лидирующие нули. Строку «00523446» Excel превратит в «523446».
А в ИНН, например, первые две цифры — это код региона. Для Республики Алтай он начинается с нуля — «04». Без нуля смысл номера исказится, а проверку формата ИНН вообще не пройдет.
Меняет даты под локальные настройки. Excel с удовольствием исправит номер дома «1/2» на «01.фев». Потому что Windows подсказал, что в таком виде вам удобнее считывать даты.
Побеждаем порчу данных правильным импортом
Если серьезно, в бедах виноват не Excel целиком, а неочевидный способ импорта данных в программу.
По умолчанию Excel применяет к данным в загруженном CSV-файле тип «General» — общий. Из-за него программа распознает цифровые строки как числа. Такой порядок можно победить, используя встроенный инструмент импорта.
Запускаю встроенный в Excel механизм импорта. В меню это «Data → Get External Data → From Text».
Выбираю CSV-файл с данными, открывается диалог. В диалоге кликаю на тип файла Delimited (с разделителями). Кодировка — та, что в файле, обычно определяется автоматом. Если первая строка файла — шапка, отмечаю «My Data Has Headers».
Перехожу ко второму шагу диалога. Выбираю разделитель полей (обычно это точка с запятой — semicolon). Отключаю «Treat consecutive delimiters as one», а «Text qualifier» выставляю в «». (Text qualifier — это символ начала и конца текста. Если разделитель в CSV — запятая, то text qualifier нужен, чтобы отличать запятые внутри текста от запятых-разделителей.)
На третьем шаге выбираю формат полей, ради него все и затевалось. Для всех столбцов выставляю тип «Text». Кстати, если кликнуть на первую колонку, зажать шифт и кликнуть на последнюю, выделятся сразу все столбцы. Удобно.
Дальше Excel спросит, куда вставлять данные из CSV — можно просто нажать «OK», и данные появятся в открытом листе.
Перед импортом придется создать в Excel новый workbook
Но! Если я планирую добавлять данные в CSV через Excel, придется сделать еще кое-что.
После импорта нужно принудительно привести все-все ячейки на листе к формату «Text». Иначе новые поля приобретут все тот же тип «General».
- Нажимаю два раза Ctrl+A, Excel выбирает все ячейки на листе;
- кликаю правой кнопкой мыши;
- выбираю в контекстном меню «Format Cells»;
- в открывшемся диалоге выбираю слева тип данных «Text».
Чтобы выделить все ячейки, нужно нажать Ctrl+A два раза. Именно два, это не шутка, попробуйте
После этого, если повезет, Excel оставит исходные данные в покое. Но это не самая твердая гарантия, поэтому мы после сохранения обязательно проверяем файл через текстовый просмотрщик.
Альтернатива: Open Office Calc
Для работы с CSV-файлами я использую именно Calc. Он не то чтобы совсем не считает цифровые данные строками, но хотя бы не применяет к ним переформатирование в соответствии с региональными настройками Windows. Да и импорт попроще.
Конечно, понадобится пакет Open Office (OO). При установке он предложит переназначить на себя файлы MS Office. Не рекомендую: хоть OO достаточно функционален, он не до конца понимает хитрое микрософтовское форматирование документов.
А вот назначить OO программой по умолчанию для CSV-файлов — вполне разумно. Сделать это можно после установки пакета.
Итак, запускаем импорт данных из CSV. После двойного клика на файле Open Office показывает диалог.
Заметьте, в OO не нужно создавать новый воркбук и принудительно запускать импорт, все само
- Кодировка — как в файле.
- «Разделитель» — точка с запятой. Естественно, если в файле разделителем выступает именно она.
- «Разделитель текста» — пустой (все то же, что в Excel).
- В разделе «Поля» кликаю в левый-верхний квадрат таблицы, подсвечиваются все колонки. Указываю тип «Текст».
Помимо Calc у нас в HFLabs популярен libreOffice, особенно под «Линуксом». И то, и другое для CSV применяют активнее, чем Excel.
Бонус-трек: проблемы при сохранении из Calc в .xlsx
Если сохраняете данные из Calc в экселевский формат .xlsx, имейте в виду — OO порой необъяснимо и масштабно теряет данные.
Белая пустошь, раскинувшаяся посередине, в оригинальном CSV-файле богато заполнена данными
Поэтому после сохранения я еще раз открываю файл и убеждаюсь, что данные на месте.
Если что-то потерялись, лечение — пересохранить из CSV в .xlsx. Или, если установлен Windows, импортнуть из CSV в Excel и сохранить оттуда.
После пересохранения обязательно еще раз проверяю, что все данные на месте и нет лишних пустых строк.
Если интересно работать с данными, посмотрите на наши вакансии. HFLabs почти всегда нужны аналитики, тестировщики, инженеры по внедрению, разработчики. Данными обеспечим так, что мало не покажется :)
Читайте также: