News:

Happy New Year!

Main Menu

Exporting Shapedata to Database. Shape and Group

Started by SJonoS, August 05, 2024, 08:16:17 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

SJonoS

Right, Hi everyone. Been lurking for a while and appreciate the community always helping each other out.

I hope you all might be able to help me today.

As the title suggests, I am attempting to export Shape data to a SQL database. Currently the built-in addon (Database Export Wizzard) that comes with Visio 2019 professional works without issues (Yay).

However I've noticed that any groups that are part of the export exclude any shapes that are within the group from exporting their own shape data.

I've added some pictures that will hopefully make it more clear at the end of this post.

Some additional information: I've tried having individual items on layers and attempting to export only the items on those layers. This works flawlessly for shapes not in a group, however, the grouping restriction still applies even when exporting from layers only.

What I'm hoping you can point me to, is how I can disable any settings that are blocking the shapes from within the group from being included in the export.

Or changing the method of export so that the data is not concerened with being linked into the database and just exports all data regardless of if the data will be linked to the Visio page at a later stage or not.

A third option (one that I'm hoping to avoid) is to code the export to database manually. Currently my module is over 1500 lines and I set out to do a lightweight module. So I'm trying to avoid having any Userforms that a user has to input data into.

A fourth option, if it was somehow possible to get to the code of the Database export wizzard addon so that I could disable and group checks and disable any functionality that would link the data together so that the export would still work.

Please feel free to ask for more clarification or evidence for settings if it will help.

[Picture explination]

In the Group shapedata picture, you can see the shapedate for the group that is selected. This group is called KUR01

In the Shape shapedata picture, you can see the shapedata for the shape inside of the group that is selected. In this case shape ID 2003

SJonoS

#1
Here is what the database results look like from the shapes attached in the first post

https://imgur.com/2dSB2EO

(Leaving this as a link as I cannot add 3 pictures to my post)



Some extra information. Attached is my attempt with using layers when exporting the data

Surrogate

Quote from: SJonoS on August 05, 2024, 11:25:27 AMHere is my attempt with using layers when exporting the data
IMHO No way! Becouse this add-on used some algorithm, and we cant change it.

Nikolay

Not related to the post (no idea), but you can attach images here.
The new forum UI may be a bit confusing.

Here is the button to attach the images (when editing the post):
You cannot view this attachment.

SJonoS

Quote from: Surrogate on August 05, 2024, 11:42:17 AM
Quote from: SJonoS on August 05, 2024, 11:25:27 AMHere is my attempt with using layers when exporting the data
IMHO No way! Becouse this add-on used some algorithm, and we cant change it.

Do you think there is a way to code a method that ungroups all groups and can regroup those same groups again later?


Yacine


There are plenty of options to export data. Actually so many, that the difficulty is to choose the right one.
Would you mind telling more about your whole scenario?
How do you use the data in the DB, do they need to be re-imported?
How often do you do that?
Is there always one same data schema?
What's the audience? Who is using your solution? Skills?
How much data?
Always the same drawing, or arbitrary ones.
etc.
Yacine

wapperdude

QuoteDo you think there is a way to code a method that ungroups all groups and can regroup those same groups again later?

No!  Don't do it.  Ungrouping will destroy links between child and parent.  Not recoverable.

Haven't played around with Database export...  Might require some code to recursively push into subshapes and export from there.
Visio 2019 Pro

wapperdude

Alternatively, instead of recursive approach, place ALL data at top-level shape.  Then, each subshapes "grabs" the data that it desires.  Bit more work initially, but needs be done just once.  It simplifies the exporting method.
Visio 2019 Pro

SJonoS

#8
Quote from: Yacine on August 05, 2024, 03:51:31 PMThere are plenty of options to export data. Actually so many, that the difficulty is to choose the right one.
Would you mind telling more about your whole scenario?
How do you use the data in the DB, do they need to be re-imported?
How often do you do that?
Is there always one same data schema?
What's the audience? Who is using your solution? Skills?
How much data?
Always the same drawing, or arbitrary ones.
etc.


Quote from: Yacine on August 05, 2024, 03:51:31 PMWould you mind telling more about your whole scenario?


So, a bit more explination on what I'm doing.

I work in robotics logistics and use Visio to plot where our robots stop/start, unload/load. Each Visio diagram is unique as its based on warehouse needs.

The robots use a database of the points to know where it is, and I fill in those points. Untill recently we filled in those points by hand. One entry at a time. I was recently hired here and I thought it was unacceptable, as such I've attempted to automated the workflow.


Quote from: Yacine on August 05, 2024, 03:51:31 PMHow do you use the data in the DB, do they need to be re-imported?


Once in the database, the robots have thier own interface with the data. I am able to make edits using that interface.

As such, once the data is exported from Visio, I'm done with Visio for that project. (Unless a major change or mistake was made, in which case we would just overright the existing data in the database)

The database does not need to connect with Visio. The export is really only a 1 way transfer [Visio --> SQL Database]


Quote from: Yacine on August 05, 2024, 03:51:31 PMHow often do you do that?

These projects sometimes take months, so the export to Database only happens at the end of the Visio diagram. So large breaks inbetween exports, but each export will be a new diagram on a new database


Quote from: Yacine on August 05, 2024, 03:51:31 PMWhat's the audience? Who is using your solution? Skills?

My department would use this in house to remove the most tediouse part of the job. (and hopefully reduce churn...) Skills are a bit difficult to define. As the job has a terrible manual data entry portion that anyone could do. I'm just trying to make it easier for all who have to do this job.

Quote from: Yacine on August 05, 2024, 03:51:31 PMHow much data?


Tiny in comparison to what Databases can get to. The data that I'm exporting with Visio is comfortably under 1 gig

Quote from: Yacine on August 05, 2024, 03:51:31 PMAlways the same drawing, or arbitrary ones.



Each one is unique, but it always uses the same shape names and same structures. I have one Shape template that is used for all drawings.




Quote from: wapperdude on August 05, 2024, 03:58:36 PMAlternatively, instead of recursive approach, place ALL data at top-level shape.  Then, each subshapes "grabs" the data that it desires.  Bit more work initially, but needs be done just once.  It simplifies the exporting method.


Hmm... I think the issue would still persist. Even if all the data is on the top-level shape that can be exported (The group) The shape ID's inside the group are never exported, I would not be able to link the Subshape to the data to the data in the toplevel shape.

Even if the shapes in groups are in thier own layers, if they are grouped, then Visio can not see those layers anymore You can see evidence of that in Post #2 on this thread.

This is assuming I have understood what you suggested correctly... I'm sorry if I've misread it.

[Edit 1]

Reading again, it seems I don't know what sub-shapes are. I'll have to do a bit of research into this

Surrogate

Quote from: SJonoS on August 05, 2024, 02:14:35 PMDo you think there is a way to code a method that ungroups all groups and can regroup those same groups again later?
I think your code can do it without ungrouping/re-grouping. Just use sub-shapes recursion.

SJonoS

#10
Quote from: Surrogate on August 06, 2024, 08:02:38 AM
Quote from: SJonoS on August 05, 2024, 02:14:35 PMDo you think there is a way to code a method that ungroups all groups and can regroup those same groups again later?
I think your code can do it without ungrouping/re-grouping. Just use sub-shapes recursion.

Do you have an example of what Subshape recursion would look like?

Are the subshapes accessible to the in-built database export addon?

Surrogate

Quote from: SJonoS on August 06, 2024, 08:33:41 AMDo you have an example of what Subshape recursion would look like?
For example
Quote from: Surrogate on July 26, 2013, 01:43:57 PMSub a()
    Dim sh As Visio.Shape
    Dim pg As Visio.Page
    Dim coll As New Collection

    For Each pg In ThisDocument.Pages
        For Each sh In pg.Shapes
            If sh.Shapes.Count > 0 Then
                Recursion sh.Shapes, coll
            End If
            coll.Add sh
        Next sh
    Next pg
    For Each sh In coll
        sh.CellsSRC(visSectionObject, visRowMisc, visNonPrinting).FormulaForceU = "False"
    Next sh
End Sub
Sub Recursion(ByVal shps As Visio.Shapes, coll As Collection)
    Dim sh As Visio.Shape
    For Each sh In shps
        If sh.Shapes.Count > 0 Then
            Recursion sh.Shapes, coll 'çäåñü ïðîèñõîäèò âûçîâ ñàìîé ñåáÿ
        End If
        coll.Add sh
    Next sh
End Sub
Thanks to 9rey for recursion macro :)
Quote from: SJonoS on August 06, 2024, 08:33:41 AMAre the subshapes accessible to the in-built database export addon?
I haven't used it in a very long time. Tried it today and couldn't find it.

