How to capture lastrow of a super long excel table and type into WEB page?

[yangyq10 (IvanY)]

Dear Experts

I have a super long table need to be handled each time

First, I need to capture the lastrow of column I and type into WEB page

And then, I have a output(a single data value without difference) from other system which need to fill in from Column K(lastrow of K+1) to K(lastrow of I)

If the rows number are small, I can quickly get the output of lastrow of Column I

But since my daily report rows are large, it takes super long time to get the lastrow in it with my below solution

May I know if there is any other way to get the lastrow from a super long excel table fast?
Or any other solution to fulfill my request?

Sample.xlsx (1.0 MB)

My solution

Thank you for your advise in advance

[lrtetala (Lakshman Reddy)]

Hi @yangyq10

Use this is Read Cell activity
No need to use Read Range activity

Excel.Sheet(“Sheet1”).Cell(“I”+Excel.Sheet(“Sheet1”). DataTableValue.RowCount.ToString))

Regards,

[Steven_McKeering]

Hi

You are just trying to get the last row value from Column I and Column K - then try this:

Use the System “Read Range” activity (Faster and no reliance on Excel installed or licence) and an Assign activity.

Let ‘DT’ be your your datatable output.

Try this in an Assign activity for Column I:
Left Assign
str_Result1

Right Assign
DT.Rows(yourDT.Rows.Count)(8).ToString

Try this in an Assign activity for Column K:
Left Assign
str_Result2

Right Assign
DT.Rows(yourDT.Rows.Count)(10).ToString

Cheers

Steve

[ashokkarale]

@yangyq10,

Use Find First/Last Data Row activity and remove the Read Range activity as it’s the time taking process here.

Find First/Last Data Row activity will give you last row address which you can use to read the data also from last row using Read Cell activity and pass it to browser for further process.

Thanks,
Ashok

[yangyq10 (IvanY)]

@lrtetala

I try it but it still takes long time to run without reaction

On the other time, if I need to get lastrow of colulmnK next time, what should I do?

Main.xaml (12.1 KB)
Sample.xlsx (1.0 MB)

Thanks

[yangyq10 (IvanY)]

@ashokkarale

Since the headers look like this, so find first/last row doesn’t work

[yangyq10 (IvanY)]

@Steven_McKeering

Somehow I got error as below. Maybe its because there is no data in green color part

[ashokkarale]

@yangyq10,

hmm…ok. If you just have to read the data, it would be best practice to uncheck Save changes.

One workaround would be delete first row using Delete Rows activity then Get the last row with Find First/Last Data Row activity.

This will not impact the original file and will give you last row as well.

Thanks,
Ashok

[yangyq10 (IvanY)]

@ashokkarale I can’t delete it. The first header is requested to upload in another system. Sigh…

[yangyq10 (IvanY)]

@lrtetala

[Steven_McKeering]

Add a filter Range step to keep only column 8 and remove the blank values then try it

Same with column 10.

[lrtetala (Lakshman Reddy)]

@yangyq10

Can you try the below

Output:

Sequence15.xaml (20.6 KB)

Regards,

[yangyq10 (IvanY)]

@lrtetala

Thank you. It works. So the problem is the double header in 1st sheet. We need to use another sheet as transition.