Deleting Rows in ExcelToCI

I recently had a situation where I needed to remove a security role from a large amount of users. While I could have done this with SQL doing a delete something like this.

DELETE FROM PSROLEUSER WHERE ROLENAME = ‘ROLE TO REMOVE’ AND ROLEUSER = ‘THE_USER’;

This would not trigger the integration broker messages that publish out to all the subscribing databases. So I was going to use the ExcelTOCI to remove the role. I had actually never done a delete before with ExcelToCI and I realized that the delivered ExceltoCI macros are not coded for a row delete. However, the excelTOCI just submits SOAP messages that end up getting parsed by the SOAPtoCI application package which looks for a “CiNodeAction” attribute and it will process a row delete. You can see the code in the SOAPTOCI: SOAPTOCI application class.

You just have to modify the VBA Macros that actually create the SOAP XML that get submitted to the Weblib.

1. While you are in your ExceltoCI template go to Tools | Macros | Visual Basic Editor
2. Open the StagingAndSubmission Module
3. In the WriteCollectionHeader function do the following

You will see a case statement “Select Case lScrollLevel”

Change the “Case 1” statement

 sXmlLine = sIndent & "<" & sXmlTag & ">" 

To

 sXmlLine = sIndent & “<” & sXmlTag & " CINodeAction=""DELETE"">" 

This worked for the USER_PROFILE component interface because the ROLEUSER table shows up at level1. If you need to delete a level 2 or 3 row then you need to basically make the same change on those Case statements.

This was a quick and dirty solution and you would probably want to make sure you did not give this modified macro to anyone because you could do some damage in the database.

http://www.cedarhillsgroup.com/knowledge-base/kbarticles/deleting-rows-in-exceltoci