Yacine

#12
Programming a robot using a Visio drawing is very clever and interesting, thanks for sharing.

Your options:

Unless you follow Wapperdudes advice to bring all data on the top level, I would not see any reasonable solution out of the box. But the code is not complicated at all.

Code - VBA
Using VBA you can connect to a database via DAO or ODBC (ADO ist also an alternative but the 2 options named should suffice).
With DAO you can connect directly to an MS Access DB, for any other, you would use ODBC.

That is if you want to write directly in your DB.

Alternatively, you create a CSV or TSV that you would then import in the DB.

I don't explain the Excel path, too complicated for the purpose.

You did not explain the type of data structures that you have, but let's assume that there is one for the top level and one for the sub-shapes.

First of all you need a way to recognize the relevant shapes, as most probably not all contain data.
This could be:
- the existence of a certain shapesheet cell - e.g. if shp.CellExists("prop.path_nr", false) then ...
- the value in a certain shapesheet cell - e.g.  shp.CellExists("user.type", false) then if shp.Cells("user.type").ResultStr("") = "path_point" then ...
- the membership to a certain layer e.g. if shp.layercount > 0 then ... if shp.layer(i) = n then ...

Now that you know how to identify the right shapes, you iterate over all the shapes and sub-shapes of the page as Surrogate explained.

