Skip to Content
Author's profile photo dirk jaeckel

Extending the Excel_Import.xem

Have you ever tried to import different columns of an Excel sheet into 1 attribute?

Usecase:

your boss wants you not to loose any information imported from the BA-Excelsheet, but not all columns fit to an attribute, what now? Hmm..ok, lets put everything what  doesnt fit an attribute to comment or description, but uhhh:

The standard behaivor of Excel_Import is to overwrite the attribute with the most right column assigned to this attribute…

(As we learned from the comments at my Blog Using the Excel_Import.xem  there is also the way to create an extended Attribute for every column but this article was already written at this time , and sometimes it´s really the case that you want to combine 2 or more columns into  1 attribute)

After diggin deep in the code of the xem, here is a 5 step solution with approx. 10 lines of vbs code and an additional extended attribute:

Step 1

locate the  Excel_Import.xem (defaultpath is C:\winapps\Sybase\PowerDesigner 16\Resource Files\Extended Model Definitions) and copy it into MyExcel_Import.xem

Step 2

Include the MyExcel_Import.xem into your model (See my blog Using the Excel_Import.xem if you don´t know how) and open it, then rename the name and code to MyExcelImport so you see the difference to the original one

Step3

add the extended attribute Append2Attribute at location: MYExcelImport::Profile\ExtendedSubObject\Stereotypes\ColumnMapping\Extended Attributes\

(HINT:copy the line above  and paste it into the highlighted adressbar!)

addxa.PNG

insert the values:

addxa2.PNG

Step4

make this attribute selectable in the Importform :

MYExcelImport::Profile\ExtendedSubObject\Stereotypes\ColumnMapping\Forms\ImportColumn

addxa3.PNG

by adding the extended attribute Append2Attribute:

addxa4.PNG

give some more information:

addxa5.PNG

Step5

locate on the global macro:

myexcelimport_xem.PNG

In function CommitRowData, locate the codelines:

    


  For each colnMap in colnCol
            key = colnMap.Name
            if dictValues.Exists(key) and not colnMap.GetExtendedAttribute("%CurrentTargetCode%.Skip") then

replace the (2) lines


              oldVale = dictValues.Item(key)
               vale = oldVale

with this code:


               Dim Append2Attribute
               Append2Attribute = colnMap.GetExtendedAttribute("%CurrentTargetCode%.Append2Attribute")
               if Append2Attribute then
                  attrName = colnMap.GetExtendedAttribute("%CurrentTargetCode%.Attribute")
                  if not isnull(newObj.GetAttribute (attrName) ) then ' when the object exists then here we find the older values !
                     oldvale = newObj.GetAttribute (attrName) & vbCrLf
                  end if
                  vale = oldVale & dictValues.Item(key)
               else
                  oldVale = dictValues.Item(key)
                  vale = oldVale
               end if

and save your work.

Enjoy!

DJ

:

PS: please rate this, cos i wanna know if its worth to continue

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thank you for showing this functionality

      Author's profile photo Former Member
      Former Member

      Dirk,

      This is very helpful. I'd like to know how to change the global macro to make the name or code case insensitive.

      I do Table.Column Excel Import a lot. Under the Import Options, I usually choose the "Reference associated objects by Code" option. So I want both the table (parent) code and the column code to be case insensitive. That way, if a column code is "PROD_CODE" with the table code of "TABLE_1" in my Excel file, it should find a match with a column code "Prod_Code" under table code "Table_1" in the physical model.

      In summary, when doing Excel Import, I'd like to be able to ignore the case.

      Please advise in which function and which lines of code I can make the changes. Our PD version is 16.5.

      Once again, I appreciate your help!

      Thanks,

      Ling