Справочная: выборка из файловых баз, крупные *.txt файлы, регулярки, EmEditor, *.csv

Тема в разделе "Свободное общение", создана пользователем orka13, 25 апр 2018.

  1. 25 апр 2018
    #1
    orka13
    orka13 ЧКЧлен клуба (А)
    [​IMG]
    Тема создана по просьбе складчиков для обсуждений перечисленных в заголовке тем. Поскольку даже на моих последних авторских складчинах постоянно задаются одинаковые вопросы, и хотелось бы создать структурированный мануал для новичков.

    Разбирать мы здесь будет принципы работы с крупными текстовыми файлами формата *.txt, *.csv. Частично затронем таблицы Microsoft Excel (*.xls, *.xlsx). Специализированные файлы баз данных (MySQL, SQLite) НЕ трогаем.
    Важно: Прежде чем задавать здесь вопрос, попробуйте подобное найти хотя бы в первых постах этой темы, если ответа нет, то сформулируйте его в гугле, если и там не понятно, то тогда уже пишем здесь.

    #1. Файлы. Их виды, основные инструменты

    #1.1. *.txt файлы очень удобны для быстрой запись крупных данных. Поскольку для «дописи» в готовый файл (скажем новой строки в конце файла) нам (точнее нашей программе для обработки файлов, например, Zennoposter) не надо считывать весь файл себе в память (как с таблицами *.xls), а хватает просто «приклеить к нему» нужный фрагмент. Поэтому такие файлы легко склеивать между собой методом простого копирования «файл+файл+файл…». И допись в них моментально происходит, даже если файл занимает уже несколько Гигабайт.
    #1.1.1. Кодировка (набор правил, или методов обозначение каждого символа) обычно бывает двух популярных видов:
    #1.1.1.1. cp-1251 (другие названия-синонимы «Windows», «Кириллица», «ANSI»).
    #1.1.1.2. utf-8 (другие названия-синонимы «Юникод», бывает еще с сигнатурой (BOM) и без нее, но это уже мелочи). Имеет значительно больший набор символов относительно cp-1251. То есть она более универсальна, включает в себя множество нестандартных символов и букв (алфавит китайский, арабский и т.д). Но поскольку для описания 1 символа в ней используется больше данных, то тот же текст на кириллице в ней будет занимать в ~2 раза больше места чем в cp-1251. Хотя английский текст одинаково по размеру занимает место. Советую работать именно с ней, чтобы в будущем не наткнутся на иероглифы вместо неподдерживаемых символов (как в cp-1251).
    #1.1.2. Иструменты
    #1.1.2.1. Для работы с такими файлами рекомендую редактор EmEditor (подробнее в отдельном посте ниже). Это как как бы аналог более популярного в массах Notepad++. Я пользуюсь Notepad++ ежедневно на мелких файлах (удобня подсветка, куча плагинов), но он очень слабо открывает/обрабатывает крупные файлы (~> 100 мегабайт), поэтому изучать его не вижу смысла.
    #1.1.2.2. Для сортировки, удаления дублей и других популярных операций я перечислил список рекомендуемого софта в этом посте.

    #1.2. *.csv файлы это фактические такие же .txt файлы внутри, просто переименованы в .csv, и текст в их строках может быть разделен между собой некими символами-разделителями, и в общей картине несколько строк с разделителями уже формируют подобие «таблицы». Но мы все так же может работать с данными как текстовыми строками (быстро дописывать, склеивать и т.д.).
    #1.2.1. По умолчанию считается что в *.csv файлах разделителем должна быть запятая (,), но на практике чаще используют точка с запятой (; ) или табуляция (типа длинный невидимый пробел, или другими словами символ \t). Хотя когда-то под каждый вид разделителя предназначался отдельный формат названия (*.tsv, *.dsv), но, чтобы не загружать пользователей лишней информацией, теперь все используют маркировку *.csv.
    #1.2.2. Для работы с такими файлами именно как с «таблицами» рекомендую EmEditor (опишу подробнее в следующих постах), НЕ рекомендую Excel (объяснение в следующем пункте).

    #1.3. *.xlsx (*.xls в старых версиях) файлы имеют ограничение. Формат *.xls допускает 65 536 строк, *.xlsx 1 048 576 строк. Поэтому для работы с крупными файлами они не пригодны. Они подойдут разве что для импорта в них финальных мелких отчетов из *.csv, чтобы удобнее было работать с ними людям, привыкшим к Excel (привет бухгалтерии :)).
    #1.3.1. Заметьте, что я написал именно «для импорта», а не «для открытия», хотя Excel любит прописывать себе в ОС Windows как программа по умолчанию для *.csv файлов, но на деле он коряво открывает эти файлы, если они не в кодировке cp-1251. И к тому же после открытия он блокирует другим программам возможность редактирования этого файла (не открывайте файлы, пока в них идет запись в Zennoposter, а то уже есть прецеденты в складчинах). Правильный импорт [*.csv > *.xlsx] делается приблизительно так в меню Excel (под себя перенастроите уже каждый шаг):
    [Данные > Из текста > Открыть файл > С разделителями, Unicode UTF-8 > Знак табуляции > Готово»]
    Скрытая ссылка
     
    Последнее редактирование: 25 апр 2018
    13 пользователям это понравилось.
  2. 25 апр 2018
    #2
    orka13
    orka13 ЧКЧлен клуба (А)
    [​IMG]
    #2. Регулярки

    (Wikipedia: Регулярные выражения).
    Используются для более расширенного поиска\замены в тексте.
    #2.1.1. Для теории рекомендую прочитать страницы №24 - 60 этой книжки:
    «Friedl J. / Фридл Дж. - Mastering Regular Expressions / Регулярные выражения».
    #2.1.2. А под рукой поначалу можно держать эту картинку-шпаргалку:
    Скрытая ссылка
    Статья авторов шпаргалки с разъяснениями/
    #2.1.3. Ну и книга с примерами построения логики на разных жизненных задачах:
    «Ян Гойвертс, Стивен Левитан - Регулярные выражения. Сборник рецептов».

    #2.2. Моя глубока практика с регулярками началась на ZennoPoster с его инструментом «Конструктор регулярных выражений». Там же есть и статья по основным популярным задачам: "Регулярные выражения, что это такое и с чем их едят?"
    Зачем это нам, если нет лицензии Зенки спросите вы? Да не проблема, ибо какой-то безызвестный добрый человек портировал этот конструктор в обычную бесплатную утилиту. Скачать архивом (обсуждение и исходники на форуме зенки).
    За корректную работу всех функций я не ручаюсь, ибо в работе не использую, но на первый взгляд работает, жалоб нет.

    #2.3. Вот что еще нагуглил интересное для тестирования регулярок:
    #2.3.1. Хабр: Визуальный генератор регулярных выражений (сервис онлайн)
    #2.3.2. Скрытая ссылка (сервис онлайн)
    #2.3.3. PowerGREP Screen Shots Visual Search and Replace Utility.

    #2.4. Под любую задачу можно нагуглить готовое решение. Правда в каждом языке программирования, а соответственно и в каждой программе синтаксис использования регулярок может чуть-чуть различаться (в пределах нескольких процентов).

    #2.4.1. Я в своей практике заметил, что слишком сложные регулярки (с множеством разных условий и костылей :)) не всегда поддерживаются. Приблизительно коэффициент поддержки сложных регулярок можно так выразить:
    • ZennoPoster C# блоки (кодинг) = 99%
    • ZennoPoster обычные кубики = 90%
    • EmEditor = 85%
    • Notepad++ = 70%
    • Gnu (консоль Linux\Unix) = 90%
    • GnuWin32 (порт консоли Linux\Unix в Windows) = 80%
     
    Последнее редактирование модератором: 1 май 2018
    7 пользователям это понравилось.
  3. 25 апр 2018
    #3
    orka13
    orka13 ЧКЧлен клуба (А)
    [​IMG]
    #3. EmEditor

    Лицензия: ShareWare
    Язык: Multi + Русский

    #3.2. Самый мощный визуальных реактор для текстовых файлов (включая *.csv), который я встречал. Может открывать практически любого размера файлы. Грузит во временную папку на диске С, или в память, если отключить использование временного файла (Меню > Сервис > Настройка > Дополнительно).

    #3.3. Главное правило: чтобы программа зря не зависала на сложных операциях (поиск по ругуляркам, фильтрация с условиями и т.д.) протестируйте сначала нужное действие на маленьком файле (клочке от большого документа). Обычно я в складчинах выдаю в первом посте мини-файл с случайной выборкой из базы для тестов и понимания структуры, вот он и подойдет. А то вдруг результат не устроит, и получится что время зря использовалось.

    #3.4. Самые часто используемые операции в нем для меня это:
    #3.4.1. Поиск: комбинация горячих клавиш [Ctrl+F]
    Скрытая ссылка
    #3.4.2. Замена: [Ctrl + H].
    #3.4.3. Закладки: [Поиск>Закладка], а потом строки с закладками обрабатываем через [Меню > Правка > Закладки > Документа] (вырезаем, копируем в другой документ и т.д.).
    #3.4.4. Удаление из таблицы дубликатов именно в пределах одного столбца (так чтобы структура таблицы не «посыпалась»): [Правка > Дополнительно > Обработка дубликатов строк…].
    [​IMG]
    #3.4.5. Сортировка всего документа по значению конкретного столбца: выделяем ячейку нужного столбца, потом [Правка > Сортировка].
    #3.4.6. Привести нужный фрагмент текста к единому РеГиСТрУ: [Правка > Преобразовать выделенный фрагмент > все строчные].
    #3.4.7. Привести документ к виду таблицы, задав в нем нужный формат разделителей: [Правка > Разделённые данные]
    Там же собрана и основная масса команд для работы с таблицей. Ну или в панели «Разделителей» можете себе кнопочки поискать нужные для работы с таблицей:
    Скрытая ссылка
    Последняя кнопка оттуда поможет закрепить первую строку, как в Excel: [Заголовок > Заголовок 1]
    #3.4.8. Фильтрация из всей таблицы только тех строк, ячейки в которых содержат нужное значение. Можно даже с регулярками работать. Но на крупных файлах вводите сразу регулярку в поле через «копирование-вставку», а то после каждого набора символа в строке регулярки программа будет пытаться применить эту «незаконченную» регулярку в фильтрации, а это займет лишнее время.
    Подробнее с примерами и скриншотами в этих трех постах:
    1. https://v13.skladchik.org/threads/Б...-Карты-Яндекс-Справочник.186213/#post-6533304
    2. https://v13.skladchik.org/threads/Б...-Карты-Яндекс-Справочник.186213/#post-6533318
    3. https://v13.skladchik.org/threads/Б...-Яндекс-Справочник.186213/page-3#post-6558201

    #3.5. Перед тем как задавать вопросы, сначала попробуйте поискать решение среди функций программы самостоятельно, прокликав по всем менюшкам и кнопочкам.
     
    Последнее редактирование модератором: 1 май 2018
    6 пользователям это понравилось.
  4. 2 июн 2018
    #4
    intelligent man
    intelligent man ОргОрганизатор
    @orka13
    Пара вопросов, интересуют советы, из разряда как-бы сделал ты :)
    1) Есть большая база данных, разбитая по городам и в формате экселя. Состав столбцов везде одинаковый, файлов много. Как с минимальными усилиями перевести базу в единый файл txt для EmEditor?
    2) Вижу, что в этой базе мало почт, хочу сделать их допарсинг. Купил твой шаблон https://v13.skladchik.org/threads/z...х-страниц-многопоток-get-запросы-паук.187588/ Вытащу я адреса сайтов, удалю дубли и прогоню шаблоном. А как потом грамотно вернуть эти почты в файл txt для EmEditor?
    Спасибо.
     
  5. 2 июн 2018
    #5
    orka13
    orka13 ЧКЧлен клуба (А)
    1) шаблон на зенке бы сделал с циклом чтения эксель и пере-сохранением строк в CSV.
    2) а здесь уже надо костыль делать, и иметь хороший опыт в работе с зенкой и чуть с SQLite. Я проделывал подобное:
    - "Вытащу я адреса сайтов, удалю дубли и прогоню шаблоном. А как потом грамотно вернуть эти почты в файл txt для EmEditor?" - результат этого загоняем импортом в SQLite файлик-БД (я делал через консольную утилиту с оф сайта вроде).
    - потом на зенке делаем шаблон, который будет дергать сайты из строкки CSV, проверять наличие их почт в файлике БД, и при нахождении дописывать почты в новы столбец.
     
    1 человеку нравится это.
  6. 2 июн 2018
    #6
    intelligent man
    intelligent man ОргОрганизатор
    @orka13
    Понятно, печаль, а есть в EmEditor аналог функции ВПР из экселя?
     
  7. 2 июн 2018
    #7
    orka13
    orka13 ЧКЧлен клуба (А)
    да хз, мне пришлось гуглить что такое "ВПР из экселя" :).
     
    1 человеку нравится это.
  8. 2 июн 2018
    #8
    intelligent man
    intelligent man ОргОрганизатор
    @orka13
    Это такая подстановка данных, то есть два массива информации, чтобы соединить сайты с почтами. Придется делать таким образом, если не найду что-то более толковое :(
     
  9. 14 июн 2018
    #9
    intelligent man
    intelligent man ОргОрганизатор
    @orka13
    1)
    Я пробовал по разному, никак не получается сделать. Вроде раньше делал, сейчас забыл, фильтрует в ноль. Например, задача в столбце Адрес сразу отфильтровать по двум значениям: Saint Petersburg и Санкт-Петербург. Можно показать, как правильно вбить регулярку и как еще настроить правильно Расширенный фильтр. Все мои действия фильтруют в ноль строк :(
    2) https://v13.skladchik.org/threads/База-организаций-России-из-Яндекс-Карты-Яндекс-Справочник.186213/
    Скрытая ссылка
    В базе присутствуют адреса на разных языках, как так получилось, по другому было никак?
     
  10. 14 июн 2018
    #10
    orka13
    orka13 ЧКЧлен клуба (А)
    Там так владельцы объекта (отель Скрытая ссылка ) походу прописали на инглише, может надеялись что для бурж-туристов это заманухой будет.

    Насчет регулярки, может там лишние кнопки в панели фильтрации нажались, вот оно и неправильно ищет, надо так:
    Скрытая ссылка

    "Расширенный фильтр" вообще не трогал еще. Посмотрел бегло... там просто можно добавлять неограниченное число дополнительных условий.
     
    1 человеку нравится это.
  11. 14 июн 2018
    #11
    Сычик
    Сычик МодерМодератор Команда форума
    Для ваших проблем может быть полезна программа TextPipe.
    Перерабатывает тексты, кодировки, csv, базы данных и прочее.
    Есть регулярные выражения.
    Обычно сравнивается с PowerGREP по функционалу.
     
  12. 14 июн 2018
    #12
    orka13
    orka13 ЧКЧлен клуба (А)
    В старпосте есть ссылка список рекомендуемого софта в этом посте. Я TextPipe там упоминал, к сожалению на практике он не подходит для крупных файлов, слишком медлителен.
     
  13. 1 июл 2018
    #13
    orka13
    orka13 ЧКЧлен клуба (А)
    По просьбе складчиков запилил еще видео по самым популярным операциям с CSV таблицами в EmEditor. В качестве материала используется таблица отчетов парсера Яндекс.Карт Барсик.

    План видео:
    1. Настройка Emeditor для мощных ПК, только если много памяти RAM (~>= 16 Гб):
    [Меню > Сервис > Настройки > Дополнительно > Использовать временный файл > отключить]
    2. Открываем крупный файл-таблицу.
    3. Указываем редактору что у нее разделители столбцов «Табулятор»
    [Панель «Разделители» > Табулятор]
    4. Делаем первую строку строкой-заголовком, чтобы она закрепилась при прокрутке:
    [Панель «Разделители» > Заголовки > Заголовок 1]
    5. Удаляем дубли исключительно по содержанию одного столбца (№26, - [ID на Яндекс.Карты]):
    [Меню > Правка > Дополнительно > Обработка дубликатов строк…]
    6. Проводим поиск нужных строк (выборку) через фильтрацию содержания текста в нужном столбце:
    [Панель «Фильтр»]
    7. Сортируем столбец по его значению.
    Через меню ПКМ на ячейках нужного столбца.
    8. Найденные строки копируем в новый документ, либо в MS Excel через:
    А) [Копировать > Вставить] (Ctrl+A + Ctrl+C + Ctrl+V)
    Б) Через пометку нужны строк закладками, потом через копирование строк с закладками в новый документ, потом импорт этого документа в Excel.

     
    Последнее редактирование модератором: 2 июл 2018
    5 пользователям это понравилось.
  14. 1 дек 2018
    #14
    arstotzka
    arstotzka ЧКЧлен клуба
    @orka13 Совершенно случайно наткнулся на твою тему, хотел сказать искреннее спасибо. Очень подробно все расписано, сразу сохранил себе и часть информации уже пригодилась в работе. Отдельный респект за столь скурпулезное структурирование контента, по пунктам и подпунктам, с ссылками и разъяснениями.
     
    5 пользователям это понравилось.
  15. 17 июн 2019
    #15
    orka13
    orka13 ЧКЧлен клуба (А)
    Небольшое замечание по работе EmEditor:
    Версия 17.9.0 у меня нормально справляется с удалением дублей в столбцах (#3.4.4). А вот на другом сервере более мощном у меня 18.2.1, и она зависает на этой операции. Так что иногда стоит попробовать другую версию, если попадется с глюками.
     
    3 пользователям это понравилось.

Поделиться этой страницей