Read random cell from Excel for transaction data

Help
1

Hi,

I have an excel like below. The fields in white will have data entered by end user. I need to read these data and use it as a transaction data.

Can someone please help

image

1 Like
2

Hi @mohammedamaan,

I hope read cell activity will help in this situation.

Read particular cell you required and store the value in a Variable.

readcell
readcell865×429 13.9 KB

Regards
Vishnu

3

Hi @winningvish,

This approach doesn’t seem to be good as I have so many fields and for each field I will have to use Read Cell. And also for transaction data i am using data table.

4

You can use dictionary variable, if the fields are aligned one after one just like in config file.

To achieve that, as per my knowledge you need modify the excel template.

Regards
Vishnu

5

If that would have been the case I could have directly used it as a datatable. The excel template cannot be modified as it is coming from the end user.

6

Ok @mohammedamaan,

I hope in that case you need read cell activity. Sorry I am not sure either.
I faced the same situation with less values I have to read from excel. (Used Read Cell Activity)

But I wish you will find, the better solution than mine.

Regards
Vishnu

1 Like
7

The image what I have shared is partial. There are values down and also there is another similar sheet as well from where I need to read data.

Anyways thanks for the response.

8

Oh then reading so many Excel cell values is not a good solution.

Kindly share if you find the solution.

Welcome

Regards
Vishnu

9

Yeah sure. Let see if someone can help us.

1 Like
10

If you can’t change the input template, then the only way to do it is to utilize a ton of read cell activities. You can of course read the whole datatable and manually choose specific row/column information, but it is cleaner & clearer to do a bunch of read cell activities instead. If it is going to be a ton, I would recommend putting it into a list or dictionary as advised earlier

EDIT: Another option would be to use a macro to extract the information and put it in a better table format, then use read range on that

1 Like
11

There is a possible workaround, if you prefer.

  1. Read the entire datatable
  2. Loop through each row and convert the elements of the row to array (row.ItemArray)
  3. Loop through the items of the array created, and use switch case to identify headers.
  4. if any element is matched in the switch expression, then assign the header and its corresponding value (index of header + 1) to a dictionary.
    You will now have your required data in dictionary.

(or )

  1. follow steps 1 through 2
  2. Add the headers in a string collection
  3. loop through the elements in the array created at step 2 and check if item is found in headers collection.
  4. if yes, add the item and its next value to the dictionary.

By this you can avoid hitting the excel file with lots of read cells and will also be useful if the cell positions change (works as long as key and value are adjacent to each other).

12

If you read it as data table how can you pass the particular data as input to the bot?

13

@mohammedamaan Is this query resolved?. If yes, please mark the solution

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.