Форум пользователей Visio

Форум по вопросам применения и программирования в Visio
Текущее время: 28 мар 2024, 13:00

Часовой пояс: UTC + 3 часа [ Летнее время ]


Правила форума


При размещении файлов предпочтительным является формат vsd (а не vsdx/vsdm)
Размещая ваши вложения на форуме не используйте имена файлов содержащих кириллицу, в противном случае файл будет иметь имя .<расширение файла> !

Для форматирования ваших сообщений используйте BBCodes, описание используемых на форуме BBCodes.



Начать новую тему Ответить на тему  [ Сообщений: 56 ]  На страницу Пред.  1, 2, 3
Автор Сообщение
 Заголовок сообщения: Visio + Excel Power Query
СообщениеДобавлено: 10 мар 2019, 12:48 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Сегодня, впервые, я хочу не задать вопрос, а поделиться одним полезным открытием. Сразу оговорюсь, что данный пост будет понятен тем, кто знаком с Power Query и, знакомых со структурой vsdx файла, как набора файлов XML. Но всё же стоит напомнить что такое Power Query (далее PQ) - это стандартная надстройка Excel, которая по умолчанию поставляется с версией 2016, и позволяет получить данные практически из любого источника, и изменить их так как надо (раньше она так и называлась Get & Transform).
Всё началось с того, что наткнулся на статью, как можно извлечь данные из zip архива с помощью PQ (http://sql10.blogspot.com/2016/06/readi ... ery-m.html). И тут я вспомнил, что vsdx файл - это по сути zip архив, который содержит набор xml файлов, и решил попробовать, решение, указанное выше, как результат - получилось, PQ увидел все XML файлы внутри vsdx файла, но увидел их как Binary файлы. Но у PQ есть функция Text.FromBinary, которая, собственно, извлекает весь текст любого файла (в нашем случае XML). Далее, можно напрямую работать с текстом, но можно пойти чуть дальше. PQ так же умеет работать с XML документами и разбивать их структуру на таблицы с помощью функций Xml.Document или Xml.Tables. Ну а далее - дело техники, вашего умения работать с PQ и вашего воображения, так как PQ это мощнейший инструмент, который удивляет меня всё больше и больше.
В качестве примера прикладываю Zip архив с двумя файлами Visio и Excel. Последний содержит данные, которые подключены к файлу visio, другие страницы содержат таблицы, которые отображают все шейпы на всех страницах, которые содержат пользовательские данные, а вторая - шейпы с данными, которые имеют связь с данными. Если в visio добавить ещё шейпов с данными, сохранить документ, перейти в Excel и обновить все данные (на вкладке данные), то таблицы обновятся - и всё это без единой строчки на VBA.
Таким образом, получаем мощный (масштабируемый, так как PQ может обрабатывать сразу несколько файлов) и гибкий инструмент для извлечения данных из visio.
Данным постом я не предлагаю готового решения, я лишь хотел показать, что есть такая возможность извлечения данных. Надеюсь будет кому-то полезно.


Вложения:
Комментарий к файлу: На вкладке Путь укажите путь к папке с файлом Visio
Example.zip [102.04 Кб]
Скачиваний: 189
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 14:21 
Не в сети
Бывалый
Аватара пользователя

Зарегистрирован: 12 май 2012, 15:16
Сообщений: 122
Очков репутации: 13

Добавить очки репутацииУменьшить очки репутации
свежее видео про Объединение запросов(таблиц) в PowerQuery от Дмитрия Щербакова (https://www.excel-vba.ru/)
Встроенное видео спрятано под спойлер ↓ Прямая ссылка на видео
Спойлер:
phpBB [youtube]

Встроенное видео спрятано под спойлер ↓ Прямая ссылка на видео
Спойлер:
phpBB [youtube]


Видео от Сергея Ожигина Сергея Ожиганова
Встроенное видео спрятано под спойлер ↓ Прямая ссылка на видео
Спойлер:
phpBB [youtube]

Дополнено позднее: 09.10.2019
Встроенное видео спрятано под спойлер ↓ Прямая ссылка на видео
Спойлер:
phpBB [youtube]


Последний раз редактировалось Surrogate 09 окт 2019, 10:56, всего редактировалось 1 раз.

Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 14:31 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Вот сделал, что-то. Так пойдет? Удалите лишние столбцы


Вложения:
1.xlsx [20.01 Кб]
Скачиваний: 90


Последний раз редактировалось Machestro 15 мар 2019, 19:06, всего редактировалось 1 раз.
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 15 мар 2019, 15:22 
Не в сети
Administrator

Зарегистрирован: 30 авг 2009, 11:02
Сообщений: 2253
Очков репутации: 100626

Добавить очки репутацииУменьшить очки репутации
Пойдет. Принцип понял. Спасибо!


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 12 янв 2022, 18:51 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Решил поднять эту тему: пару лет назад прочитал статью Из Visio в Excel через Power Query и смог это воспроизвести на своей стороне. Поигрался и забыл…

Сегодня попробовал, не выходит каменный цветок! :wall:

Похоже я что-то упустил!
1. По ходу UnzipContents не является штатной функцией языка формул Power Query M
2. После того как я внутри своего документа Excel добавил запрос UnzipContents, но вот как дальше заставить работать запрос ConnectionsTable1 описанный в статье ?
Можно более подробно описать, что такое dPath (заголовок умной таблицы), VisioPath (имя умной таблицы, имя диапазона или имя листа Excel содержащего параметр dPath)?

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 08:36 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
1. Все верно функцию UnzipContents мы сами создаем (её код есть в файле с примером)
2. В примере который я выкладывал нужно было указать путь к Папке с файлами Visio. Можно было просто сделать хардкод типа Folder.Files("C:\tmp\MeVisioFolder"), но что бы была возможность указать путь не залезая в редактор PowerQuery в своем примере я в файле Excel на листе Путь создал "Умную" таблицу у которой тоже имя Путь. В ней всего одна ячейка, где собственно и прописан путь к папке. Из Вашего примера видно, что у вас в файле Excel где то была таблица с названием VisioPath с колонкой dPath и в этой ячейке должен быть прописан путь к файлу


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 08:42 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
вот мой не рабочий пример :wall:
почти все как вы описывали


Вложения:
UnzipContents.xlsx [18.88 Кб]
Скачиваний: 30

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 08:46 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
UnzipContents это лишь функция которая вернет таблицу. Этой функции нужно "скормить" файл vsdx то есть вызвать функцию. В примере у вас есть только определение функции, но нет её вызова


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 08:49 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
В примере у вас есть только определение функции, но нет её вызова
как бы знать как ее добавить ? пробовал и без умной таблицы скармливать PQ путь к визио файлу. ругается что-то не то

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 08:53 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Предлагаю скачать мой пример из первого поста (файл excel и файл visio) указать правильный путь к папке с файлом(и) visio (именно к папке в моем примере) и в редакторе PQ пройтись по всем шагам каждой таблицы - думаю станет понятно. Вся магия твориться в таблице DataRecordsets - именно там идет вызов функции.
Отмечу что функции нужно передавать не путь а именно бинарник файла


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 08:59 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
да, блин суровая магия !
Содержимое спрятано под спойлер ↓
Спойлер:
Код:
let
VisioXML = UnzipContents(File.Contents(Excel.CurrentWorkbook(){[Name="VisioPath"]}[Content]{0}[dPath])),
Pages = Table.SelectRows(VisioXML, each
Text.StartsWith([FileName], "visio/pages/page")
and not Text.EndsWith([FileName], "s.xml")),
ContentColumn = Table.AddColumn(Pages, "Custom", each Xml.Tables(Text.FromBinary([Content]))),
ExpandedSections = Table.ExpandTableColumn(ContentColumn, "Custom", {"Name", "Table"}, {"Name", "Table"}),
// Filter Connect section and expand cells
FilterConnects = Table.SelectRows(ExpandedSections, each ([Name] = "Connects")),
PageContents = Table.ExpandTableColumn(FilterConnects, "Table", {"Table"}, {"Table.1"}),
ExpandCells = Table.ExpandTableColumn(PageContents, "Table.1", {"Attribute:FromSheet", "Attribute:FromCell", "Attribute:FromPart", "Attribute:ToSheet", "Attribute:ToCell", "Attribute:ToPart"}, {"FromSheet", "FromCell", "FromPart", "ToSheet", "ToCell", "ToPart"}),
RemoveColumns = Table.RemoveColumns(ExpandCells,{"Content", "Name"}),
// Expand pageID from file name
ExpandPageID = Table.AddColumn(RemoveColumns, "pageXML", each Text.AfterDelimiter([FileName], "visio/pages/"), type text),
Remove1 = Table.RemoveColumns(ExpandPageID,{"FileName"}),
ConnectionsTable1 = Table.ReorderColumns(Remove1,{"pageXML", "FromSheet", "FromCell", "FromPart", "ToSheet", "ToCell", "ToPart"})
in
ConnectionsTable1
т.е. запрос из статьи Геннадия это не про то ?

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 09:06 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Должен с работать, если создать этот отдельный запрос и правильно указать имя таблицы, где прописан путь, в вашем файле это Table1 а не VisioPath


Вложения:
Комментарий к файлу: Поправил файл, должно сработать
UnzipContents.xlsx [20.17 Кб]
Скачиваний: 31
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 11:01 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
Должен с работать, если создать этот отдельный запрос и правильно указать имя таблицы, где прописан путь, в вашем файле это Table1
сделал все как вы описали.
Изображение
Machestro писал(а):
редакторе PQ пройтись по всем шагам каждой таблицы
это как в уме ? давно дело было, но помню мне каждый запрос выдавал свою таблицу, которую я мог посмотреть в редакторе запросов (но это не точно)
Machestro писал(а):
Предлагаю скачать мой пример из первого поста (файл excel и файл visio) указать правильный путь к папке с файлом(и) visio (именно к папке в моем примере) и в редакторе PQ пройтись по всем шагам каждой таблицы - думаю станет понятно. Вся магия твориться в таблице DataRecordsets - именно там идет вызов функции.
попробовал и это ! если следовать выделенному совету то запросы работают !
чувствую скрестить ваш рабочий вариант с моим нерабочим и сделать из него чего-то полезное уже не получится ! :wall:
Спасибо за помощь !


Вложения:
Комментарий к файлу: Снова не работают запросы
UnzipContents_v2.xlsx [17.96 Кб]
Скачиваний: 31

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты
Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 11:23 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

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


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 11:33 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
На скрине вижу, что указан путь к файлу а не к папке
прошу прощения - скрин не тот дал, и ошибка в моем нерабочем примере была другая !
Изображение
c ошибкой в предыдущем скрине разобрался
Surrogate писал(а):
если следовать выделенному совету то запросы работают !
про указание пути к папке, а не файлу.

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 11:37 
Не в сети
Постоянный участник

Зарегистрирован: 16 окт 2016, 12:41
Сообщений: 99
Использую Visio c: 2016
Очков репутации: 4

Добавить очки репутацииУменьшить очки репутации
Я понял в чем проблема. В коде функции - она отличается от того что в моем файле примера
Код:
let
    UnzipContents = (ZIPFile) =>
let
    Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
        BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
        ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)   
    ]),

    HeaderChoice = BinaryFormat.Choice(
        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
            then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
            else BinaryFormat.Choice(
                    BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                    each BinaryFormat.Record([
                        IsValid  = true,
                        Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                        Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                        Content  = BinaryFormat.Transform(
                            BinaryFormat.Binary(Header(_)[BinarySize]),
                            (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                        )
                        ]),
                        type binary                   // enable streaming
                )
    ),

    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),

    Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
    )
