Export database design to Excel

Started by seattlerainier, August 19, 2014, 04:19:48 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

seattlerainier

With Visio 2010, I can reverse engineer a database and get database schema. After I modify the database design, how can I export the database design data to a Excel file. Such as table column name, data type, etc.

Thanks,

Yacine

Yacine

seattlerainier

I try it. However it looks like the shape is locked. When I create new report, it doesn't allow me to select the table column or even table name.

I tried unprotect everything from "developer" manual. However the shape is still locked somehow. I can't pull anything from it.

Otherwise, it looks like the report is the solution.

Can you help me more.

Thanks!

Yacine

Yacine

seattlerainier

I attached two document:

1. order.vsd ----- It is just a example database modeling file which illustrate the database design in Visio
2. database.png ----- a screenshot which show the table columns and properties which I'd like to report.

Thanks a lot!

Yacine

So I had some time to look for a solution to your problem.
Alas the results are very poor:
- I tried looking in the shapes both in visio itself and in an exported vdx with XML Notepad. MS did a good job in hiding the data. I gave up.
- Google does find a tool when you search for visio database forward engineering
http://forwardengineer.codeplex.com/
http://www.isummation.com/blog/forward-engineering-visio-2007-database-model-to-sql-server/
I didn't get it myself to work, but you may be more patient than me.
Sorry.
Yacine

Nikolay

#6
The database data (and UML data) is stored in a BLOB in binary form (in a hidden master named "Database model").
It's next to impossible to unpack this blob directly. But, there is a programmatic API (undocumented unfortunately, afaik) which allows you to access that data.

The API resides in the MODELENG.dll (it's in Visio folder) and provides COM API to read the data out of the DB and UML diagrams (that's the one that was removed in Vsio 2013)
The forward-engineering addin uses this MODELENGLib to access the diagram data.

Anyways, here is some VBA code sample, which extracts the following info using that MODELENGLib: table, columns, PKs, nullable, physical names..
If you want to extract full model information, including relationships and so on, you better consult the source code of the forwardengineer addin (ModelTransform.cs)

The code prints to the output window something like:

Table [customer]
  Column [customerID], Type : "INTEGER", Nullable : False, PK : True
  Column [firstName], Type : "VARCHAR(15)", Nullable : True, PK : False
  Column [LastName], Type : "VARCHAR(30)", Nullable : True, PK : False
Table [product]
  Column [productID], Type : "INTEGER", Nullable : False, PK : True
  Column [productName], Type : "VARCHAR(30)", Nullable : True, PK : False
  Column [price], Type : "DECIMAL(10,2)", Nullable : True, PK : False
Table [order]
  Column [orderID], Type : "INTEGER", Nullable : False, PK : True
  Column [customerID], Type : "INTEGER", Nullable : False, PK : True
  Column [ProductID], Type : "INTEGER", Nullable : False, PK : True


Excel stuff is up to you I suppose... mind the reference to the MODELENG.dll !


Sub ExtractInfo()

    Dim engine As New MODELENGLib.VisioModelingEngine
   
    Dim models As MODELENGLib.IEnumIVMEModels
   
    Dim modelEnum As MODELENGLib.IEnumIVMEModels
    Set modelEnum = engine.models
   
    Do
        Dim model As MODELENGLib.IVMEERModel
        Set model = modelEnum.Next
        If model Is Nothing Then Exit Do
       
        Call ExtractTables(model)
       
    Loop
End Sub

Sub ExtractTables(model As MODELENGLib.IVMEERModel)

    Dim tableEnum As MODELENGLib.IEnumIVMEEntities
    Set tableEnum = model.Entities
   
    Do
        Dim table As MODELENGLib.IVMEEntity
        Set table = tableEnum.Next
        If table Is Nothing Then Exit Do
       
        Debug.Print "Table [" & table.PhysicalName & "]"
       
        Set pkColumns = CreateObject("Scripting.Dictionary")
       
        Call ExtractTableKeys(table, pkColumns)
        Call ExtractTableColumns(table, pkColumns)
       
    Loop

End Sub

Sub ExtractTableColumns(table As MODELENGLib.IVMEEntity, pkColumns)

    Dim columnEnum As MODELENGLib.IEnumIVMEAttributes
    Set columnEnum = table.Attributes
   
    Do
        Dim column As MODELENGLib.IVMEAttribute
        Set column = columnEnum.Next
        If column Is Nothing Then Exit Do
       
        Dim columnDataType As MODELENGLib.IVMEDataType
        Set columnDataType = column.DataType
       
        isPrimaryKey = pkColumns.Exists(column.PhysicalName)
       
        Debug.Print "  Column [" & column.PhysicalName & "], Type : """ & columnDataType.PhysicalName & """, Nullable : " & column.AllowNulls & ", PK : " & isPrimaryKey
    Loop

End Sub

Sub ExtractTableKeys(table As MODELENGLib.IVMEEntity, pkColumns)

    Dim annotationEnum As MODELENGLib.IEnumIVMEEntityAnnotations
    Set annotationEnum = table.EntityAnnotations
   
    Do
        Dim annotation As MODELENGLib.IVMEEntityAnnotation
        Set annotation = annotationEnum.Next
        If annotation Is Nothing Then Exit Do
       
        Select Case annotation.kind
        Case eVMEEREntityAnnotationPrimary
       
            Dim pkColumnEnum As MODELENGLib.IEnumIVMEAttributes
            Set pkColumnEnum = annotation.Attributes
           
            Do
                Dim pkColumn As MODELENGLib.IVMEAttribute
                Set pkColumn = pkColumnEnum.Next
                If pkColumn Is Nothing Then Exit Do
               
                pkColumns.Add pkColumn.PhysicalName, pkColumn
            Loop
        End Select
    Loop
End Sub


I've also attached the diagram file you posted (take a look at the VBA code inside)

Yacine

Yacine

aledlund


seattlerainier

Thanks a lot of the inputs!!! It really help.