Retrieving cell value in Excel

Help Activities
1

Hello Everyone! I am currently building a robot that needs scraping data table from the websites.

Now I have the data table scrapped but some of the value are too messy. May I know how I can use the for each row activity to retrieve every cell value of the column “USD” so that I can perform string manipulation to remove all extra texts.

Thank you so much for your help!

Financial Data.xlsx (10.8 KB)

2

Hi @Komom

This image depicts an automation workflow in UiPath that reads an Excel file and iterates through each row in the data table to write the value of the "USD" column to the output. (Captioned by AI)
This image depicts an automation workflow in UiPath that reads an Excel file and iterates through each row in the data table to write the value of the "USD" column to the output. (Captioned by AI)848×612 23.9 KB

Regards,

1 Like
3

Hi @Komom you can follow below steps :

  1. Use read rangeworkbook activity - give file name sheet name and range as “”. With has headers as yes /true
    2.store the result into data table variable
  2. Use for each data row activity and pass this table to iterate.
  3. Assume you have variable declared currentrow in for each activity
  4. You can have assigne value inside for each loop and in the right side of assign value you can pass the string like below
  5. Currentrow(“(USD)”).trim.tostirng
    And it will give you value

Hope this helps !
Happy automation ! :blush:

1 Like
4

Hey @Komom , could you please let us know exactly what are the extra texts you want to remove from the table under the USD Column? We can then also help you on finding a solution through string manipulations or Regex.

Meanwhile I can guide you one how you can use the For Each Row Activity:

  1. Search the For Each Row in Data Table Activity in the activity panel and drag it on to your sequence.

  2. Next you will need to specifiy the datatable you want to iterate over. For example, let’s say you have read the Financial Data.xlsx file and have stored that data into a datatable variable named dt_financialData, then you can add this datatable in to the For Each Row in Data Table activity in this manner:
    image

  3. So the activity will convert the datatable into Enumerable rows through which you can then access the values in the table. Now if you want to access each value from under the column USD, then inside the loop you can make use of this expression i.e. CurrentRow(“USD”).ToString
    image

This is just a gist of how to use the For Each Row in Data Table activity.

Let us know if you need any help regarding the String Manipulations.

1 Like
5

Thank you so much for your help! Basically I just want the first line of every cell under the USD “column”

For example, there is a super long description for “Revenue” start from the second line in the cell and I want to remove it is unnecessary and also making the cell unreadable.

1 Like
6

@Komom

Can you try this

The image illustrates an automation workflow where data is read from an Excel workbook, processed to increment the "(USD)" column values, and then written back to another sheet in the same workbook. (Captioned by AI)
The image illustrates an automation workflow where data is read from an Excel workbook, processed to increment the "(USD)" column values, and then written back to another sheet in the same workbook. (Captioned by AI)832×645 35.4 KB

CurrentRow("(USD)") = CurrentRow("(USD)").ToString.Split(Environment.NewLine.ToCharArray)(0)

Output:

The image displays a financial summary for Q3 FISCAL 2024, reporting various financial metrics such as revenue, operating expense, net income, net profit margin, earnings per share, EBITDA, and effective tax rate, along with their year-over-year changes. (Captioned by AI)
The image displays a financial summary for Q3 FISCAL 2024, reporting various financial metrics such as revenue, operating expense, net income, net profit margin, earnings per share, EBITDA, and effective tax rate, along with their year-over-year changes. (Captioned by AI)1086×430 9.5 KB

Regards,

7

Actually you are not required to read range to read all table.

  1. Find the last row of excel
  2. Starting from for loop 2 to last row
  3. Read cell in column(idx)
  4. So that you can only retrieve specific cell value for each in the excel table
8

Hey @Komom,

I’ve understood what you’re trying to achieve. We can achieve by making use of the .Split method. We can Split the content (String) into an array of substrings using newline characters as delimiters.

The Expression to use:
CurrentRow("(USD)").ToString.Split(Environment.NewLine.ToCharArray).First

This retrieves the data in the current row under the column USD data as a string, splits it into an array at each newline, and returns the first substring

image

You can make use of this expression in the assign block activity in the following manner:
image

Regards.

9

Hey @Komom ,

Alternatively, I’d recommend you try using LINQ. You can achieve the same in just one assign block.

Make use of the following LINQ:

(From row In dt_financialData
Let usd=row("(USD)").ToString.Split(Environment.NewLine.ToCharArray).First
Select dt_financialData.Clone.Rows.Add({CType(usd,Object)}.Concat(row.ItemArray.Skip(1)).ToArray)).CopyToDatatable

Use it in the assign block in this manner:
image

Your final Excel Output will look like this:

image
image860×181 6.25 KB

Regards.

10

@lrtetala Thank you so much! It helps. Do you mind briefly explain what does Environment.NewLine.ToCharArray means?

11

@Komom

Environment.NewLine.ToCharArray() is the way to split the text whenever a new line (or line break) is found

Regards,

1 Like
12

@lrtetala Thank you so much!

13

Hi @lrtetala Actually I just found a problem: As the currency is not always USD, but the data I need to manipulate must be at the first column. May I know how can I change to specify the first column instead of fixing it to the “USD” column?

14

@Komom

We can do by using column index

CurrentRow(0) = CurrentRow(0).ToString.Split(Environment.NewLine.ToCharArray)(0)

Regards,

1 Like
15

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