in
    Table.FromRecords(Entries)
in
    UnzipContents

Это то что должно быть


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
 Заголовок сообщения: Re: Visio + Excel Power Query
СообщениеДобавлено: 13 янв 2022, 12:01 
Не в сети
Content manager
Content manager
Аватара пользователя

Зарегистрирован: 02 окт 2009, 01:01
Сообщений: 5043
Откуда: оттуда
Использую Visio c: 1998
Отрасль: Интеграция системных интеграторов
Должность: Дизайнер по оформлению документации
Уровнь квалификации: Форматирование документов MS Word
Machestro писал(а):
Я понял в чем проблема
спасибо теперь :mrgreen: заработало !

_________________
База знаний ShapeSheet
Мой Youtube-канал @surrogate-tm
Мои трафареты


Пожаловаться на это сообщение
Вернуться к началу
 Профиль  
Ответить с цитатой  
Показать сообщения за:  Поле сортировки  
Начать новую тему Ответить на тему  [ Сообщений: 56 ]  На страницу Пред.  1, 2, 3

Часовой пояс: UTC + 3 часа [ Летнее время ]



Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 14


Вы можете начинать темы
Вы можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете добавлять вложения

Найти:
Перейти:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
Вы можете создать форум бесплатно PHPBB3 на Getbb.Ru, Также возможно сделать готовый форум PHPBB2 на Mybb2.ru
Русская поддержка phpBB