excel data dictionary is converted to PDM in batch, and PowerDesigner exports sql (no download is required)

First, my version of PowerDesigner16.5

Batch conversion, excel data dictionary conversion to PDM

Three steps~
Step 1
Fill in the contents of the table according to the excel template (field name, data type...)
Create a new excel. The following table header (11 columns in total) is the excel template. The order of the table header should not be wrong~

Chinese table name English table name Field description English field Field type Field description Field length Primary key Foreign key Is it empty Precision

All table designs are placed in sheet1 of excel, with a blank row in the middle of each table, and then the table is placed infinitely downward

Step 2
Copy the script, paste it into the txt document, and search x1 Workbooks Open, change the excel path to the path of the excel table you created above, and do not touch anything else

'******************************************************************************  
'* All table designs are placed in one excel One of sheet In, there is a blank line in the middle of each table, and the table body has a header, as described below,  
'* The previous row contains the table name and table description, which are displayed in the A and C Columns. The following format is copied directly to excel As you can see in, spaces are tabs.  
'******************************************************************************    
'                             Excel The format is as follows 
'Chinese table name	English table name	Field description	English field	Field type	Field description	Field length	Primary key	Foreign key	Is it empty	Precision                                     
'******************************************************************************    
Option Explicit  
  
Dim mdl ' the current model  
Set mdl = ActiveModel  
If (mdl Is Nothing) Then  
    MsgBox "There is no Active Model"  
End If  
  
Dim HaveExcel  
Dim RQ  
RQ = vbYes 'MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation")  
If RQ = vbYes Then  
    HaveExcel = True  
    ' Open & Create Excel Document  
    Dim x1  '  
    Set x1 = CreateObject("Excel.Application")  
    x1.Workbooks.Open "C:\Users\pc\Desktop\pdm Import export\002.xlsx"  'specify excel Document path  
    x1.Workbooks(1).Worksheets("Sheet1").Activate   'Specify to open sheet name  
Else  
    HaveExcel = False  
End If  
  
a x1, mdl  
sub a(x1, mdl)  
dim rwIndex  
dim tableName  
dim colname  
dim table  
dim col  
dim count  
dim abc  
  
on error Resume Next  
'--------------------------------  
'Here is the read excel´╝îAdd table entity attribute  
'--------------------------------  
For rwIndex = 2 To 3031  'Specify the Excel Row Mark since row 2 is the header, it depends on how many rows you design in this table from row 1  
    With x1.Workbooks(1).Worksheets("Sheet1")'Cyclic sheet name  
        If .Cells(rwIndex,1).Value <> "" And  .Cells(rwIndex,2).Value <> ""  Then  
            set table = mdl.Tables.CreateNew 'Create a table entity  
            table.Code = .Cells(rwIndex,2).Value'from excel Get table name and code from  
            table.Name = .Cells(rwIndex,1).Value'
            table.Comment = .Cells(rwIndex,1).Value
            count = count + 1  
        End If 
        If .Cells(rwIndex,4).Value <>"" Then
            set col =table.Columns.CreateNew 'Create a column/field  
            col.Name = .Cells(rwIndex, 3).Value 'Specify column name  
            col.Code = .Cells(rwIndex, 4).Value 'Specify column code  
            col.DataType = .Cells(rwIndex, 5).Value 'Specify column data type 
            col.Comment =  .Cells(rwIndex, 6).Value 'Specify column description   
            If .Cells(rwIndex, 8).Value = "True" or .Cells(rwIndex, 8).Value = "true" Then'Specify primary key  
              col.Primary = true  
              End If       
            If .Cells(rwIndex, 10).Value = "True" or .Cells(rwIndex, 10).Value = "true" Then'Specify whether the column can be empty true Cannot be empty  
             col.Mandatory = true  
             End If 
         End If  
    End With  
Next  
    MsgBox "Total data table structure generated " + CStr(count), vbOK + vbInformation, "surface"  
Exit Sub  
End sub  

Step 3
Open the pdm, and press ctrl+shift+x to open the script window. Open the old version of pdm with ctrl+shift+h, copy and paste all the contents in your txt document, and click run

Just wait. After that, the "total number of generated data table structures" will pop up

Click close after confirmation. No need to save

PowerDesigner export sql

Four steps~
Step 1
Database->change current DBMS select a database

Select oracle or mysql
Step 2
Put the field description and field description together. Otherwise, the field description will be exported by default, and the field name will disappear
Database - > Edit current dbms->general tab ->script - > objects - > column - > columncommnet- modify the value value

The value value can be copied directly. The code meaning is also very simple. When the field description and the field description are equal, the field description will be displayed, otherwise both will be displayed

comment on column [%QUALIFIER%]%TABLE%.%COLUMN% is
.if (%COMMENT%==%COLNNAME%)
 '%COLNNAME%'
.else
 '%COLNNAME% %COMMENT%'
.endif(\n)

Step 3
Modify generation rule
Database - > generate database->format tab - > select the "generate name is empty comment" option

This means that if the field description is empty, the field description is displayed as a comment~
Step 4
Database->generate database (ctrl+g) select the path. Do not touch other paths. Just click OK

After confirmation, look at the bottom of pdm

OK, let me show you the renderings~

Tags: MySQL Oracle Excel

Posted by jola on Tue, 31 May 2022 04:23:54 +0530