Clarity

I wanted to share an easy way to generate XML files directly from excel using its native functionality.  I created a tutorial video and wrote step by step descriptions.

for the Youtube Video click on this link https://youtu.be/ABpaHelBNGI

for the step by step descriptions are available here

http://www.itroisolutions.com/ppm-blog/how-to-use-microsoft-excel-to-map-data-into-xog-files-then-update-directly-to-ca-ppm

happy xogging.

Federico

Absolutely awesome Federico. Thank You!

Thanks for sharing this, Federico

Have a nice weekend

Regards,

NJ

fpena

In the video it works just fine like there is nothing to it.

When I try to do that with users first I get

which you did not get.

I do have two resources.

When I refresh the XML data I get more rows, at least one for each group membership and maybe even more for the Global etc rights.

Then when I try to export it without any modification at all I get

There must be something more to it.

Tried with Excel 2010 and 2016 with the same results.

Do you have instructions for Dummies?

Hi Urmas,

the errors you see are normal, and probably related to the path of the xsd that is in the top of your xml.

as for the multiple lines, yes you must have a simple xml, and that is probably what is causing your issue.  Remember you want to have an xml that can easily be mapped to a single record (1:1) or it will not know how to handle this.

I will try to figure out how to do nexted tags in the excel file, but have not been able to yet.

Just as an FYI, the XQ will soon have that functionality built in (available here https://xogbridge.itroisolutions.com/ ) this will allow you to build more complex xml files.

hope this helps

Hi.

You may excel get many resources dynamically, as in the following example?

<Resources>

        <Resource resourceID="joeTime" availFrom="2003-01-01T00:00:00" availTo="2003-03-01T00:00:00" openForTimeEntry="1" bookingStatus="5" requestStatus="1" capitalPercentage="0.5" defaultAllocation=".7" projectRoleID="manager"/>

       <Resource resourceID="joeTime" availFrom="2003-01-01T00:00:00" availTo="2003-03-01T00:00:00" openForTimeEntry="1" bookingStatus="5" requestStatus="1" capitalPercentage="0.5" defaultAllocation=".7" projectRoleID="manager"/>

  </Resources>

Thanks fpena

That got me over  a couple of hurdles, but not all.

My input file is

So I put the xsd file so that the path in the file matches. Apparently it would work if you had just one XOG command line client on your PC and the xml file you are working with were in a subfolder of the bin folder. But I can live with adding a xsd folder for each client I have unzipped.

Secondly for the brothers Ueberoth there are no OBS associations, groups, rights.

Now the source is added even through the error is still comes.

Then I just copy pasted the lines to get more data quickly as you did in the video.

The coloring is faint so maybe you cannot see that only the line in the original dataset is colored. The added lines are not.

No error when exporting.

However,

the result is not exactly as desired: it is the orginal data only.There is still more to it.

That would be the final hurdle to cross, before I can overwrite the users in my system with data having correct locales and timezones.

Hi Urmas,

now that you have it working, i would recommend just writing a query to export the data, and paste it in to the excel.

If you do not have access to the DB, you can always use the XQ on the link i mentioned above.  It has both pieces a XOG and a data extraction component.

the query would be something like  (remove the columns you do not need.

SELECT

@SELECT:DIM:USER_DEF:IMPLIED:USERLIST:cmn.id:KEY_ID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:srm.user_id:KEY_USER_ID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:cmn.first_name:FIRSTNAME@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:CMN.USER_NAME:KEY_USER_NAME@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:cmn.last_name:LASTNAME@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:cmn.email_address:EMAILADDRESS@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:srm.unique_name:KEY_RESOURCEID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:cmn.locale:USERLOCALE@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:cmn.timezone:USERTIMEZONE@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:LOWER(case when cmn.is_ldap = 1 then 'true'when cmn.is_ldap = 0 then 'false' else NULL end):LWR_ISLDAP@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:UPPER(case when cmn.user_status_id = 200 then 'ACTIVE' when cmn.user_status_id = 201 then 'INACTIVE' when cmn.user_status_id = 202 then 'LOCK' end

):USERSTATUS@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:UPPER(case when srm.is_external = 0 then 'INTERNAL'when srm.is_external = 1 then 'EXTERNAL' end):USERTYPE@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:SUBSTRING (NBI.PATH,1,4):UNITPATH@, 

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:OBST.UNIQUE_NAME:ID@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:USERLIST:OBST.NAME:NAME@

FROM CMN_SEC_USERS CMN

LEFT JOIN odf_ca_resource odf ON odf.id=cmn.id

LEFT JOIN srm_resources srm ON srm.user_id=cmn.id

LEFT JOIN cmn_languages lan ON cmn.language_id = lan.id

LEFT JOIN PRJ_OBS_ASSOCIATIONS ASO ON ASO.RECORD_ID=srm.user_id AND ASO.TABLE_NAME='SRM_RESOURCES'

LEFT JOIN NBI_DIM_OBS NBI ON NBI.OBS_UNIT_ID=ASO.UNIT_ID

LEFT JOIN PRJ_OBS_TYPES OBST ON OBST.ID = NBI.OBS_TYPE_ID

WHERE 1=1 

AND @WHERE:SECURITY:RESOURCE:cmn.ID@

AND @FILTER@

Hope this helps

Federico

Sorry to say, it is not working.

I already have the data, but when I paste it to Excel and export only the original two records get written.

feel free to send me an email and i will try to walk you through it.  that is OOB functionality of excel, so it should just work.

my email is fpena@itroisolutions.com

cheers,

Federico

Thanks again fpena

The final bit was just to copy only cells and not lines when I create quickly more data.

Now I can make it work as smooth as you did in the video :-)

Just to recap my hurdles:

The locations of the files must be such that the location of the xsd file at the beginning of the xml file is valid.

The xml file must have simple non-hierarchical structure so for eg. users remove groups, rights, partitions, OBS etc.

Excel will give an error about the schema which is OK as you want Excel to create the schema.

Once you have go your data populated on the spread sheet and want more and want to use copy, do not copy lines but only cells from the columns where you already have data.

Hi Urmas,

just as an FYI, the location of the file is not important. although you might see the error, it still works just fine.  it is just telling you that it cannot validate against an XSD file.

i'm glad you were able to get  it to work!!

cheers

Federico

Thanks, as this functionality is really useful. What I prefer doing is repeat the code via mail merge and xog that in.

If you have a simple set of existing data which creates single level xml and you want to add data/records then this is at its best.

I do use mail merge formulas in Excel when they are more fit.

The first thing I used was Actuate to output the report in xml.