how do I import indicies with excelimport.xem???

[former_member185199]

Hi all,

i have the following exceltable:

Indexnumber	Indexname	Field	Aufsteigend
1	TBAW0666TESTTAB.IXAW066601TESTTAB	PK	X
2	TBAW0666TESTTAB.IXAW066602TESTTAB	TBAW0666TESTTAB.PK	X
2	TBAW0666TESTTAB.IXAW066602TESTTAB	TBAW0666TESTTAB.FELD2	X
2	TBAW0666TESTTAB.IXAW066602TESTTAB	TBAW0666TESTTAB.FELD3	X
2	TBAW0666TESTTAB.IXAW066602TESTTAB	TBAW0666TESTTAB.GUELTIG_AB
3	TBAW0666TESTTAB.IXAW066603TESTTAB	FELD1	X
3	TBAW0666TESTTAB.IXAW066603TESTTAB	GUELTIG_AB	X

And the following importdefinition:

Table Indexdefinitionen=Table.Index.IndexColumn: Indexnumber=Comment, Indexname=<Parent>, Field=Column, Aufsteigend=Ascending

The 3 indexes are created, but not the indexcolumns, i tried both versions of fieldnames with tablename and without, i also switched on and of the option "create object if not exists", but no results, do i try to bind the field to the wrong object/collection within Table.IndexColumn?

Thanks for any advice

[Former Member · Accepted solution]

Was there a solution which does not need VBA? I am in the
process of importing metadata from various sources and having a major issue.
Should I be submitting this as a bug?

I really do not feel like doing VBA programming again…

[former_member185199 · Accepted solution]

Konstantin,

i haven´t a solution for this so far, i am also not able to use vba here because all other objects are imported by the standard solution i can´t mix technics here, yes i would treat this as a bug, even more that no one fom sap came with an easy to solve description.

regards

dj

[GeorgeMcGeachie · Accepted solution]

I think it is a bug. I've just tried different ways of creating the index columns, and PD just tells me that "The import of a sub-object requires a  column".

[GeorgeMcGeachie · Accepted solution]

Konstantin

Are you also having problems importing Index Columns, or different objects?

[Former Member · Accepted solution]

Yes.  Index Columns.

I have no problem creating indexes through import process but i cannot assign what columns are part of the index.

I had same error as  "The import of a sub-object requires a  column". I tried different aproach where i got script errors.

[vaudino · Accepted solution]

Hi all

Several fixes have been done on index column import and will be included in next PL (16.5 SP03 PL2)

veronique

[former_member185199 · Accepted solution]

Hi Veronique,

thanks for this info, do you know if these fixes are in the excelimport.xem or  on PD side?

cheers

dj

[vaudino · Accepted solution]

Hi Dirk

The fixes are in excelimport xem.

veronique

[mark_mcnary · Accepted solution]

I am stuck at 16.1.  Is there a fix for this version, as I am having the same difficulty.

-Mark McNary

[former_member185199 · Accepted solution]

I was able to run the importexcel from 16.5 SP1 with 16.1 ( not rcommended of course), actually i dont have the newer version, but you could try it yourself

[mark_mcnary · Accepted solution]

According to Veronique, the fix is coming in the next PL (16.5 SP03 PL2)

Or were you able to import index columns using the current 16.5 importexcel.xem?

[former_member185199 · Accepted solution]

no, i tried that version before veronique posted this solution. as you can see, i posted this question already 7 month ago. After finding that it is not fixed in that version i went back to the original xem from 16.1(we are also not on 16.5 yet)

Message was edited by: dirk jaeckel

[mark_mcnary · Accepted solution]

Ah, I understand.  This is pretty irritating.  I am at the point where I am building indexes for performance reasons, and want a way to import the data back into my physical mode.

Oh well.  When they fix it, they fix it.

[mark_mcnary · Accepted solution]

Veronique, do you have an ETA on this PLrelease date?

-Mark McNary

[vaudino · Accepted solution]

PD 16.5 SP03 PL2 is currently planned for mid April.

Veronique

[mark_mcnary · Accepted solution]

Thank you.  I look forward to being able to import index columns from a spreadsheet.  The excel import functionality has been an immensely useful tool.  It has saved me literally hundreds of hours on my project.

-Mark McNary

[mark_mcnary · Accepted solution]

Veronique, has this PD 16.5 SP03 PL2 been released yet?

-Mark McNary

[vaudino · Accepted solution]

Hi Mark

Not yet

PD 16.5 SP03 PL2 will be released early next week.

veronique

[mark_mcnary · Accepted solution]

Well, I have the xem, and it is supposed to fix the bug, 759001.  so I must be doing something wrong when I try to import the index columns.

I am assuming that the first decision is to import the sheet as table.index.index.column.

I am not sure of what information I should need to complete the import.  I have a spreadsheet with the column name, a class name of index column, sort, the index name and the column name again.

Any ideas on how to put this together into an import of index columns?

[vaudino · Accepted solution]

Hi

