Skip to Content

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Ling Guo

    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

    (0) 

Leave a Reply