Сравнение данных из разных источников (1С 8, SQL, CSV/TXT/DBF/XLS/XML, табличный документ)

Обработки - Универсальные обработки

Обработка Консоль сравнения данных из разных источников предназначена для сравнения данных, полученных из разных источников данных (информационных баз 1С 8, баз данных SQL, файлов формата CSV/TXT/DBF/XLS/XML, вручную заполненного табличного документа).

Обработка Консоль сравнения данных из разных источников предназначена для сравнения данных, полученных из разных источников данных (информационных баз 1С 8, баз данных SQL, файлов формата CSV/TXT/DBF/XLS/XML, вручную заполненного табличного документа).

ВНИМАНИЕ: Актуальная инструкция всегда доступна во встроенной справке обработки по клавише F1.

Сравнение можно производить между данными любых источников: текущей, внешними базами 1С 8, внешними базы данных SQL, файлами CSV/TXT/DBF/XLS/XML, данными табличного документа. В каждом сравнении участвуют выборки из двух баз данных, к каждой из которых должен быть написан запрос на языке 1С для информационной базы 1С 8 либо SQL для базы данных SQL. После этого оба запроса выполняются, а полученные таблицы значений сравниваются между собой по ключу, который определяется как первая колонка  (или первая и вторая колонки в случае составного ключа) запроса.  В верхней части формы находятся кнопки с изображениям кругов Эйлера, предназначенные для указания обработке, какую операцию реляционной алгебры применять для объединения результатов запросов. Выбранная операция подсветится зеленым цветом.

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

  • Тип базы – реквизит может принимать следующие значения:
    • текущая база 1С 8 – в этом случае никаких дополнительных параметров подключения к базе данных заполнять не нужно;
    • внешняя база 1С 8 – внешняя база 1С 8, доступная из текущей подсети, в этом случае необходимо заполнить параметры Версия платформы, Вариант работы, Путь к базе или Сервер/Имя базы, Логин и Пароль;
    • база данных SQL – база данных SQL;
    • файл – файл формата CSV/TXT/DBF/XLS/XML;
    • таблица - табличный документ, расположенный на форме;
  • Вариант работы – вариант работы информационной базы, может принимать следующие значения:
    • файловый – внешняя база размещена в файле, в этом случае необходимо заполнить Путь к базе, Логин и Пароль;
    • клиент-серверный – внешняя база размещена на сервере приложений 1С, в этом случае необходимо заполнить Сервер, Имя базы, Логин и Пароль;
  • Версия платформы – версия платформы 1С 8, отвечает за выбор COM-компоненты, используемой для подключения к базе;
  • Путь к базе – путь к файловой базе;
  • Драйвер – имя драйвера для подключения к базе данных SQL, можно выбрать из списка предопределенных (SQL Server, PostgreSQL);
  • Сервер – имя сервера приложений 1С или сервера баз данных;
  • Имя базы – имя базы на сервере приложений 1С или базы данных SQL;
  • Логин – логин пользователя внешней базы 1С (у пользователя должно быть право внешнего соединения) или пользователя базы данных SQL;
  • Пароль – пароль пользователя внешней базы 1С 8 или пользователя базы данных SQL:
  • Приводить ключ к верхнему регистру – символы нижнего регистра, входящие в первичный ключ, будут преобразованы в символы верхнего регистра;
  • Удалять из ключа фигурные скобки – символы { и } будет заменены в ключе на пустые строки;
  • Использовать в качестве ключа УИД – первичный ключ будет сформирован путем вызова функции УникальныйИдентификатор() из первой колонки, которая должна быть ссылочного типа;
  • Текст запроса – текст запроса на языке 1С для баз 1С 8 (доступен конструктор запроса) и на языке SQL для баз данных SQL;
  • Пользовательский режим – если флаг установлен в Истина, то при открытии формы все реквизиты настроек скрываются, остаются видимыми только команда сравнения, команды открытия сохраненных настроек и результат сравнения.

В таблице Правила заполнения реквизитов, предназначенной для задания соответствия колонок источников данных реквизитам результирующей таблицы или произвольного кода, необходимо заполнить следующие реквизиты:

  • Реквизит А/Б – номер реквизита соответствующего источника данных; 
  • Номер колонки – номер колонки CSV/TXT/DBF/XLS файла;
  • Имя колонки – имя колонки XML-файла;
  • Произвольный код – произвольный код для заполнения текущего реквизита (обращение к значениям реквизитов осуществляется по именам Р1...Р5, обращение к текущему - по имени РТек, пример для реквизита 3: РТек = Окр(Р1 * Р2 * 1.2,2)).