It is working for me with a tab for Table.index containing Parent (being the table), index code and index name.

And a tab Table.Index.Index.Column containing Parent (being the qualified name of the index : Table.Index) and COlumn (being teh qulified name of teh column : Table.column), and Ascending

I am using the code to refer to objects and I have changed the options accordingly

veronique

[mark_mcnary · Accepted solution]

I am sooo close.

My import is adding column elements of the index, but it is not finding my table columns.

Here is what I have for import.  The index already exists.  The index column contains the code values for the table and column

Parent	Index.Column	Sort
Business CLIENT Account Number.IX_BUS CLIENT ACCT NBR_1	BUS_CLIENT_ACCT_NBR.ACCT_NBR	Ascending
Business CLIENT Account Number.IX_BUS CLIENT ACCT NBR_1	BUS_CLIENT_ACCT_NBR.ABA_ROUTE_NBR	Ascending

When I import these values, using parent for parent, code terms, table column for index column and sort for sort, I get these warnings.  The table and columns exist, I cut and pasted the values from the model.

Opening document \\stuff\performance_indexes_001.xlsx

Importing data from table Table.Index.Index.Column

Error: Could not find object location with Name = BUS_CLIENT_ACCT_NBR.ACCT_NBR under Physical Data Model 'IBP Master PDM 140422'

Error: Could not find object location with Name = BUS_CLIENT_ACCT_NBR.ABA_ROUTE_NBR under Physical Data Model 'IBP Master PDM 140422'

  2 object(s) created

Closing document performance_indexes_001.xlsx

The import of \\stuff\performance_indexes_001.xlsx is complete

and 2 objects have been created.

2 warning(s) have been logged in the output window.

Any advice?

[vaudino · Accepted solution]

On Table.Index.Index.Column tab, Parent should contain the index qualified (table.index) and the Index.column should contain the Column of the index qualified also (table.column).

Usually they start by the same string, as Table is used in both qualified name of index and qualified name of column

this is not the case in your example....

Depending on your matching choice (name or code) you should change one or the other

veronique

[mark_mcnary · Accepted solution]

Excelsior!

I was able to import the columns.  I did not get the sort to take, but that is a minor issue.

Thank you so very much for your help.

[vaudino · Accepted solution]

In my sample, I used a boolean for Sort.

It was actually Ascending Y/N

I indicated in the options that I want a Y for boolean True value

veronique

[Former Member · Accepted solution]

hi, I recently downloaded a trial version of PowerDesigner 16.5 and have been attempting to load index columns using excelimport.xem and have been unable to get it to work. I have a tab called Table.Index with columns Parent, Name, Code, Comment, Unique that successfully imports the Indexes however I also have a tab called Table.Index.Index Column with columns Parent, Column and Sort that does not work. The Parent contains Table.Index Name and Column contains Table.Column Name. I am matching on name.

During the import the dialog for matching on Column successfully matches the Column Attribute but also shows Object: <None> below.  Am I supposed to change this to be Table.Index.Index Column? I have tried this but it doesn't seem to help.

I noticed that the version of ExcelImport.xem has a last update date of 1/8/2013 yet the thread here seems to indicate there have been changes since then. Is there a more current version that I should be using?

thanks for any help.

[mark_mcnary · Accepted solution]

I have found that when importing into a model from a spreadsheet, less is more.  The more columns you are importing, the more likely it is that something will fail.  My spreadsheet has 2 columns.  Column 1 is the parent, which is the table index, identified as <table name>.<index name> and column 2 is the table column, identified as <table name>.<table column>.

When importing, the column parent is identified as parent, and the column is identified as type column, with sub-type of table column from the drop down.

This will associate table columns with the index.  I let the default sort value of ascending stay unless I need to change it in the model.

[Former Member · Accepted solution]

Hi,

You must update your trial.

Bye

[former_member185199]

really no-one has an idea with this ?

[Former Member]

Dirk,

I ran into this when I scripted an excel imprt.  Here is a snippet of code that might help:

Dim CopyIndxCol

Dim Indx

Dim IndxCol

Dim CpyCol

For each CopyIndx in CopyTab.indexes

  Set Indx = Tab.CreateObject(PdPDM.cls_Index)

  Indx.Name = CopyIndx.Name

  Indx.Code = CopyIndx.Code

  Indx.Type = CopyIndx.Type

  For each CopyIndxCol in CopyIndx.columns

   For each CpyCol in Tab.Columns

    If CopyIndxCol.name = CpyCol.name Then

     Set IndxCol = Indx.createobject(PdPDM.cls_IndexColumn)

     Set IndxCol.Column = CpyCol

    End If

   Next

  Next

Next

The key here is bold lines.  The index column object contains a link to the table column.  I'm not sure how to make this happen in an excel import.  Of course I tend to default to vb scripting rather than try to use the imports.

Tim

[former_member185199]

Hi Tim,

as you can see in my blogged articels I prefer it either,

but unfortunatly the Customer wants to get rid of too much vbs

and is heading to more standard solutions

cheers

dj