Согласитесь, было бы неплохо получить инструмент, который принимал бы запросы к данным со слабовыраженной и нестабильной структурой в том виде, в котором человек объяснял бы другому человеку правила по которым он глазами находит колонку "цена" в УПД. Ну, например:
о цене мы точно знаем, что это число, оно находится в колонке с названием содержащим слово "цена", и оно располагается между наименованием и ставкой НДС
Сформулированный "запрос" содержит основные критерии (тип, название колонки) и "подстраховку" (расположение относительно других данных), которые не позволят выбрать что-то лишнее. При этом, как вы могли заметить, такой "запрос" не привязан к какому-то одному формату, с помощью него можно успешно найти цену и в УПД и в Накладной и в ТОРГ-12. И уж тем более, там ничего не написано ни про номер колонки в которой стоит цена ни про номер строки с которой начинаются товары.
Но если так может человек, почему так не может 1С?
Если обобщить, мы хотим получить возможность писать "запрос", атрибутирующий любые данные, имеющие непостоянную структуру, через непосредственно само значение и его окружение.
Фактически, такой "запрос" можно получить в виде фразы ГДЕ языка запросов 1С, построив нужным (и автоматическим) образом фразу ИЗ. Фраза ВЫБРАТЬ особого интереса не представляет и будет статичной. Если "перевести" описание колонки Цена, данное выше, с человеческого на язык запросов 1С, получится примерно такое:
ГДЕ ТипЗначения(Слово) = Тип(Число) И СловаВыше ПОДОБНО ""%ЦЕНА%"" И СловаСлева ПОДОБНО ""%[А-Я]%"" И СловаСлева.Длина > 3 И ТипЗначения(СловаСправа) = Тип(Число)
Разберемся, в том, что тут понаписано.
Основные параметры
Во-первых надо понять, что мы имеем дело с обычным запросом, ну т.е. с его фразой "ГДЕ". Соответственно, для описания условий нам доступны все возможности языка запросов: сравнения, равенства, манипуляции периодами, математические выражения, оператор "ПОДОБНО", и все остальное, что только можно написать в обычном запросе.
Во-вторых, для рассмотрения доступно само поле. О нем доступны следующие сведения:
- в реквизите Слово.Значение хранится "чистое" значение поля.
- в реквизите Слово.ОригинальноеЗначение хранится значение поля в том виде, в котором оно было в изначальном экселе, без очистки.
- в реквизитах Слово.НомерСтроки и Слово.НомерКолонки хранятся координаты позиции слова в исходной таблице.
- Слово.Длина содержит длину строки (для не строк — длину строкового представления соответствующего значения).
Для наглядности и простоты написания, выражения "Слово.Значение" и "Слово" — эквивалентны, т.е. когда вы не уточняете какое имеете ввиду поле у таблицы "Слово" — будет подставлено поле по-умолчанию "Значение".
В-третьих, доступно все окружение каждого поля. Например, если мы хотим описать какое-то поле через его предшественника — ставку НДС, мы можем написать "СловаСлева ПОДОБНО "18\%". Другой пример — описание поля через заголовок столбца: "СловаСверху ПОДОБНО "%ЦЕНА%". Ну и так далее, всего возможно 8 вариантов обращения к окружению, все описаны в справке. Каждое из окружающих слов содержит туже структуру его описания, что и само Слово (Значение, ОригинальноеЗначение, НомерСтроки, НомерКолонки, Длина). И также, при опускании ".Значение" — оно будет поставлено автоматически (т.е. записи "СловаСлева" и "СловаСлева.Значение" эквивалентны).
В-четвертых, требуется осознать следующую вещь: "Слева" — это не значит "непосредственно в соседней слева ячейке". Это значит где-то слева. Такой подход помогает нам легче обращаться к наименованиям колонок (они же могли быть и 3 и 10 строк назад), а также не иметь проблем при объединениях ячеек. Но, если есть уверенность в непосредственной близости каких-то данных, всегда можно написать "СловаСлева.НомерКолонки + 1 = Слово.НомерКолонки"
В-пятых, в каждом направлении (влево, вверх…) мы можем анализировать не одно поле. В таком случае мы можем обзывать поля как хотим, главное — оставить префикс из того набора, который у нас есть. Например, при поиске количества хотим чтобы оно следовало за "шт" и "796": "СловаСлеваИмяЕдИзм = "ШТ" И СловаСлеваКодЕдИзм = 796". Естественно, если написать еще какое-то условие для синонима СловаСлеваКодЕдИзм — будет понятно, что это требование к тому же самому полю, а не к третьему.
Углубимся в технику
Технически, весь объем данных (Excel, табличный документ, Word, не важно) помещается в таблицу значений с колонками Значение, ОригинальноеЗначение, НомерСтроки, НомерКолонки, Длина, где каждая запись соответствует одной ячейке. Далее, таблица значений помещается в запрос и она соединяется сама с собой по правилам, в результате которых каждому слову (синоним Слово) получаются сопоставлены слова стоящие в той же строке слева от него (синоним СловаСлева), в той же строке справа от него (СловаСправа) в той же колонке выше него (СловаВыше) и в той же колонке ниже (СловаНиже). Области этих четырех соединений схематично показаны ниже, на первой схеме.
Кроме того, есть еще 4 таблицы: представляющие все ячейки справа (ВсеСловаСправа) и все ячейки слева (ВсеСловаСлева), независимо от строки — вторая схема. И все ячейки сверху (ВсеСловаВыше) и все ячейки снизу (ВсеСловаНиже) независимо от колонки — третья схема. Т.е. если предыдущие таблицы представляли собой "линию" из ячеек, то эти таблицы — это поле ячеек. Соединения всех таблиц происходит по координатам ячейки в реквизитах НомерСтроки и НомерКолонки. И, естественно, реально объявляются во фразе ИЗ только те таблицы, которые используются во фразе ГДЕ.
Есть также дополнительный вариант, для "условно-не-табличных" данных (например, требуется вычленить из шапки документа номер, дату) — хорошо работает принцип когда каждое слово по одному помещаются в таблицу (получается, что каждое конкретное слово становится доступно для анализа отдельно от остального окружения в ячейке). За это отвечает реквизит РежимРаботы функции ТаблицаСловПоМассиву. Нумерация строк и колонок при этом остается как в оригинале. Так очень эффективно бороться, например, с датами (их пишут все кому как в голову взбредет: кто-то в одной ячейке, кто-то в трех, кто-то еще псевдо-печатное оформление оставляет в стиле "___")
Также отдельное внимание стоит обратить на разборщика входящих данных (функция ОкультуритьТекст). Эта функция строит т.н. "чистое" значение по оригинальному значению, без нее весь банкет был бы невозможен. Она работает так:
- переводит все в верхний регистр
- определяет соответствует ли значение одному из шаблонов даты, если да — пытается привести значение к типу дата.
- определяет можно ли привести значение к числу. если да — приводит. при этом разные написания вроде "1 234.50", "1`234,50", "1.234,50" и т.п. будут обработаны успешно.
- для строк — все символы кроме букв, цифр и символов "-", "/", "(", ")", заменяет на пробелы, удаляет пробелы в начале и конце строки, удаляет повторяющиеся пробелы.
В результате ее работы мы получаем т.н. "чистое" значение, однако, если нам все-же захочется проанализировать оригинальное значение — оно сохраняется в реквизите ОригинальноеЗначение.
Визуальная консоль запросов
Для проверки запросов в процессе разработки функциональности (ну и для поиграться) предназначена визуальная консоль, которую вы видите в гифках и можете скачать ниже. Пользоваться консолью проще простого:
- в табличный документ консоли копи-пастим эксель
- пишем текст своего запроса
- жмем выполнить — соответствующие запросу ячейки будут подсвечены
В консоли есть справка и простейшие примеры. В модуле объекта изолированы процедуры, которые вы должны переместить в свой общий серверный модуль для программного использования.
Методика программного использования
При разборе документов у меня методика использования получилась следующая:
- Думаем, какой столбец будет опорным. Это должен быть столбец, определить элементы которого можно с наименьшей погрешностью. Я выбрал цену. (многообещающе выглядела ставка НДС, но ее нет в некоторых форматах)
- Делаем запрос ко всем значениям опорного столбца. Для цены у меня такой запрос: "ГДЕ СловаСлева ПОДОБНО ""%[А-Я]%"" И СловаСлева.Длина > 3 И ТипЗначения(СловаСправа) = Тип(Число) И СловаВыше ПОДОБНО ""%ЦЕНА%"" И ТипЗначения(Слово) = Тип(Число)"
- Далее, пишем по одному запросу к каждому типу значений (один запрос — количество, второй — наименование, третий — сумма и т.д.), используя в них конструкции типа Слово.НомерСтроки В (&НомераСтрокОпорныхЗначений).
- Если нужно получить одно значение, а получить четко одно невозможно — можно приоритезировать результаты поиска по каким-то критериям, например, по номеру колонки или длине строки (при программном использовании доступна также установка фразы УПОРЯДОЧИТЬ ПО) Вроде как не совсем красиво выбирать наименование между "шт" и "Туфли женские" опираясь на длину строки, но мне не встречалась ситуация, когда такой критерий подводил.
Есть и более простой вариант: написать по одному запросу, каждый из которых вернет значения всех соответствующих областей независимо ни от какого другого, но при таком подходе существенно возрастает риск, что запрос "зацепит" какие-то данные, которые вы не планировали получать, хотя если в консоли такие запросы успешно обкатаются — можно и так.
Код полностью открыт, запароленных и/или обфусцированных участков кода нет. Писал на платформе 8.3.9.2233. Удачи!
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
Почерпнул несколько идей, есть оригинальные моменты, спасибо.
А можно что-то типа мастер-класса для тупых? мол, «вот задача, вот файл эксель, а вот так мы теперь сможем делать»…
А то звучит круто, но непонятно. Жажду демонстрации на живых
людяхданныхКак-то сталкивался с проблемой «плавающих колонок». пошел от обратного и сделал обработку с пользовательской настройкой.
1) Пользователь читает файл с таблицей
2) Выбирает на каждый предопределенный параметр область ТД. (К примеру контрагент у меня вот в этой ячейке, а колонка с номенклатурой тут. А вот тут сумма и цена)
3) Обзывает свое творение «ТОРГ-12 от Рога и копыта»
4) Загружает
При открытии очередного файла с таблицей, происходит поиск наиболее подходящей из сохраненных настроек (Проверялись предопределенные Дата, Контрагент, колонка номенклатуры, Сумма, цена по нескольким алгоритмам).
Если таких много, то дает выбрать. Так же дает выбрать, если поиск не увенчался успехом (К примеру критерий того же контрагента провалился, а на самом деле просто формат текста был неверный)
Если приходит что-то новое, пользователь начинает настраивать новую настройку. (Зато сам видит, что изменились колонки/ячейки, а не «программист плохой снова денег хочет»)
Во людям делать не фиг 🙂
(2) Непонятно в чем вопрос, если пример использования в техногенном смысле — консоль как раз реализует программный интерфейс. Если в более прикладном смысле — то суть то весьма проста — берем эксель и разбираем где цена где количество, где артикул и записываем в документ.
Автор молодец.
В реальной жизни можно проще.
Для названий колонок мне хватило массивов подстрок по каждой колонке и разбор наименований колонок в двух строках.
Что-то нестандартное пришлось придумывать для разделения артикула и наименования номенклатуры.
Всё.
(3) все привыкли вбивать параметры «номер первой строки товары» и «номер колонки цены» цифрами, ваш вариант гораздо более юзерс-френдли. а если его еще раскрасить разными цветами (цена фиолетовым, количество зеленым…) — наверно весьма футуристично будет выглядеть ))
(6) кстати да, артикул из наименования у меня тоже выделяется. это самая спорная часть функциональности, поэтому в основной статье не стал писать, но раз уж вы затронули эту тему: наименование номенклатуры помещается в отдельный запрос в варианте «ПоСловам», далее ищутся слова, содержащие цифры (в моем варианте в артикуле всегда есть хотябы одна цифра) и самое длинное слово считается артикулом.
(7) Цветасто было) Как раз для выбора существующего варианта подкрашивались области, но вообще идея в целом провальная.
В целом закончилось все тем, что «программист плохой». Воткнулось все в алгоритмы поиска данных. Только для поиска номенклатуры было где-то 15 штук, и этого оказывалось мало. По артиклу, штрих-коду, наименованию, парсингу Наименование+характеристика (штук 5 кажется)…… Поэтапный поиск. Комбинированные. И в итоге вариантов становилось больше. Сначала делал все новые и новые функции, после решил, что возможность загрузки алгоритма через тхт (чтобы на коленке можно было бы им отправить тхт с новой функцией, которую можно было бы вставить в Выполнить(ТекстИзФайла)). После решил, что иметь регистр сведений с «Контрагент», «Номенклатрура», «Как выглядит в ехсел» куда дешевле, чем исполнять сотни запросов. Оказалось нужно хранить для разных баз. В итоге родилась конфигурация к которой через COM (в перспективе планировался web-сервис) можно было получить гуид номенклатуры или контрагента по текстовому представлению. ( к счастью до этого не дошло, развитие этого франкенштейна окончилось)
По зависимостям: только платформа или есть ещё БСП etc?
(10) только платформа
все равно все алгоритмы — эмпирические. без предварительного визуального исследования человеком — не разобрать. а хотелось бы сказать «торг12» и получить на выходе «линенынй данные»
Красно было на бумаге, да забыли про овраги, а по ним ходить…
Так я и не уловил, для каждой ячейки будем делать проверку, а нет ли где-то там там выше, ячейки со словом цена? Это же сколько проверок будет для 1000 строк и 20 колонок?
А потом, окажется, что там не «цена», а «Цена», а еще окажется, что и в другой колонке тоже присутствует слово цена. А потом, окажется, что под табличной часть сначала итоговые значения, а затем и вовсе подвал, и из него еще подтянутся значения.
Разбор структуру данных — это всегда такой увлекательный процесс, даже когда видишь структуру перед глазами, и пишешь код под конкретную структуру, получается не с первого раза, потом еще несколько месяцев всплывают особенности.
Допускаю, что возможно, есть какая-то задача, для которой подходит решение предложенное в данной статье,
(12) да, все так. думаю, что можно построить некую «матрицу» в которой будут предопределенные настройки запросов для каждого вида входящих документов и предопределенные параметры записи в объект системы, но это уже следующий «слой».
(13)
про 1000 строк — вопрос интересный, теоретически понятно, что язык запросов, на котором все построено способен переваривать и не такие объемы, но на всякий случай проверил, нашел документ на 1300 строк, результаты такие:
— обработка строк (см. разборщик входящих данных) работает порядка 45 секунд. это вообще довольно затратная с точки зрения производительности история.
— запрос в том виде, в котором он приведен выше выполняется около 120 сек.
— если выкинуть из него условие по наличию справа числа (цены) — запрос выполняется около 5 сек.
— если добавить условие на значение поля ниже — запрос работал 10 минут, я не дождался.
таким образом, имеет место экспоненциальный рост времени выполнения в зависимости от количества используемых таблиц (что логично), но в целом, для условий фоновой обработки и очень большого документа для разора (99% документов, это все-таки до 100 строк) — результат считаю удовлетворительным.
Мда уж) Время идет а велосипеды у нас все придумывают)
Сделано 10 лет назад, живое видео
Так же сталкивался с такими задачами и искал оптимальное решение. Склонился все же к пути 1С. Вставляем данные из Excel в табличный документ и в нем уже определяем где и что. Пример в БП 3.0 в справочнике номенклатура Загрузить
(17) не очень себе решение. Учитывая что из УПД, Торг12 — не так просто и колонку скопировать с «чистыми» данными — все будет с лишним мусором. И колонок там много разных.
И если номенклатура отличается и нет записей в номенклатуре поставщиков — ничего не найдет.
Точно также как не распознает половину добавочных данных типа единиц (которые могут быть у поставщика криво названы), ндс, и прочего.
Ну и напоследок не сможет работать с характеристиками.
Подходит исключительно когда нет характеристик, номенклатура 100 процентное совпадение, единицы и все остальное — тоже самое.
Короче под идеальную таблицу, номенклатуру и тп…
Много ручной работы и ковыряние со вставками колонок — где же тут автоматизация.
(17) (16)
в первую очередь решение предлагается для автоматической обработки. то, что вы видите в консоли — это всего лишь метод проверки написанного запроса. далее, запрос должен работать сам по себе, без пользователей, которые ему подсказывают.
Вопрос рядом с темой. При автоматической обработке файла через регламентное задание СОМ-соединение читает файлы XLS не всех форматов и это усугубляется наличием сервера 1С на 64-разрядах. Как в этом случае быть? В моём случае контрагенты шлют файлы на почту и нет возможности заставить их менять форматы, удобные нам. Приходится эти «неправильные» файлы разбирать запуском клиентского приложения через планировщик операционной системы.
(20) а что в поиске никаких готовых решений нет? Мне кажется загрузок уже несколько тысяч.
(18) Автоматизация ручной загрузки из excel — ну как сказать… Кто-то же сделал ручную работу на входе — на выходе получаем что получаем. Есть же стандартизированные форматы, протоколы, EDI, EnterpriseData…
(5) пошаговый пример: как взять Вашу консоль и с ее помощью разобрать какой-нибудь excel?
Будем, будем. Подсмотрите, например, как в типовой реализована загрузка номенклатуры из файла.
(20)ADODB. Установка одновременно 32 и 64 версии AccessDatabaseEngine на сервере 1с. + 64х битный обычный офис. Читаем файл ADODB, если проблема «unexpected format», делает пересохранение через excel.application и заново открываем ADODB. На практике читает 100% текстовых и 95% эксельных файлов, которые шлют разные поставщики. (5% — обычно не «родной» эксель — а программно сформированный с косяками из всяких кривых приложений)
А как будет работать, если, например, нужна цена, а этих несколько? Цена начало конец акции, полки, закупки, продажи и т.п.?
(26) Если вы можете определить значение через его окружение — значит у вас все получится, нужно только аккуратно сформулировать правила. В вашем случае, полагаю, поможет анализ названий колонок, если цен фиксированное количество, или выбор минимального (максимального) значения, если количество цен заранее неизвестно.
Идея конечно интересная, но только для самых простых случаев. «Загрузка здорового человека» должна из одной колонки делать запись в номенклатуру поставщика, тут же создавать номенклатуру с определенным видом, определять нужна ли характеристика, знать правила именования в трёх справочниках, привязывать их к номенклатуре поставщика, ориентироваться какие доп. реквизиты и свойства необходимы но не заполнены, а какие даром не нужны, делать проверку по штрихкоду «а ну как у нас такое уже есть» наплевав на наименование, понимать данные в каких колонках пойдут в допсвойства, а в каких в реквизиты, уметь создавать наборы допреквизитов как номенклатуры, так и характеристик и прочее прочее прочее
В нашем случае пишется однократно парсер под каждого поставщика, который как раз и раскидывает вышеперечисленное добро в универсальный файл загрузки нужного формата, а оттуда уже стартует «магия»
Создание «всего и сразу», да «на лету» с таким уровнем доверия к входящим данным -ну, не знаю.
(28) Вы говорите о том, что у вас сложный алгоритм работы с полученными данными. Надо много всего проверить в базе данных, создать несколько объектов и т.п. Иными словами, вы говорите что у вас сложности на этапе использования данных.
Предложенный метод же отвечает на вопрос о том «как данные взять», а не о том «как данные использовать».
(29) Да если бы не заголовок — и зануду бы не включал. Способ наверное прекрасно подходит для загрузки многоообразия табличных вариантов Торг-12, но я не вижу как его приспособить для загрузки прайсов и свойств номенклатуры. Как в вашем алгоритме будеть обработано объединение ячеек? Пример на картинку. Справится ли чудо запрос c конструкцией «Если наименование поля включено в подшапку Свойства Харакреристики, то положить его в ячейку Характеристика_ЭтоСвойство_ИмяСвойства?
(25) Вот из-за этих 5% пришлось делать запуск через планировщик для чтения не прочитанных с первой попытки файлов кривого формата через excel.application
(30) куда ж без колхозного маркетинга?! )))
над вопросом с объединением я на самом деле голову ломал не одну неделю. в итоге оставил «по умолчанию»: значение будет только в левой верхней ячейке. второй вариант был — заполнять соответствующим значением все объединенные ячейки. наверно, нужно было сделать это опцией.
а что касается прайс-листа — то в нем вероятно не одна тысяча строк — для такого этот инструмент действительно ограниченно годен, поскольку сильно деградирует производительность с ростом объема файла (там, как вы понимаете все на полных соединениях)
(3) у меня практически точно также сделана загрузка в 77.
визуально выбирает колонки табличногодокумента(йоксель, прочитан эксель) соответсвующие предопределенным параметрам. Получаем схему данных. Эта настройка привязывается к контрагенту (или юзер простовыбирает схему из перечня схем). выбранную/привязаную схему можно оценить — показывает «раскраску» документа по текущей схеме. жмакает юзер загрузить, дальше как обычно.
конечно это исключительно интерактивная работа.
слава богу что таких «обменов данными» все меньше…