СофтХабрахабр

Редактируем CSV-файлы, чтобы не сломать данные

Естественно, тестировщики ежедневно с этими данными имеют дело: обновляют тест-кейсы, изучают результаты очистки. Продукты HFLabs в промышленных объемах обрабатывают данные: адреса, ФИО, реквизиты компаний и еще вагон всего. Часто заказчики дают «живую» базу, чтобы тестировщик настроил сервис под нее.

Все по заветам: «Не навреди». Первое, чему мы учим новых QA — сохранять данные в первозданном виде. Советы помогут ничего не испортить, сохранить информацию после редактирования и в целом чувствовать себя увереннее. В статье я расскажу, как аккуратно работать с CSV-файлами в Excel и Open Office.

Материал базовый, профессионалы совершенно точно заскучают.

Что такое CSV-файлы

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


CSV-файл состоит из строк с данными и разделителей, которые обозначают границы столбцов

Но пусть название вас не обманет: разделителями столбцов в CSV-файле могут служить и точки с запятой, и знаки табуляции. CSV расшифровывается как comma-separated values — «значения, разделенные запятыми». Это все равно будет CSV-файл.

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

CSV используют прикладные промышленные программы, в него выгружают данные из баз. Из-за своих преимуществ CSV — сверхпопулярный формат обмена данными, хотя ему уже лет 40.

Еще ничего, если таблица простая: в первом поле ID одной длины, во втором дата одного формата, а в третьем какой-нибудь адрес. Одна беда — текстового редактора для работы с CSV мало. Но когда поля разной длины и их больше трех, начинаются мучения.


Следить за разделителями и столбцами — глаза сломаешь

Я уж не говорю о красивых графиках. Еще хуже с анализом данных — попробуй «Блокнотом» хотя бы сложить все числа в столбце.

Поэтому CSV-файлы анализируют и редактируют в Excel и аналогах: Open Office, LibreOffice и прочих.

Это статья для начинающих, а на базовом уровне и небольшом объеме данных Excel с аналогами хватает. Ветеранам, которые все же дочитали: ребята, мы знаем об анализе непосредственно в БД c помощью SQL, знаем о Tableau и Talend Open Studio.

Как Excel портит данные: из классики

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

Большинство казусов связано с тем, что программа без спроса преобразует строки с набором цифр в числа.

Что сделает Excel? Округляет. Например, в исходной ячейке два телефона хранятся через запятую без пробелов: «5235834,5235835». Так мы потеряем второй телефон. Лихо превратит номера́ в одно число и округлит до двух цифр после запятой: «5235834,52».

Исходное значение потеряем напрочь. Приводит к экспоненциальной форме. Excel заботливо преобразует «123456789012345» в число «1,2E+15».

Например, КЛАДР-кодов (это такой государственный идентификатор адресного объекта: го́рода, у́лицы, до́ма). Проблема актуальна для длинных, символов по пятнадцать, цифровых строк.

Мол, и так ясно, что число положительное, коль перед ним не стоит минус. Удаляет лидирующие плюсы. Excel считает, что плюс в начале строки с цифрами — совершенно лишний символ. (В реальности номер пострадает еще сильнее, но для наглядности обойдусь плюсом). Поэтому лидирующий плюс в номере «+74955235834» будет отброшен за ненадобностью — получится «74955235834».

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

Например, «8 495 5235834» превратит в «84 955 235 834». Разбивает по три цифры. Цифровую строку длиннее трех символов Excel, добрая душа, аккуратно разберет.

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

Для Республики Алтай он начинается с нуля — «04». Удаляет лидирующие нули. Строку «00523446» Excel превратит в «523446».
А в ИНН, например, первые две цифры — это код региона. Без нуля смысл номера исказится, а проверку формата ИНН вообще не пройдет.

Потому что Windows подсказал, что в таком виде вам удобнее считывать даты. Меняет даты под локальные настройки. Excel с удовольствием исправит номер дома «1/2» на «01.фев».

