Extending the Excel_Import.xem

[former_member185199]

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!)

insert the values:

Step4

make this attribute selectable in the Importform :

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

by adding the extended attribute Append2Attribute:

give some more information:

Step5

locate on the global macro:

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