Macro: Import Attributes

This macro for importing attributes into ER/Studio Data Architect was provided by  in a webinar last summer. Check it out!

ImportAttrib3.txt
'1. Entity Name
'2. Table Name
'3. Attribute Name
'4. Column Name
'5. Data type
'6. Table Definition
'7. Attribute Definition
'8. Nullability - 'NULL' or 'NOT NULL'


'Location of the spread-sheet
Const FILE_NAME As String = "C:\Documents and Settings\oktaya\Desktop\DataAttribUpload.xls"
Sub Main

	Dim theDiagram As Diagram
	Dim theModel As Model
	Dim theEntity As Entity
	Dim theAttribute As AttributeObj

	'define excel variables
	Dim Excel As Object
	Dim sheet As Object
	Dim range As Object
	Dim rowCount As Integer
	Dim I As Integer
	Dim entity_name As String
	Dim table_name As String
	Dim attribute_name As String
	Dim column_name As String
	Dim table_definition As String
	Dim attr_definition As String
	Dim datatype As String
	Dim nullability As String
	Dim datawidth As Integer
	Dim nPos As Integer
	Dim sDataType As String
	Dim s As String
	Dim nSepPos As Integer
	Dim datascale As Integer

	'initialize excel object and make visible
	Set Excel = CreateObject("Excel.Application")
	Excel.Visible = True
	Excel.workbooks.open FILE_NAME
	'get sheet info from excel object
	Set sheet = Excel.worksheets(1)
	Set range = sheet.usedrange
	rowCount = range.rows.Count

	Set theDiagram = DiagramManager.ActiveDiagram
	Set theModel = theDiagram.ActiveModel
	For I = 2 To rowCount
		entity_name = Trim(range.cells(I,1).Value)
		table_name = Trim(range.cells(I,2).Value)
		attribute_name = Trim(range.cells(I,3).Value)
		column_name = Trim(range.cells(I,4).Value)
		sDataType = Trim(range.cells(I,5).Value)
		table_definition = Trim(range.cells(I,6).Value)
		attr_definition =  Trim(range.cells(I,7).Value)
		nullability =  UCase(Trim(range.cells(I,8).Value))
		nPos = InStr(sDataType, "(")
		If nPos = 0 Then
			datatype = sDataType
			datawidth = 0
		Else
			datatype = Mid(sDataType, 1, nPos - 1)
			sDataType = Mid(sDataType, nPos +1, Len(sDataType))
			nPos = InStr(sDataType, ")")
			s = Mid(sDataType, 1, nPos - 1)
			nSepPos = InStr(s, ",")
			If nSepPos = 0 Then
				datawidth = Mid(s, 1, nPos - 1)
				datascale = 0
			Else
				datawidth = Mid(s, 1, nSepPos - 1)
				datascale = Mid(s, nSepPos + 1, Len(sDataType))
			End If
		End If
		Set theEntity = theModel.Entities(entity_name)
		If theEntity Is Nothing Then
			Set theEntity = theModel.Entities.Add(0, 0)
			theEntity.EntityName = entity_name
			theEntity.TableName = table_name
			theEntity.Definition = table_definition
		End If
		Set theAttribute = theEntity.Attributes(attribute_name)
		If theAttribute Is Nothing Then
			Set theAttribute = theEntity.Attributes.Add(attribute_name, False)
			theAttribute.ColumnName = column_name
			theAttribute.AttributeName = attribute_name
			theAttribute.Definition = attr_definition
			theAttribute.Datatype = datatype
			theAttribute.DataLength = datawidth
			theAttribute.DataScale = datascale

			If nullability = "NULL" Or nullability = "NOT NULL" Then
				theAttribute.NullOption = nullability
			End If
		End If
	Next I
	MsgBox "Done"
End Sub

  • This is macro is based on the one included in Data Architect "Import Columns From Excel.bas". I'm a bit confused by it.

    If I supply all the fields:
    '1. Entity Name
    '2. Table Name
    '3. Attribute Name
    '4. Column Name
    '5. Data type
    '6. Table Definition
    '7. Attribute Definition
    '8. Nullability

    If I fill in all the cells - so I give an Entity Name and a Table Name and run it in a Logical model

    I creates all the Entities named with the Entity Names and with Attributes named with the Attribute Names as expected.
    However, it also creates Entities named with the Table Name, each with a single Attribute (the first in the list) named with the first Attribute Name.

    I'd expect it to use the Entity and Table names to create a single Entity with the Entity Name and Table Names set correctly.

    What would be really helpful would be an export macro and a corresponding import macro - with a comprehensive set of properties imported and exported.
  • In reply to Andrew Morris:

    Could you post the format of your spreadsheet?  Attached is a sample spreadsheet that I've used and it is importing as expected.  The first row is a header row so it's ignored.

     

    /cfs-file/__key/communityserver-discussions-components-files/339/6710.ImportSample.xlsx

  • In reply to Stanley Chan:

     export.xlsx

     import.xlsx

    Hi Stanley,

    The import file is import.xlsx

    I've exported the result as export.xlsx

    The entities get created with all attributes, but also an entity named like the table name with just the first attribute.  The table and column names don;'t get set in the Entities either.

    I'm also unclear on what is the valid format for the datatypes.

  • In reply to Andrew Morris:

    Are you doing this on the physical model or the logical model?  This macro only works on the logical model.  In the physical model we'd have to modify the macro a bit.

     

    For the datatype this would be logical datatypes.

  • In reply to Stanley Chan:

    On the logical model.
  • In reply to Andrew Morris:

    /cfs-file/__key/communityserver-discussions-components-files/339/ImportModel.zip

     

    Attached is a zip of the model that I imported using the same spreadsheet you have.  I have 3 Entities and all the columns are there.

    I believe that I received a similar issue as you was if I tried this in the physical model.

     

    Would it be possible to take a look at what you're doing over the phone and figure this out?  If you can email me stanley.chan@idera.com a day/time that works best for you I can set something up.  I'd really like to know what's different between your setup and mine.

  • In reply to Stanley Chan:

    Hi Stanley , Thanks. I'll take a look at this and send you an email. Regards, Andrew
  • In reply to Andrew Morris:

    Just to add some closure to this, the issue here is in the model options. When you set the name handling to Complete Synchronization we run into some issues and new entities are created. When you set the name handling to No Synchronization or Partial Synchronization then the macro works as expected.
  • In reply to Stanley Chan:

    Hi Stanley !
    I want to run the similar macro on physical model. What are the changes that i need to do?
    Thanks.
  • In reply to Ayyappa Bhusamsetti:

    Hi,

    You'll want to modify the following line:

    Set theEntity = theModel.Entities(entity_name)

    to this:

    If theModel.Logical = True Then

    Set theEntity = theModel.Entities(entity_name)

    Else

    Set theEntity = theModel.Entities(table_name)

    End If

     

    This will use the Physical name instead of the logical name.

     

    Regards,

    Stan

  • In reply to Stanley Chan:

    Thanks Stanley !

    It is working now.