StudioX - Find Last data row in excel file

Help
,
1

I have an excel workbook which contains data, with blank rows inserted in between data rows. I need to read this excel and insert new data after the last data row.

How do I find the last row with data in UIPath StudioX?

2

i have never tried with studio x cases like this.
maybe you can use this example as a reference.
at least has its logic

3

Thank you for your response. Unfortunately studioX doesn’t have any DataTable variable type and doesnt allow ínvoke code.

So wondering how to do this using the limited functionality that StudioX has.

4

@ayyagari.sudhakar - I was able to achieve it… using “Find First/Last Data row” You can find the last row in the column and you can mention in the properties like how many consecutive blank row you are expecting in that column , if my case I stated 50. Note: you can mentioned max of 99 blank rows consecutively.

After that, I saved that value, then using “Save for Later” Activity added + 1 (to write the new cell value).

Please find the sample xaml and output file: StudioX_FindLastRow_Excel.zip (106.5 KB)

Please mark as solution, if this helps to solve your query.

5

@ayyagari.sudhakar - Did you get a chance to try the provided solution?

6

Hello @prasath17, apologies for the delayed reponse. I have been trying to get latest version of StudioX.

In my version 20.4, I dont see the Find First/Last Data Row’’ activity. Can you please let me know which version of StudioX you are using?

7

@ayyagari.sudhakar …I am using 2020.10.2…Please check whether you can update the uipath.excel.activities to the latest version…

image
image1079Ă—129 10.3 KB

8

@ayyagari.sudhakar - I was able to achieve it with out using Find Last Row/Column.

Here is the xaml. FindLastRow_20.4.zip (647.3 KB)

Idea is simple: I wrote the excel formula on the cell as: =LOOKUP(2,1/(B:B<>“”),ROW(B:B))

This will give the last row count. Then i added +1 and saved it to a variable.

Finally wrote the output text using the variable ex: B34.

Please test this and let me know.

2 Likes
9

@prasath17 Brilliant! Thanks a lot for this formula. This works perfectly for my case.

Also, looks like the 20.10 version has many more functionalities than 20.4. Will try to get the latest version.

10

@ayyagari.sudhakar…thanks for the confirmation…yes until you get 20.10 version you can use this …

11

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