Load data from excel sheet to clarity

[Anon Anon]

I have a requirement to read data from excel and xog the data into clarity.I need to write a process that runs on demand and load the data into clarity. If you have a sample code please do share it.

Thanks

 

[Atul.Kunkulol]

If it is one time.. just use a MS word mail merge functionality to map Excel data to XOG script. Then paste this in the XOG Client. Fastest way to get the data in...  Works fine. 

If it is not one time,

1. then create a macro within the Excel which can do the same

2.create a CSV or TEXT file on a FTP folder and then have the process/gel script read this and XOG it in.

[Anon Anon]

atul.kunkulol:

If it is one time.. just use a MS word mail merge functionality to map Excel data to XOG script. Then paste this in the XOG Client. Fastest way to get the data in...  Works fine. 

If it is not one time,

1. then create a macro within the Excel which can do the same

2.create a CSV or TEXT file on a FTP folder and then have the process/gel script read this and XOG it in.

Thanks for the reply. My requirement is Users will attach the excel sheet to collab tab and I need to automate the script to xog data from the excel to clarity.When I create VBA macro in excel where does it save xml files? and Is there a way to automate that process. Problem here is excel sheet will be attached to collab tab after all the changes are done in local machine so I was wondering how to run macro when doc is attached to collab tab. So I thought GEL script would be more ideal in this scenerio but I may be missing something.

[Atul.Kunkulol]

Based on your requirements, Gel would be more appropriate. Convert your Excel Data to a XML format using a tool or through Macro's. Train the user run this macro. Get them to save the XML file to Clarity. A few options - Save it as a attachment field on a custom object or a related object to the data. Then in the process you can read this XML file and XOG in this data.

 

[Anon Anon]

atul.kunkulol:

Based on your requirements, Gel would be more appropriate. Convert your Excel Data to a XML format using a tool or through Macro's. Train the user run this macro. Get them to save the XML file to Clarity. A few options - Save it as a attachment field on a custom object or a related object to the data. Then in the process you can read this XML file and XOG in this data.

 

I would actually disagree with most of this statement - sorry!

"Based on your requirements, Gel would be more appropriate." - not really ; GEL is only a tool when we need the programtical control to be part of the Clarity application, XOG is the key tool in this requirement ("Load data from excel sheet to clarity") not GEL and XOG can be called from "anything" that can call SOAP web-services. 

"Convert your Excel Data to a XML format using a tool or through Macro's." - if you are doing this (writing an Excel macro), then you might as well add into that macro the call to the XOG webservice to load the data to Clarity - job done! This is what I was suggesting earlier but this does not accomplish the "upload the XLS as an attachment" part of the use-case so unless that is really part of the requirement (rather than just solutionising the requirement) I would challenge it.

"Get them to save the XML file to Clarity" - an XML file is relatively meaningless to a non-technical user, getting them to process an XLS and then upload to Clarity a file is just a ugly complication; the use-case is either that they want to upload data from an XLS to Clarity or that they want to save an XLS to Clarity and then interpret that data (but this is the part of the use-case I am challenging).

"Then in the process you can read this XML file and XOG in this data" - ugly.  Ideally the GEL want to read the XLS from the database/filestore, if the filestore is on a drive (rather than the database) then i could image a GEL script being able to read it directly, otherwise a GEL script could read out (using XOG) the XLS to somewhere it can reference and then process it (line by line).  NB this is still "ugly" in my opinon.

 

 

[Anon Anon]

clarityhelp123:

I have a requirement to read data from excel and xog the data into clarity.I need to write a process that runs on demand and load the data into clarity.

I would question why you think that you "need to write a process that runs on demand" to satisfy your requirement "to read data from excel and xog the data into clarity"?

A process based solution would work, but is perhaps one of the more complicated ways of doing this. As Atul has suggested, a VBA macro running against your XLS would be a better way of doing this (or indeed any sort of 'code' that can read the XLS and call a SOAP message would work - don't get hung up on needing to write processes/GEL scripts in order to manipulate data through XOG)
 

[Eugene Greiff]

Not completely sure this would work but I most likely would push down this path.

Create a custom java job that using the Apache POI or SmartXLS that is able to parse the data elements out of the Excel spreadsheet.  This job would need a couple of inputs: which object gets the parsed data and the file to parse.

Ref: https://communities.ca.com/web/ca-clarity-global-user-community/message-board/-/message_boards/view_message/100149518

Create a process which contains this job as a step.

Write a Gel script that uses jelly:com.niku.bpm.gel.BPMTagLibrary to fire off the process that contains our custom java job.

Ref: http://www.linkedin.com/groups/Anyone-know-run-CA-Clarity-2076955.S.191719251

As Dave mention, if your installation is using the database for the file location you are going to need to XOG out the spreadsheet to a directory that the custom job has access to.

AS for “automate that process”, I am not sure if there are any event handlers for the collab tab if so attach the Gel script to the upload event handler. 

If not, I then I would probably skip all this and just write an external job to do this and schedule it say every 15 mins.  The job would check to see if anything has been upload in the past 15 mins and if so then do the parse / upload on that object.

 

V/r,

Gene