Import excel file using Integrity 11.2

[ryan_yamamoto]

Is there a way to import excel files into a document, using Integrity 11.2.

We are looking for a feature that would allow creation of new line items and update any existing line items, if the Item ID matches

[LLawton]

Yes, it is possible and not too difficult.

I am attaching an example from a mapping file I use for Test Suites (on 11.2). Please note that most fields are our own custom fields that must be removed or replaced with your own. The basic ones (no "ALM" prefix!) are there.

Also note the use of "_" characters to replace spaces in field names on the Excel side, that's because of an Excel limitation.

The simplest way to test it is to create a Test Suite in Integrity with one Test Case, and import it in Excel using the "Get" function. You're prompted for the document ID.

You can then remove all the columns you don't want or reorganize them (another Excel limitation).

Any items with an "Item_ID" are updated when you synchronize, lines without a value are added to the document. I'm pretty sure the Type and Project are automatically set from the document, you don't have to set them in Excel.

I hope this helps.

[ryan_yamamoto]

I have installed the Excel Integration, but not familiar with how to use this plugin still.  Where can I get some instructions on how to use this integration?

With the provided template, where does it go on the server?

[LLawton]

The mapping file goes under "...\data\gateway\mappings" on the server. It could also go on the client, I believe.

The only useful documentation I have is from an old version ("IntegrationsUserGuide_Integrity_10_4.pdf"), I don't think PTC updates it anymore, at least I haven't found it. It still applies to 11.2.

I'm attaching it here.

[ryan_yamamoto]

Thank you for the assistance so far.  I was able to use the Get Items function and synchronize based on my mapping file.

I have a couple questions regarding the capabilities of this Excel Integration..

1. Is it possible to keep the item ordering between the two?  I noticed that inserting a new line in excel will just append to the bottom of the document Integrity.  Same thing would apply vice versa.

2. Couple of the fields that we have in the system are picklists.  Is it possible to define a picklist in the mapping file to make it easier to select in the Excel file?

[LLawton]

Glad to hear you're making progress.

1) The Excel integration doesn't update the structure of the document. You're correct, new items are simply appended. However, when you correct the structure in Integrity, then the next get will have the correct order.

I think the last time I used it, I did that: a) get correct document in Excel, b) add new items and synchronize, c) update structure in Integrity, d) remove all items from the Excel table (except heading/mapping), then perform a get.

2) You cannot define a picklist in the mapping file. You can define one in excel, though. As long as you have valid values, that should work. For this kind of functionality, I think I would create an Excel template file, with a predefined mapping: don't do a "get", but a simple "import mapping", which allows you to pre-define an empty table where you could set your picklist(s). Then when you create a new file from the template file (copy or save as), you do a synchronize and not a get, and you have you predefined picklists.

I'm including an old 10.4 IntegrationsUserGuide that contains some details in Part III > Microsoft > Excel. I think you should have it anyway. Now you have all the documentation I have!

I hope this helps.

[skamaraj]

Hi @LLawton 

You should be very careful in handling the Excel Synchronization. Below are some stuff you need to take care,

1. No blank row is allowed at middle

2. Project and ID should always be there in excel for sych

3. Mapping should be available in Server location. 

4. Require attribute for exchange "in or out" should be configured in map file (so better create some template for each doc type)

5. Synchronization could not be stopped. You should be very careful. The sych will be done at server side, so in case of any issue, you need to restart the Integrity Service

6. Richtext could be supported, if each rich text cell holds the XHTML tags.

7. Samelevel hierarchical import should be avoided as this creates more IMAP in database

Alternatively you could try out the CSV import. This is quite easy and picks could also be handled with comma separated. I usually do CSV import first and then will update the rich content with Excel Sych.

- Copy JAR files to lib location

- Modify batch file content

- This runs based on mksapi.jar file

[LLawton]

Hello,

Thanks for the response and details.

I don't agree with all the points you make but this is not too important. Please note that I replied to Ryan, personally I don't have any problems with the Excel Integration.

I appreciate your including your CSV import file, I'll have a look as soon as I can.

[kokici]

Hi,

you have said that you have got rich text content imported using Excel Integration. 

To be honest this is quite new to me and it would be interesting to know how you got this working?

Thank you!