Question
Pegasystems Inc.
JP
Last activity: 4 Oct 2018 13:54 EDT
OpenSpan7.1 How to lookup values from a table of record which is stored in an excel workbook
I wanted to convert a code to its name (ex. 001 -> USD, 002 -> GBP).
The code & name pare is stored in an excel work book and I have implemented an automation which loops over all lines of record and return the matching name.
However, the automation looks a bit combursom and I wanted to know if there is any smarter way (like excel lookup function), where I don't need to implement a loop at all.
***Updated by moderator: Lochan to add Categories***
***Updated by Moderator: Marissa to update categories***
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems Inc.
US
Hi Chunzhi,
If your codes are unique, you can use the Excel connector to export the data to an OpenSpan lookup table. Assuming your codes are in column A and your names are in column B, create a lookup table with 2 columns - Code and Name. Then export the region as shown below:
Once the data is in the lookup table, use the code to lookup the name.
Pegasystems Inc.
IN
Hello Chunzhi,
I dont think we have any API that can help you do a kind of auto looping ! How are you looping thru the excel ? Are you loading the excel in the applicaiton first and store it and then looping thru or directly looping thru in the excel ?
Pegasystems Inc.
US
Hi Chunzhi,
I don't know of a good way to do this easily. I might ask why you are storing this data in Excel. You might find it easier to have it in a local data storage or data table (depending on your version of the Pega Platform). Then you could convert the data on the list with a data transform.
Thanks,
Mike
Pegasystems Inc.
US
Hi Chunzhi,
Can you please share a screenshot of the autx that you are currently using? Also, what are you referring to when you state "code & name". Are you referencing worksheet code function or an associated name for a worksheet?
Thanks,
--Zach
Pegasystems Inc.
US
Hi Chunzhi,
Unfortunately, there is not a "smarter" way of traversing an excel sheet. There is the option of using the script component and coding the traversal with C# code which may be a cleaner solution but both functions will be doing the same thing.
--Mitchell
Accepted Solution
Pegasystems Inc.
US
Hi Chunzhi,
If your codes are unique, you can use the Excel connector to export the data to an OpenSpan lookup table. Assuming your codes are in column A and your names are in column B, create a lookup table with 2 columns - Code and Name. Then export the region as shown below:
Once the data is in the lookup table, use the code to lookup the name.
Accenture Solutions Pvt Ltd
IN
Hi Jeff,
Could you please explain how to read the excel records(horizontal /column wise e.g,A1 cell value then B1 cell value etc) using Forloop & listloop?please give some example for this
Pegasystems Inc.
IN
This question is also asked here: Openspan related doubts
Binghamton University State University of New York
US
Hi Jeff
I got this but is there some kind of a way in which I can get the name of a particular number as required rather than looping all round the table?
Zensar
IN
How do we use the code property and from where do we choose the GetRecord property?
My requirement is :
I have a String1 as "E/M_ACCESSORY KIT_SOFTWARE MEDIA"
I need to find the String1 in the excel (attached) if found, get the corresponding "Commodity Manager".
Pegasystems Inc.
US
To do this you need an automation to convert a number to a column letter. Here is an automation that does that that I call EL_F_Column_GetLetter. It takes a 1-based number and converts it to an Excel column.
Now you create an automation that uses that to convert a ForLoop index to a column. In this example it will read column A - Z.
Accenture Solutions Pvt Ltd
IN
Thanks Jeff for the reply,but actually am facing issue when connecting forloop with Excel connector method Getcellvalue,it is not returning any record or value.the loop itself is not running.Could you please show me the example for Excel connector Getcellvalue & setcellvalue method using forloop?
Pegasystems Inc.
US
GetCellValue needs a cell as input, for instance B1. In the example above the stringUtils.Concat method concatenates the Column (for example B) with the row (for example 1) to create B1. In the example above EL_F_Column_GetLetter converts a number to a column letter.
Accenture
IN
Binghamton University State University of New York
US
i am unable to do this can you be a bit more elaborate please. what do I select for database name?? is it the xlsx file im using here ??
Accenture
IN
Binghamton University State University of New York
US
Hey Jeff, what do you mean by "code " and "name" here? and from where and how did you get them
Westpac Banking Corporation
AU
By default all methods are not displayed in object explorer so selected required component in object explorer and click on icon as shown in the diagram and select all method from the lookup window or search required method and select then OK.