Для использования составного ключа необходимо установить флаг Использовать составной ключ из 2 столбцов на вкладке Общие настройки -> Основные, при этом ключ будет определяться на основании первых двух колонок каждой выборки для баз 1С и SQL, а для файлов необходимо явно указать номер колонки, соответствующей каждому столбцу ключа.  

Для обеспечения возможности сравнения данных по периодам можно использовать предопределенные параметры ДатаНачала и ДатаОкончания, которые можно задать на вкладке Общие настройки -> Основные как в виде абсолютного значения периода, так и виде относительного значения с указанием дискретности (год, месяц, день). Относительный период удобен при ежедневном контроле данных, т.к. период в этом случае рассчитывается автоматически относительно текущей даты.

При выводе каждой строки (т.е. уже после применения операции реляционной алгебры) производится проверка условий вывода и условий запрета вывода, расположенных в табличной части, и если не выполняется условие вывода или выполняется условие запрета вывода, то строка удаляется из таблицы значений. Данный механизм игнорируется только для строк, в которых в одной из баз найдено больше одной строки для данного первичного ключа, т.к. в этом случае сравнение бессмысленно. Параметры, отвечающие за вывод и запрет вывода строк, заполняются на вкладке Общие настройки. На вкладках Условия вывода строк и Условия запрета вывода строк можно заполнить следующие реквизиты:

  • Логический оператор для объединения условий – оператор объединяет все строки из табличной части;
  • Код редактируется вручную – при установке флага код становится доступным для редактирования;
  • Многострочное поле ввода без имени – произвольный код, формируемый автоматически на основании строк табличной части, может редактироваться вручную, выполняется для каждой строки таблицы значений Результат сравнения.

      Реквизиты табличных частей заполняются следующим образом:

  • Реквизит – имя реквизита таблицы значений Результат сравнения;
  • Условие – условие сравнения;
  • С чем сравнивать – определяется режим сравнения, может принимать значения Реквизит и Значение;
  • Значение – заполняется в случае, если реквизит С чем сравнивать равен Значение, в данном режиме значение реквизита таблицы значений сравнивается с указанным в данном поле значением;
  • Реквизит – заполняется в случае, если реквизит С чем сравнивать равен Реквизит, в данном режиме значение реквизита таблицы значений сравнивается со значением реквизита, указанного в данном поле.

В результате выполнения произвольного кода с вкладки Условия вывода строк устанавливается значение переменной УсловияВыводаСтрокиВыполнены, а с вкладкиУсловия запрета вывода строк - значение переменной УсловияЗапретаВыводаСтрокиВыполнены. Строка останется в таблице значений Результат сравнения, еслиУсловияВыводаСтрокиВыполнены = Истина, а УсловияЗапретаВыводаСтрокиВыполнены = Ложь. Необходимо также помнить, что если по ключу хотя бы в одной из баз найдено более одной строки, то условия игнорируются, т.к. такие сроки должны быть отображены для выявления причин ошибки.

После настройки всех параметров необходимо нажать кнопку кнопку Сравнить данные, обработка выполнит оба запроса, объединит их в соответствии с выбранной операцией реляционной алгебры, выведет в таблицу значений, затем отфильтрует в соответствии с условиями вывода и запрета вывода строк, после чего отобразит ее на форме на вкладке Результат сравнения, которая содержит следующие колонки:

  • Ключ – ключ, по которому сопоставляются выборки;
  • Количество записей А - количество записей, найденных в базе А по ключу;
  • Количество записей Б - количество записей, найденных в базе Б по ключу;
  • Реквизит А1...А5 – реквизиты выборки из базы А;
  • Реквизит Б1...Б5 – реквизиты выборки из базы Б.

В случае, если число строк с данным первичным ключом в базе X больше одной, то в колонке X, хранящей количество строк, найденных по данному первичному ключу, выводится число найденных строк, а реквизиты X1 - X5 заполняются данными из первой попавшейся строки внешней БД (для облегчения последующего поиска ошибки), и подсвечиваются розовым цветом. Такие строки должны отсутствовать, если первичный ключ выбран правильно.

При подключениях к информационным базам 1С 8 доступен обычный конструктор запросов (команда Конструктор запросов расположена на вкладках, соответствующих настройкам баз 1С 8).

