cancel
Showing results for 
Search instead for 
Did you mean: 

csv2tcxml/TCXML data migration in TC11.2.x

Hi everyone. I just wanted to start a discussion. How many people out there are using the csv2tcxml tool to convert your data for bulk data migration? I've been doing it for 8 or 9 months now. I've successfully moved 98% of my data migration tasks to TCXML but still have to clean up 2% the old way. I wanted to hear from other users how it is going for them. What have they got snagged on? I'm stuck with a pesky problem with ip & gov_classification and Participant users on Change Objects. I have a work around but TCXML is falling short in this area currently for my migration project. Fun fact, my previous load cycle was about 102 hrs and with TCXML bulk load it is under 4 hrs for the same data! 

 

Regards,

Jamie G.

22 REPLIES

Re: csv2tcxml/TCXML data migration in TC11.2.x

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom
Hi Jamie,

I could find very few people using csv2tcxml at Siemens PLM Connection 2016. You and OrbitalATK were the big hitters.

Best,
/Randy

Randy Ellsworth, Teamcenter Architect, Applied CAx, LLC
NX 11.0.1.mp01 | SW 2016 | TcUA 11.2.3
Evaluating:AW 3.2

Re: csv2tcxml/TCXML data migration in TC11.2.x

I'm in contact with the Orbital ATK team and we are sharing issues on csv2tcxml. I am hoping more people are trying to use it and if they've want to share any success or failures. The more people trying to use it the more it will get enhanced. 

 

~Jamie G.

Re: csv2tcxml/TCXML data migration

Solution Partner Creator Solution Partner Creator
Solution Partner Creator

Hi Jamie,

 

We are looking at useing csv2tcxm to replicate data from the Parts Catalouge / ERP system to Teamcenter using csv2tcxml. We have done some inital loads using IPS int a test environment and it took DAYS. (0.6 to 1 second per record for 1 million records).

 

However I feel we have jumped in at the deep end as everyting we are doing has multi field keys, which is being a bit of a challenge and not very well documented. Our target data model is the Aerospace and Defence with the Vendor Parts model.

 

This means that we have 3 types of multi Field Key in use:

  • Item{item_id}
  • D5_COAItem{fnd0OriginalLocationCode,item_id}
  • ManufacturerPart{item_id,vm0vendor_ref,vm0company_location}

The first is ok, but the second and third are a challenge as fnd0OriginalLocationCode and vm0company_location are refernces to CompanyLocation which are Workspace Objects, not Items.

 

I am not able to get csv2tcxm to create the CompanyLocation directly. The only way I have been able to create it is when I create a Vendor by mapping  a relationship that forces the creation of the CompanyLocation (this only works as our business rule is that it is a 1to1 relationship). The issue is that I do not seem to be able to generate the same PUID when I need to  re-refernce it. 

 

To overcome this I have used a lookup column...which generates the xml file with the correct PUID for the company location,  but now the tcxml converter complains as it cannot generate the correct key....which will give me further problems.. of that I am sure.

 

If anyone has any experience in  populating the vendor part model using csv2tcxml any feedback would be greatly appreciated especially:

  1. Creating and referncing CompanyLocation so that it can be re-refernced.
  2. Creating the GRM relationships where there are different "KEY"s on each side i.e: ManufacturerPart{item_id,vm0vendor_ref,vm0company_location}->TC_vendor_part_rel->Vendor(Item_id)
  3. Creating the MOM relationships where there are different "KEY"s on each side i.e: D5_COAItem{fnd0OriginalLocationCode,item_id}->D5_NSN(item_id)

Thanks

 

Peter

Re: csv2tcxml/TCXML data migration

Hi Peter. 

 

Let's see if we can work thru some of your issues. We also have MFK, but it is on Item ID and Domain and that is pretty simple - {mfk_columns|ItemSmiley Surprisedbject_type,parent_type,child_type}. What are you setting in the config file for MFK? Have you loaded all the 3 types you listed already with TCXML? If so I'm guessing you worked the config file issue out. I'd think once you have the correct format in the config file it will be easier to do the relation loads. If you haven't done the config file yet: I think you need to put them all in at once possibly using ; to seperate out the 3? It is hard to say as I only have one MFK I'm using currently. Maybe something like this? 

mfk_columns|Item:item_id;D5_COAItem:fnd0OriginalLocationCode,item_id;ManufacturerPart:item_id,vm0vendor_ref,vm0company_location

 

We also load Company Location objects. First I will load any Admin LOV's, Company Locations, etc before even starting other migrations, any workspace object that will need referenced. You will need to run this in as they are existing items with the gsid turned on. 

 

I added to the csv2tcxml_mapping file:

CompanyLocation|CompanyLocationSmiley Surprisedbject_name

 

CompanyLocation csv example:

!Item:item_id|ItemRevision:item_revision_id|CompanyLocation|CompanyLocation:fnd0LocationCode|CompanyLocation:fnd0LocationType|CompanyLocation:city|CompanyLocation:state_province|CompanyLocation:country|owning_user|owning_group
dummy||Cabin|12345|CAGE|Burbank|CA|USA|jgriffis|Configuration Management

 

Convert:

perl csv2tcxml.per <filelocation&name>.csv item=exist use_attrval_as_gsid=1

 

It will generate a <filename>.csv.gsid.out file. You will use the values after the comma as your value in your csv files that reference these objects - you mentioned fnd0OriginalLocationCode & vm0company_location. In the header for your import you need to use a $ to designate that as a reference object.

 

Example from my Part load file:

$ItemRevision:c5a_company

Cabin!CompanyLocation

 

Our relation load was easy with only one MFK type to worry about:

