Clarity

First of all, my apologies, if this is a duplicate question.  I searched but could not find the similar question

CA-PPM version :15.1.3

Here is the OOTB XML for CMN_Users_Read.xml file 

<?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
<Header version="6.0.11" action="read" objectType="user" externalSource="NIKU"/>
<Query>
<Filter name="userName" criteria="EQUALS">admin</Filter>
<!-- Possible values "ACTIVE", "INACTIVE"
<Filter name="userStatus" criteria="EQUALS">ACTIVE</Filter> -->
<!-- Possible values "INTERNAL", "EXTERNAL"
<Filter name="userType" criteria="EQUALS">INTERNAL</Filter> -->
</Query>
</NikuDataBus>

I got the XOG output for the above XML but when I open the XML file from EXCEL, I get the below error message.  Attached the XOG output file for your reference

Any help is greatly appreciated

Thanks

-Venkitta

Hi Venkitta,

Why would you want to open .XML in excel, you can use tools like XML SPY or Notepad ++

Regards

Suman Pramanik 

Hi Suman

I do not have neither of the tool installed in my system.  I wanted to see the value for each XOG attribute from Excel.

Even, I tried XOG out for Project and Resource and I was able to open from Excel and I  did not get any error.

I got the error only for CMN_Users (XOG output).  

Is there any specific reason?

Thanks

The error seems related to strange characters in the file - for example accented "ó" and so on - do you have language packs loaded into your Excel installation?

Could open the XML file in a XML viewer (like IE) or just a text editor (notepad/notepad++) and look at what data is there and what "strange" characters have been produced.

Hi David

I get blank page when I open from IE.

When  I open the admin.xml from Chrome, here is the error message I get.  Basically, i wanted to see the XOG output in excel (rows and columns)

What is the language pack should I add in my excel?

As Dave said the rendering is blowing up the special character, have some online tool like https://codebeautify.org/xmlviewer  and see the file and get the notepad++ installed 

Hi Suman

I was able to  open in Notepadd++ without any error.  So, how do i view the output in excel format?

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_user.xsd">
<Header action="write" externalSource="NIKU" objectType="user" version="15.3.0.200"/>
<Users>
<User externalId=" " isLDAP="false" oldUserName="admin" uiThemeDefaultPartitionCode=" " userLanguage="English" userLocale="en_US" userName="admin" userStatus="ACTIVE" userTimezone="Europe/London" userType="INTERNAL" userUid="admin">
<PersonalInformation emailAddress="username@mailserver.com" firstName="CA PPM" lastName="Administrator"/>
<Resource resourceId="admin"/>
<BusinessObjects/>
<Company/>
<General addedBy="admin" addedDate="2001-03-12"/>
<OBSAssocs complete="false"/>
<Groups>
<Group id="admin"/>
<Group id="admin_role"/>
<Group id="ApplAdminRl">
<nls languageCode="ca" name="Administrador de l'aplicació"/>
<nls description="Správce aplikace" languageCode="cs" name="Správce aplikace"/>
<nls description="Applikationsadministrator" languageCode="da" name="Applikationsadministrator"/>
<nls description="Anwendungsadministrator" languageCode="de" name="Anwendungsadministrator"/>
<nls description="Application Administrator" languageCode="en" name="Application Administrator"/>
<nls description="Administrador de la aplicación" languageCode="es" name="Administrador de la aplicación"/>
<nls description="Sovelluksen järjestelmänvalvoja" languageCode="fi" name="Sovelluksen järjestelmänvalvoja"/>
<nls description="Administrateur de l'application" languageCode="fr" name="Administrateur de l'application"/>
<nls description="Alkalmazásadminisztrátor" languageCode="hu" name="Alkalmazásadminisztrátor"/>
<nls description="Amministratore di applicazione" languageCode="it" name="Amministratore di applicazione"/>
<nls description="???????????" languageCode="ja" name="???????????"/>

As you can see the xml any specifc reason for excel. What business case you are trying to achieve.

Regards

Suman Pramanik 

Here is the business case

Currently, we are using V15.1 but the business wanted to go with clean slate V15.3 instead of upgrade from 15.1 to 15.3 (the reason being too many customization in 15.1)

#1 - I wanted to validate the existing user data as per HRMS/Peoplesoft systems

#2- I wanted to give this excel output to the data migration team so I can ask them to populate the data and send it back to me for XOG write.

I hope this helps!

Hi Venkitta,

Can think of other ways 

1. Write a portlet and export to xls 

2. Write a report and export to xls

3. If you have database access write a SQL and spool it to xml

Regards

Suman Pramanik 

Good Idea.  But I need the Group and access right information as well for each user. 

Is there anyway, i can filter out only with the below condition in the XOG Read? To prevent other special characters?

            <nls description="Application Administrator" languageCode="en" name="Application Administrator"/>

Thanks

-Venkitta

No but you could just strip it out of your XML file before you tried to open it in Excel.

I agree with Suman though ; there are many other/better ways of doing this.

If you insist on trying to force it all into Excel, read this thread too ; How to use Microsoft Excel to Map Data into XOG Files, then Update Directly to CA PPM 

If you just want to get ride of all the other nls codes:

This xlst along with a simple powershell script will clean out the other nls nodes.

RemoveLanguage.xsl

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

     <xsl:output method="xml" indent="yes"/>
     <xsl:strip-space elements="*"/>

     <!-- Identity template : copy all text nodes, elements and attributes -->  
     <xsl:template match="@*|node()">
          <xsl:copy>
               <xsl:apply-templates select="@*|node()" />
          </xsl:copy>
     </xsl:template>

     <!-- When matching nls do nothing -->
     <xsl:template match = "nls[@languageCode!='en']" />


</xsl:stylesheet>

Powershell:

$xml = "Test.xml";
$output = "TestClean.xml"
$xsl = "RemoveLanguage.xsl";

$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
$xslt.Load($xsl);
$xslt.Transform($xml, $output);

Write-Host "Cleaned" $output;

To Run:

Source XML:

After transform:

V/r,

Gene

The problem is because of the nls tag. So if you only keep nls tag where language_code=en, then your file will work fine.

 try this <args name="nls_language" value="en" />  in your read file (not sure if it  works.)

Hope this helps.

So if you really want it in Excel:

Add this statement to the top of your xml file: <?xml version="1.0" encoding="ISO-8859-1"?>

You should then be able to open it in Excel:

I agree with Suman and Dave though ; there are many other/better ways of doing this.

Just for Fun,

Gene

Gene - Thanks for the code.  It worked and I was able to open in Excel

Thank you all for contributing to this question.