Для обеспечения возможности хранения настроек сравнения в базе необходимо добавить в конфигурацию справочник НастройкиКонсолиСравненияДанных с реквизитом Настройка типа ХранилищеЗначения.

Буду благодарен, если будете уведомлять о багах на sertakov [а] list.ru или в комментариях.

Скачать файлы

Наименование Файл Версия Размер
Консоль сравнения данных из разных источников (1С 8, SQL, CSV/TXT/DBF/XLS/XML, табличный документ)
.epf 109,89Kb
23.05.18
51
.epf 8.1.25 109,89Kb 51 Скачать
Консоль сравнения данных из разных источников (1С 8, SQL, CSV/TXT/DBF/XLS/XML, табличный документ)
.epf 111,55Kb
23.05.18
0
.epf 8.2.26 111,55Kb Скачать

См. также

Комментарии
1. Лёша Жегздрин (alexzhegzdrin) 15.02.17 16:28 Сейчас в теме
Неплохо бы увидеть возможность выбора драйвера, отличного от MS SQL
2. Виталий Сертаков (sertak) 53 15.02.17 16:58 Сейчас в теме
(1) Версия 4.4.12: драйвер можно указать вручную, можно выбрать из списка (SQL Server, PostgreSQL).
3. Виталий Сертаков (sertak) 53 16.02.17 23:11 Сейчас в теме
Версия 5.0.13:
добавлена возможность сравнения с файлами CSV/TXT/DBF/XLS.

Теперь можно сравнивать данные из любых двух источников:
- текущая база 1С 8;
- внешняя база 1С 8;
- база данных SQL Server;
- база данных PostgreSQL;
- другая база SQL через вручную указанный драйвер;
- файл формата CSV/TXT/DBF/XLS.
4. Виталий Сертаков (sertak) 53 20.02.17 12:24 Сейчас в теме
Версия 6.1.15:
добавлена возможность использования составного ключа из двух колонок;
исправлена ошибка, из-за которой невозможно было извлечь УИД из ссылки из внешней базы 1С 8.
5. Виталий Сертаков (sertak) 53 20.02.17 15:47 Сейчас в теме
Версия 6.1.16:
исправлена ошибка.
6. Виталий Сертаков (sertak) 53 04.03.17 00:15 Сейчас в теме
Версия 7.0.17:
реализована возможность загрузки данных из табличного документа.

Версия 7.0.18:
исправлена ошибка.
7. Виталий Сертаков (sertak) 53 12.03.17 19:27 Сейчас в теме
Версия 7.1.19:
увеличена длина и точность типа Число реквизитов.
8. Олег zerdov (zerdov) 46 24.05.17 17:00 Сейчас в теме
Как внести значения параметра Период в запросе к ВТ регистра сведенийЦеныНоменклатурыСрезПоследних ?
Такое впечатление, что запрос работает с обычной таблицей ЦеныНоменклатуры. Выводит даты документов- регистраторов.
9. Виталий Сертаков (sertak) 53 25.05.17 12:26 Сейчас в теме
10. Виталий Сертаков (sertak) 53 25.05.17 12:32 Сейчас в теме
(8)
1)
 ВЫБРАТЬ
	ЦеныНоменклатурыСрезПоследних.Номенклатура,
	ЦеныНоменклатурыСрезПоследних.Цена
ИЗ
	РегистрСведений.ЦеныНоменклатуры.СрезПоследних(ДАТАВРЕМЯ(2017, 1, 1), ) КАК ЦеныНоменклатурыСрезПоследних


2)
ВЫБРАТЬ
	ЦеныНоменклатурыСрезПоследних.Номенклатура,
	ЦеныНоменклатурыСрезПоследних.Цена
ИЗ
	РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаНачала, ) КАК ЦеныНоменклатурыСрезПоследних


а на вкладке консоли Общие настроки -> Основные нужно в левую границу периода внести нужную дату среза цен
11. Олег zerdov (zerdov) 46 25.05.17 16:14 Сейчас в теме
(10)
ДАТАВРЕМЯ(2017, 05, 25)

Спасибо! Я по второму варианту строил запрос. Мое "впечатление" было ошибочным, периоды и должны были быть разными, главное что бы были последними ).
ВЫБРАТЬ
	ЦеныНоменклатурыСрезПоследних.Номенклатура.Код КАК НоменклатураКод,
	ЦеныНоменклатурыСрезПоследних.Период КАК Период,
	ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
	ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура
ИЗ
	РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаОкончания, ) КАК ЦеныНоменклатурыСрезПоследних
12. Виталий Сертаков (sertak) 53 16.11.17 15:55 Сейчас в теме
Версия 7.1.20:
исправлена ошибка, из-за которой в некоторых случаях сравнение по составному ключу производилось некорректно.
13. Виталий Сертаков (sertak) 53 17.11.17 22:24 Сейчас в теме
Версия 7.2.21:
реализована возможность использования параметров запроса при подключении к текущей базе 1С 8 (параметры ДатаНачала и ДатаОкончания берутся только с вкладки Общие настройки -> Основные даже в том случае, если одноименные есть на вкладке Параметры запроса);
исправлена ошибка, из-за которой в настройках не сохранялся номер первой строки файла.
14. Виталий Сертаков (sertak) 53 20.02.18 11:56 Сейчас в теме
Версия 8.0.22:
реализована возможность загрузки данных из файлов формата XML;
исправлена ошибка, из-за которой в некоторых режимах некорректно определялся список обязательных к заполнению полей;
доработан вывод сообщений об ошибках заполнения реквизитов.
15. Erne100 (Erne100) 219 26.03.18 11:23 Сейчас в теме
А параметров запроса Б нет? Хотя бы примитивный типов... для начала
17. Виталий Сертаков (sertak) 53 26.03.18 14:52 Сейчас в теме
Вы имеете в виду ситуацию, когда Б - внешняя база 1С, видимо. Не реализовывал пока.
18. Erne100 (Erne100) 219 26.03.18 15:59 Сейчас в теме
(17) Да именно это. А пока делаю так:
ГДЕ
	РеализацияТоваровУслуг.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
	И РеализацияТоваровУслуг.Организация.ИНН = "32177162"
19. Роман Марков (m-rv) 282 04.05.18 14:49 Сейчас в теме
Оч.крутая штука!
Как мысль для дальнейшего развития: нужен какой-то а-ля пользовательский режим, в котором не видны настройки подключений, запросы, но есть возможность редактировать режим "таблица". т.е. программист все настроил, сохранил настройки, потом пользователь просто открывает настройки, сравнивает и смотрит на результат.
20. Виталий Сертаков (sertak) 53 04.05.18 15:51 Сейчас в теме
Спасибо, сам к ней неравнодушен ).
Сохранение/открытие настроек есть, пользователю достаточно сделать 2 действия: выбрать настройку и нажать кнопку сформировать, даже период будет сам пересчитываться, т.к. его можно задать в единицах времени относительно текущей даты. Напишите подробнее мысль, заинтересовали.
24. Виталий Сертаков (sertak) 53 14.05.18 09:04 Сейчас в теме
21. Виталий Сертаков (sertak) 53 05.05.18 07:25 Сейчас в теме
Версия 8.0.23:
исправлена ошибка, из-за которой при наличии пробелов в ключе могли не заполняться реквизиты.
22. Виталий Сертаков (sertak) 53 08.05.18 11:47 Сейчас в теме
Версия 8.0.24:
исправен запрет на имя колонки выборки запроса: теперь колонку можно называть Количество.
23. Виталий Сертаков (sertak) 53 14.05.18 09:04 Сейчас в теме
Версия 8.1.25:
добавлена возможность открытия формы в пользовательском режиме, где пользователь может только выбирать сохраненные настройки и производить сравнение, для включения режима достаточно установить флаг Пользовательский режим в программно созданную обработку либо передать этот флаг как параметр при открытии формы (ОткрытьФорму("Обработка.ВС_КонсольСравненияДанныхИзРазныхИсточников.Форма", Новый Структура("ПользовательскийРежим", Истина), ПараметрыВыполненияКоманды.Источник, Новый УникальныйИдентификатор)).
П.С. Спасибо Маркову Роману за идею.
25. Виталий Сертаков (sertak) 53 23.05.18 11:40 Сейчас в теме
Версия 8.2.26:
добавлена возможность программного заполнения реквизитов с помощью произвольного кода при чтении данных из файлов (обращение к значениям реквизитов осуществлется по именам Р1...Р5, обращение к текущему - по имени РТек, например, для реквизита 3 может быть задан такой произвольный код: РТек = Р1 * Р2 * 1.2).
Оставьте свое сообщение