Each time you find one, you collect its data.

something like:

dim aFields() as variant
dim vField as variant
dim tFields as string
dim res = string
tFields = "ID,Name,X,Y"
aFields = split(tFields, ",")

for each vField in aFields:
res = res & shp("prop." & vField).ResultStr("") &"|"
next vField
'remove the last separator
res = left(res, len(res)-1)

you would then add res to a structure collecting the data, the write to your DB.

Alternatively, you can use python.
If you are will to go this way, there is at least one library to connect to Visio, the method is more or less the same as in VBA, but the data handling is significantly easier.

Would that be enough to get you started?





Yacine

SJonoS

I have found that containers do not suffer this issue.

Any shapes that have shapedata inside of a container are included in the Database export. The container can also have its own shapedata. If you sperate the shapes and containers via layers you can export all data or only the data on specific layers.

Pictures are included.

I guess this means my problem is mostly solved as this won't have as many changes in the code needed to get this working

However,

I will need to find a way to maintain shape position and size relative to eachother within the container.

Does anyone have a suggestions on how to maintain shape position of shapes in a container?

SJonoS

Quote from: Yacine on August 06, 2024, 11:49:26 AMProgramming a robot using a Visio drawing is very clever and interesting, thanks for sharing.

Your options:

Unless you follow Wapperdudes advice to bring all data on the top level, I would not see any reasonable solution out of the box. But the code is not complicated at all.

Code - VBA
Using VBA you can connect to a database via DAO or ODBC (ADO ist also an alternative but the 2 options named should suffice).
With DAO you can connect directly to an MS Access DB, for any other, you would use ODBC.

That is if you want to write directly in your DB.

Alternatively, you create a CSV or TSV that you would then import in the DB.

I don't explain the Excel path, too complicated for the purpose.

You did not explain the type of data structures that you have, but let's assume that there is one for the top level and one for the sub-shapes.

First of all you need a way to recognize the relevant shapes, as most probably not all contain data.
This could be:
- the existence of a certain shapesheet cell - e.g. if shp.CellExists("prop.path_nr", false) then ...
- the value in a certain shapesheet cell - e.g.  shp.CellExists("user.type", false) then if shp.Cells("user.type").ResultStr("") = "path_point" then ...
- the membership to a certain layer e.g. if shp.layercount > 0 then ... if shp.layer(i) = n then ...

Now that you know how to identify the right shapes, you iterate over all the shapes and sub-shapes of the page as Surrogate explained.

Each time you find one, you collect its data.

something like:

dim aFields() as variant
dim vField as variant
dim tFields as string
dim res = string
tFields = "ID,Name,X,Y"
aFields = split(tFields, ",")

for each vField in aFields:
res = res & shp("prop." & vField).ResultStr("") &"|"
next vField
'remove the last separator
res = left(res, len(res)-1)

you would then add res to a structure collecting the data, the write to your DB.

Alternatively, you can use python.
If you are will to go this way, there is at least one library to connect to Visio, the method is more or less the same as in VBA, but the data handling is significantly easier.

Would that be enough to get you started?

Thank you, this is a great amount of information and is helping with getting more context of sub-shapes and database export if I can't get the containers to work out for me

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 462 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1319KB.
Tokens: post-login.
Cache hits: 16: 0.00195s for 26,551 bytes (show)
Cache misses: 6: (show)
Queries used: 17.

[Show Queries]