Настройка MS Excel для анализа данных из SQL-базы данных 1С

09.10.07

Интеграция - Загрузка и выгрузка в Excel

Приведен пример реализации механизма прямого доступа к базе данных 1С на SQL-сервере с целью извлечения необходимых данных для получения аналитических отчетов.
Основными достоинствами данного метода являются:
1. Очень высокая скорость выборки данных.
2. Использование всех возможностей сводных таблиц.
Наиболее серьезным недостатком является сложность организации анализа данных в процессе выполнения запроса.
Пример реализован с помощью "MS Excel 2007"
 
Общий порядок действий:

1. Написать на понятном SQL-серверу языке запрос, выбирающий все необходимые данные из БД 1С.
Это конечно можно сделать и ручками, а можно использовать для построения запроса "Конструктор SQL-запросов 1C++" (Freeware 2005, 2006 by Ivan T Berezdetsky). Конструктор полностью копирует внешний вид и функциональность конструктора из восьмерки, поэтому трудностей с его использованием быть не должно. Полученный через конструктор текст запроса нельзя использовать в таком виде, т.к. он еще требует "перевода" имен таблиц, полей и т.д. Для этого можно воспользоваться "Просмотр регистров 1С++ без ВТ.ert" (к сожалению автора не помню), если установить галку "Фиксировать" и "Debug", а также вставить имеющийся текст запроса, то обработка выдаст нам уже готовый к использованию текст SQL-запроса. Для большей надежности рекомендую проверить его работоспособность прямо в консоли запросов SQL-сервера.
При формировании текста запроса необходим учесть то, что SQL сервер не умеет типизировать объекты 1С, а значит, например, нельзя просто выбрать измерения регистра продажи, обязательно необходимо делать связи между измерением "Номенклатура" и таблицей "номенклатура", а выбирать наименование из справочника. Если этого не учесть, то вместо наименований объектов в Excel будут непонятные надписи типа " 9N2Х", которые является идентификаторами объектов в БД.

Пример правильного текста запроса:

SELECT
Номенклатура.DESCR AS [1. Номенклатура],
Продажи.SP2375 AS [2. Количество],
Продажи.SP2350 AS [3 Продажная стоимость],
Продажи.SP2349 AS [4. Себестоимость]
FROM
dbo.RA2351 AS Продажи INNER JOIN
dbo.SC84 AS Номенклатура ON Продажи.SP2343 = Номенклатура.ID INNER JOIN


2. Необходимо создать файл подключения к БД.

Для этого можно в моих документах найти папку "Мои источники данных", в ней на "+Новое подключение к SQL-серверу.odc" щелкнуть правой, выбрать "Изменить в Excel". Далее необходимо ввести имя SQL-сервера, логин и пароль (рекомендуется создать служебного пользователя, имеющего права только на чтение в SQL-базе), далее выбрать необходимую БД и снять галку "Подключение к отдельной таблице", далее необходимо сохранить настройки подключения в файл. Затем при запросе таблицы, к которой мы хотим подключиться, щелкаем на любой. В появившемся окне меняем тип команды на SQL, а в поле "Текст команды" вставляем текст SQL-запроса, полученный ранее. Еще раз сохраняем файл подключения, и на этом Excel должен закрыться.

3. Непосредственный импорт данных.

Открываем Excel, нажимаем "Данные" - "Получить внешние данные" - "Существующие подключения", по-идее созданный нами файл подключения должен быть в списке, но если его нет - можно воспользоваться кнопкой "найти другие".
Дважды на нем щелкаем, появляется окно с запросом способа представления данных. Выбираем сводная таблица, нажимаем "ОК" и все! В зависимости от объема данных, рано или поздно сводная таблица будет сформирована и представлена пользователю.

Для примера, база 4 Гб, количество реализаций за день около 1000, среднее количество позиций в документе 10 шт. За 3 года запросом выбирается около 1 млн. записей. Общее время формирования сводной таблицы, учитывая не только время выборки но и обновление структуры сводной таблицы порядка 90 секунд!!!

В дальнейшем можно сохранить Excel'евский файл, создать в нем несколько различных сводных таблиц с одним источником данных. При обновлении данных обновляются сразу все сводные таблицы.

См. также

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 руб.

29.10.2014    209832    620    524    

438

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150387    365    375    

499

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

5400 руб.

04.06.2019    101140    296    173    

312

Загрузка данных отчета о реализации товаров из Excel файла СберМегаМаркет

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11 и Управление нашей фирмой, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

3600 руб.

07.06.2022    14956    78    0    

59

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в Excel Оптовая торговля Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 руб.

09.11.2016    214616    921    886    

938

Универсальная загрузка остатков и цен поставщиков в 1С (УТ 11, КА 2, ERP 2.0) из файлов Excel, CSV, XML (YML и CML)

Прайсы Оптовая торговля Розничная торговля Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Загрузка и выгрузка в Excel Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

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

12000 руб.

23.01.2017    58796    59    48    

67
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. German 413 09.10.07 09:34 Сейчас в теме
в достоинства забыл добавить универсальность
user756416; +1 1 Ответить
2. CheBurator 3119 09.10.07 12:37 Сейчас в теме
угум... думаю востребовано будет там, где руководство само не знает чего хочет...
3. Aleksey.Bochkov 3659 09.10.07 14:16 Сейчас в теме
(2) Не только. Думаю что это достаточно серьезная альтернатива OLAP кубам
4. German 413 09.10.07 14:57 Сейчас в теме
Альтернатива то она может и есть .. Только очень редкие люди использующие 1С имеют в этом необходимость и вообще представляют себе что такое олап
5. avhrst 518 22.10.07 09:46 Сейчас в теме
тема актуальна, особенно при использовании отдельного скл сервера скорость прямого доступа по сравнению с 1С быстрее в разы! плюс
6. logarifm 1116 18.12.07 14:46 Сейчас в теме
в достоинства забыл добавить универсальность
Со старта -1, видимо вы не понимаете, что речь идет об прямых запросах через консоль.

Универсальность можно сделать в 1С++, ToySQL

Тема актуальна...
7. German 413 18.12.07 14:55 Сейчас в теме
(6) как же не понимаю ..:) очень! хорошо понимаю...

Универсальность для любого типа данных.
Универсальность в версиях 7 и 8*.

А уж ваши 1С++, ToySQL далеко не универсальны.
8. JohnyDeath 301 18.12.07 15:16 Сейчас в теме
(7) Ну как это не универсальны?
Например, есть две одинаковые КОНФИГУРАЦИИ, но разные базы. В одной базе в регистре "Продажи" измерение "Количество" в таблице представляетсяполем SP1111, а в другой - SP2222. Т.о. получается, что один и тот же отчет я немогу таскать по разным клиентам. А имея 1с++, я могу в запросе написать просто: $Продажи.Количество и метапарсер сам подставит нужное значение и отдаст уже чистый запрос серверу!
9. AlexO 135 11.01.12 11:35 Сейчас в теме
>Для примера, база 4 Гб, количество реализаций за день около 1000, среднее количество позиций в документе 10 шт. За 3 года запросом выбирается около 1 млн. записей.
Либо база не 4ГБ, либо - не три года заполнялась... 4ГБ SQL-база для 1С8 - это, можно сказать, демобаза :)
Или здесь 7.7 база? в теме не указано.
(8) где взять этот метапрасер и как им вопользоваться?
Оставьте свое сообщение