Побеждаем порчу данных правильным импортом

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

Из-за него программа распознает цифровые строки как числа. По умолчанию Excel применяет к данным в загруженном CSV-файле тип «General» — общий. Такой порядок можно победить, используя встроенный инструмент импорта.

Запускаю встроенный в Excel механизм импорта. В меню это «Data → Get External Data → From Text».

Кодировка — та, что в файле, обычно определяется автоматом. Выбираю CSV-файл с данными, открывается диалог. В диалоге кликаю на тип файла Delimited (с разделителями). Если первая строка файла — шапка, отмечаю «My Data Has Headers».

Отключаю «Treat consecutive delimiters as one», а «Text qualifier» выставляю в «». Перехожу ко второму шагу диалога. Выбираю разделитель полей (обычно это точка с запятой — semicolon). Если разделитель в CSV — запятая, то text qualifier нужен, чтобы отличать запятые внутри текста от запятых-разделителей.) (Text qualifier — это символ начала и конца текста.

Для всех столбцов выставляю тип «Text». На третьем шаге выбираю формат полей, ради него все и затевалось. Удобно. Кстати, если кликнуть на первую колонку, зажать шифт и кликнуть на последнюю, выделятся сразу все столбцы.

Дальше Excel спросит, куда вставлять данные из CSV — можно просто нажать «OK», и данные появятся в открытом листе.


Перед импортом придется создать в Excel новый workbook

Если я планирую добавлять данные в CSV через Excel, придется сделать еще кое-что. Но!

Иначе новые поля приобретут все тот же тип «General». После импорта нужно принудительно привести все-все ячейки на листе к формату «Text».

  • Нажимаю два раза Ctrl+A, Excel выбирает все ячейки на листе;
  • кликаю правой кнопкой мыши;
  • выбираю в контекстном меню «Format Cells»;
  • в открывшемся диалоге выбираю слева тип данных «Text».


Чтобы выделить все ячейки, нужно нажать Ctrl+A два раза. Именно два, это не шутка, попробуйте

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

Альтернатива: Open Office Calc

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

При установке он предложит переназначить на себя файлы MS Office. Конечно, понадобится пакет Open Office (OO). Не рекомендую: хоть OO достаточно функционален, он не до конца понимает хитрое микрософтовское форматирование документов.

Сделать это можно после установки пакета. А вот назначить OO программой по умолчанию для CSV-файлов — вполне разумно.

После двойного клика на файле Open Office показывает диалог. Итак, запускаем импорт данных из CSV.


Заметьте, в OO не нужно создавать новый воркбук и принудительно запускать импорт, все само

  1. Кодировка — как в файле.
  2. «Разделитель» — точка с запятой. Естественно, если в файле разделителем выступает именно она.
  3. «Разделитель текста» — пустой (все то же, что в Excel).
  4. В разделе «Поля» кликаю в левый-верхний квадрат таблицы, подсвечиваются все колонки. Указываю тип «Текст».

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

И то, и другое для CSV применяют активнее, чем Excel. Помимо Calc у нас в HFLabs популярен libreOffice, особенно под «Линуксом».

Бонус-трек: проблемы при сохранении из Calc в .xlsx

Если сохраняете данные из Calc в экселевский формат .xlsx, имейте в виду — OO порой необъяснимо и масштабно теряет данные.


Белая пустошь, раскинувшаяся посередине, в оригинальном CSV-файле богато заполнена данными

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

Или, если установлен Windows, импортнуть из CSV в Excel и сохранить оттуда. Если что-то потерялись, лечение — пересохранить из CSV в .xlsx.

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

HFLabs почти всегда нужны аналитики, тестировщики, инженеры по внедрению, разработчики. Если интересно работать с данными, посмотрите на наши вакансии. Данными обеспечим так, что мало не покажется 🙂

Показать больше

Похожие публикации

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»