Есть у меня задачка — загрузить в некую БД сведения из файлов формата XLS. Загрузка осуществляется программой, которая извлекает данные из файла XLS, при помощи COM-объекта Excel. Как следствие, данные выгружаются крайне долго и печально, а учитывая что файлов около десятка и в каждом по 7000-10000 строк, то всё весьма не быстро.
Поэтому было решено использовать Excel для превращения файла XLS в CSV, а потом обработка CSV как массива строк. Строки предполагалось разбирать при помощи регулярного выражения, используя встроенный в Windows процессор регулярных выражений VBScript.RegExp.
По умолчанию (и в соответствии с настройками системы) в качестве разделителя Excel использует точку с запятой. Всё было бы хорошо, пока в файлах не стали попадаться значения такого вида: 123;456;7";"89
Очевидно, что Excel экранирует символ-разделитель, обрамляя его кавычками. И это бы ничего, но в файлах встречаются значения и такого вида: 123;456;"122";"789; ... то есть полная мешанина из кавычек и символов-разделителей.
Проблема состоит в том, как быстро и просто разобрать такой файл по отдельным значениям. Excel с такой задачей справляется великолепно.
У меня родилось несколько идей, как это сделать: Нормализовать файл;
Использовать регулярное выражение.
Под нормализацией файла я понимаю замену всех встреченных в файле символов-разделителей (;) на какой-нибудь нейтральный символ, который потом легко заменит назад, например на символ "неразрывный пробел" (CHAR(160)).
После того, как файл будет нормализован, к каждой строке файла применяется регулярное выражение, которое извлекает хранимое значение. Полученное значение надо проверить на наличие символа "неразрывного пробела" и если он там есть, то заменить его на символ-разделитель.
Вариант плох тем, что надо делать предварительную обработку данных, потом использовать регулярное выражение, а потом производить обратную замену данных. Ввиду этих особенностей родился второй вариант — сразу написать регулярное выражение, разбирающее строку нужным образом. При реализации данного способа я столкнулся с той проблемой, что моих навыков создания регулярных выражений недостаточно для написания РВ, сразу корректно разбирающего строку, с учётом кавычек, наличия символов-разделителей в строке и т.п.
Я покопался в интернете и нашёл пару РВ, максимально близко решающих мою задачу: ,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))
(""([^""]*|""{2})*""(,|$))|""[^""]*""(,|$)|[^,]+(,|$)|(,)
Ввиду того, что парсер заменяет скобку с точкой с запятой на смайлик, точка с запятой заменена просто запятой.
Первое регулярное выражение находит в строке все символы-разделители, отделяя те, которые экранированы кавычками:
123;456;7890 -> (;)(3)(;)(7), где (3)(7) — позиции символа-разделителя.
123;456;78";"90 -> (;)(3)(;)(7)
Строки, полученные
Второе выражение выделяет сразу элементы строки, но всё равно требуется пост-обработка — удаление символа-разделителя с концов извлеченных групп символов, и удаление парных кавычек по краям извлечённого текста. И да, второе выражение работает некорректно, например, на такой строке: 123;"456;""""";789
Выражение считает, что в строке 4 элемента, хотя данная строка была получена из 3 ячеек: 123 | 456;"" | 789
Собственно, прошу помощи в следующем:
Решал ли кто-нибудь аналогичную задачу и если да, то может быть знает более элегантный способ её решения?
Помогите оптимизировать какое-либо из существующих регулярных выражений, или помогите написать новое РВ, которое бы корректно отрабатывало на файлах CSV, продуцируемых Microsoft Excel 2010
Спасибо!
Re: Разработка регулярного выражения для разбора строки с разделителями
Здравствуйте, masta, Вы писали:
M>Собственно, прошу помощи в следующем: M>
M>Решал ли кто-нибудь аналогичную задачу и если да, то может быть знает более элегантный способ её решения? M>Помогите оптимизировать какое-либо из существующих регулярных выражений, или помогите написать новое РВ, которое бы корректно отрабатывало на файлах CSV, продуцируемых Microsoft Excel 2010 M>
IMHO, регулярки в такой хитрозагнутой задаче, как парсинг CSV, не катят. Если задача, конечно, не одноразовая. Лучшее, что мне пришло в голову — наваять врукопашную специализированный парсер с простейшим конечным автоматом (несколько флагов) внутри. Но мне, правда, требовался быстрый (и потому однопроходный) парсер.
Btw, парсер, приведенный в качестве примера в книге Кернигана и Пайка "Практика программирования", проверки временем не выдержал. Насколько помню, ошибочной оказалась сама идея сначала разбивать файл на строки, а уже потом искать в ней разделители.
Одним из 33 полных кавалеров ордена "За заслуги перед Отечеством" является Геннадий Хазанов.
Re: Разработка регулярного выражения для разбора строки с ра
Здравствуйте, masta, Вы писали:
M>Решал ли кто-нибудь аналогичную задачу и если да, то может быть знает более элегантный способ её решения?
Решал на питоне, используя парсер xls напрямую, без промежуточного приведения к csv.
Как насчёт посмотреть на уже существующие парсеры xls для С/С++?
Начиная с libxls и т.п. (Просто погугли "c++ xls parser", и будет щасте)
(Сам ими не пользовался — мне хватило питона)
M>Помогите оптимизировать какое-либо из существующих регулярных выражений, или помогите написать новое РВ, которое бы корректно отрабатывало на файлах CSV, продуцируемых Microsoft Excel 2010
csv проще разбирать побуквенно, конечным автоматом, чем рожать эквивалентное автомату регулярное выражение.
Тем более, что эксель создаёт локале-специфичный csv: разделитель ";" взят из региональных настроек, а в оригинале это всё же comma-separated values.
Перекуём баги на фичи!
Re: Разработка регулярного выражения для разбора строки с разделителями
M>Решал ли кто-нибудь аналогичную задачу и если да, то может быть знает более элегантный способ её решения? M>
M>Спасибо!
Я использовал для аналогичной задачи xls2csv | awk | tcl | sql , правда скорость была не важна.
В разбор строки с разделителями в awk- элементарная задача. Мне пришлось использовать еще тикль,
т.к. задача была посложнее. Посмотри еще на утилиту csvfix, может ее хватит.
Re: Разработка регулярного выражения для разбора строки с разделителями
Здравствуйте, masta, Вы писали:
M>было решено использовать Excel для превращения файла XLS в CSV, а потом обработка CSV как массива строк.
CSV нельзя использовать в качестве промежуточного формата. Потому что в наиболее распространённой реализации разделители (полей и десятичный) зависят от региональных настроек того пользователя, который генерирует CSV-файл.
Рассмотрите ODS или хотя бы XLSX. Парсить строго XML-парсером.
Re[2]: Разработка регулярного выражения для разбора строки с разделителями
Видимо, я недостаточно полно описал задачу, потому что некоторые советы неприменимы в принципе. Проблема также в том, что на форуме нет подходящего подформума, куда можно было впихнуть эту тему, поэтому я опубликовал её тут. Дело в том, что грузить данные я собираюсь в 1С и есть 2 подходящих мне способов загрузки данных:
используя ADO;
используя промежуточный файл с разделителями (CSV).
Начнём с файла. Итак, преимущества загрузки данных из файла с разделителями такие:
есть готовые работающие обработки, выполняющие разбор строки без использования регулярных выражений;
возможна предварительная обработка данных на листе.
Несомненным плюсом является наличие готовых кодов, которые успешно разбирают строку и заталкивают её в таблицу значений (ТЗ) в 1С. Притом разбор строки выполняется без использования регулярных выражений. Этот код корректно обрабатывает строку любой сложности, напичканную символами-разделителями и кавычками в любом сочетании. Просто супер!
Также плюсом является то, что можно выполнить предварительную обработку данных на листе Excel, удалив с него всякий мусор в виде символов табуляции, неразрывных пробелов и т.п. Это важно, т.к. файлы, которые грузятся в систему, поставляются разными организациями и зачастую содержат приличное количество хлама.
Ещё одним плюсом является то, что при способе загрузки с использованием CSV появляется возможность сбросить форматирование с ячеек листа. Таким образом отпадает необходимость преобразовывать строки вида "2 100,00 руб." в число "2100", которое, по сути, и записано в ячейке, но благодаря стилю форматирования "Денежный" представлено в виде строки "2 100,00 руб."
Минусы у этого способа такие:
приходится использовать COM-объект Excel;
приходится использовать посредника в виде файла с разделителями, что немного "колхозно".
Если говорить о возможности использования ADO для решения моей задачи, то меня беспокоят несколько моментов.
1. Зачастую, поставляемые файлы имеют расширение .xls, но не являются в прямом смысле таблицей Excel, а представляют собой текст, отформатированный при помощи разметки HTML. Видимо, поставщику удобнее так делать экспорт. Excel такой файл открывает, глазом не моргнув, а как с ним будет работать ADO? Смогу ли я при использовании ADO загрузить такой файл без дополнительных подпрыгиваний?
2. Зачастую, файлы "косые". То есть, например, в первом столбце данные начинаются с первой строки, а в третьем столбце — с десятой. Как отсутствие данных в столбце будет интерпретировано ADO? Не случиться ли так, что часть данных будет утеряна? При использовании CSV пустые ячейки нормально выгружаются в виде пустых же значений между разделителями.
3. Файлы могут содержать объединения ячеек, как правило, строк. При загрузке из CSV объединённая ячейка утрачивает признак объединения и вместо неё получается одна ячейка со значением и много пустых ячеек. Как такая ситуация будет интерпретирована при использовании ADO?
4. Файл может быть отформатирован совершенно произвольно. Например, с первой по десятую строку в первом столбце стоит дата с использованием соответствующего форматирования, а с 11 строки и далее — число. В каком виде будут загружены данные?
5. Не потребуется ли дополнительного анализа, что за файл загружается, если один из поставщиков предоставил текст в HTML-разметке, присвоив ему расширение xls, второй — классический файл Excel 2003 (xls), третий — файл Excel 2010 (xlsx)? Крайне не хотелось бы городить систему правил для определения того, что это за файл и как его грузить. С CSV таких проблем нет по определению.
Перекуём баги на фичи!