!parent_item|parent_rev|parent_type|child_item|child_rev|child_type|ImanRelation:relation_type
1234548-1|M|Part|1234548-1|M|Design|TC_Is_Represented_By

 

Maybe this will give you ideas to work with to get you moving. Let me know how it goes. Feel free to ask more questions.

 

I am now down to about 2.5hrs to upload data with TCXML vs. the 102 hrs (4.25 days) with IPS uploader. That was 2 million objects to the database. Not a huge amount of data but the time savings is amazing.

 

Good luck!
Jamie G.

 

Re: csv2tcxml/TCXML data migration

Solution Partner Creator Solution Partner Creator
Solution Partner Creator
Thanks Jamie. You have given me a very helpful push in the right direction.

Re: csv2tcxml/TCXML data migration

Valued Contributor
Valued Contributor

Hi Jamie,

 

2.5 hrs for 2 Million objects  -  Do you mean number of items or number of POM Objects?

 

Have you had to make any database changes to get this performance?

Re: csv2tcxml/TCXML data migration

Solution Partner Creator Solution Partner Creator
Solution Partner Creator

Hi Jamie,

 

Thanks for the tip with creating the CompanyLocations. That worked perfectly.

 

As you suggested, the issue is with the mfk_columns variable. With the mfk Vendor(item_id, object_type), I set the mfk_columns variable as:

mfk_columns|ItemSmiley Surprisedbject_type,parent_type,child_type

 

and then process the Vendor.txt with the following content using the option “item=exist”:

!Item:item_id|ItemSmiley Surprisedbject_name; ItemRevisionSmiley Surprisedbject_name|ItemSmiley Surprisedbject_type|ItemRevision:item_revision_id

ABCDE|ABCDE|Vendor|A

Z1234|Z1234|Vendor|A

 

The Vendor.txt.gsid.out file contains:

H79ttWhefLNARC,ABCDE!Vendor

B6kTKLJYfL9b6D,ABCDE!Vendor!A

ylpUL6y5fLdIOA,Z1234!Vendor

_kgHmPm2fL9FnA,Z1234!Vendor!A

This is what I would expect

 

If I change to:

mfk_columns|VendorSmiley Surprisedbject_type,parent_type,child_type

I get:

LhWs57HafLtbKA,ABCDE

n$eXvhQ5fLdRMA,ABCDE!A

vzXCe6mRfLdqdB,Z1234

8epYoWREfL9N0C,Z1234!A

 

With the ManufacturingPart (item_id,vm0vendor_ref,vm0company_location)

I set:

 mfk_columns|ItemSmiley Surprisedbject_type,ManufacturerPart:vm0vendor_ref,ManufacturerPart:vm0company_location

 

and then process the ManufacturerPart.txt with the following content using the option “item=exist”:

!Item:item_id|ItemSmiley Surprisedbject_type|ItemRevision:item_revision_id|$ManufacturerPart:vm0vendor_ref|$ManufacturerPart:vm0company_location|ItemSmiley Surprisedbject_name; ItemRevisionSmiley Surprisedbject_name
X123-17|ManufacturerPart|A|ABCDE!Vendor|ABCDE!CompanyLocation|X123-14
X123-17|ManufacturerPart|A|Z1234!Vendor|Z1234!CompanyLocation|X123-15

 

This file should produce 2 unique ManufactureParts, however it generates an message that there are 2 duplicates. The ManufacturePart.txt.gsid.out file contains:

rKCWvRqjfL9u6C,X123-17!ManufacturerPart
mBk_63FQfLtTVA,X123-17!ManufacturerPart!A
H79ttWhefLNARC,ABCDE!Vendor
PbVlpbjZfLdMXA,ABCDE!CompanyLocation
ylpUL6y5fLdIOA,Z1234!Vendor
VPjaA2EUfLtbuD,Z1234!CompanyLocation

 

In the xml file there is only one ManufacturerPart. Only the last one is processed.

 

If I change the order of the key attributes to:

 

mfk_columns| ManufacturerPart:vm0vendor_ref,ManufacturerPart:vm0company_location, ItemSmiley Surprisedbject_type

 

The ManufacturePart.txt.gsid.out file contains:

Kp6EkAiNfLd5lD,X123-17
lLGX3NbNfLNJCA,X123-17!A
H79ttWhefLNARC,ABCDE!Vendor
PbVlpbjZfLdMXA,ABCDE!CompanyLocation
ylpUL6y5fLdIOA,Z1234!Vendor
VPjaA2EUfLtbuD,Z1234!CompanyLocation

 

I have logged a call with GTAC requesting more information on the use of the MFK_columns variable.

 

Regards

 

Peter

Re: csv2tcxml/TCXML data migration

POM Objects. Datasets are copied into the volume at the OS level. As long as they are in the volume before you attempt to open the dataset, it works. I believe there is a way to make TCXML move the datasets but the would be slow just like IPS uploader. I haven't tried it that way.

 

Jamie G.

Re: csv2tcxml/TCXML data migration

Interestingly when I previewed my post there wasn't any smiley faces but now that I see your post there are some in both of our post. Html fun. I guess you understood it was a semicolon followed by a "o". I'm glad you go the Company Location working!

 

The item=exist switch is only for when you are putting in workspace objects, datasets & relations or updating existing items. If you need the Items, Manufacturing Part and Vendor Item to be created, don't use that switch. You also can ignore the gsid values for anything you aren't going to be using as a reference. I left it on for all my conversions but didn't need it. 

 

I thought your MFK was going to be tricky. Hopefully Gtac can connect with the Dev group on this one and get you something that works. I will think about it more today and send you any ideas I have. MFK for csv2tcxml is not well documented at all in the bulk loader class or in the online documentation.

 

Jamie G.