Get a row in a large excel file

Help Studio
1

Hi Everyone,

I’m beggining with UiPath studio Cloud and i want to extract a row in a large excel with a key value (invoice number). The read range not works (too much cells).

So I tried an other way (Read range on only one colomn, took the row number, read range on Arow_num:Lrow_num). But it takes a lot of time !! (>4minutes)

How to get directly the entire row from the studio cloud ?

Thank you !! :slight_smile:

Forum Engagement Daily Reports
2

read the data once and then use Filter Table to extract the required row. If the file is very large, limit the range

3

@Theo_l

Can you show your workflow

Are you using excel online activities?

if yes you can try with read column and then read row activities

cheers

4

Hi @theo_l ,

When the Excel file is very large, reading ranges repeatedly becomes slow because Studio Web loads the data each time from the file. Instead of reading the sheet multiple times, try loading the data only once and then filter it in memory.

A faster approach is:

  1. Use Read Range Workbook (or Read Range) once to load the whole sheet into a DataTable.
  2. Use Filter Data Table or Lookup Data Table to find the row using the invoice number (key column).
  3. Once you get the matching row index, you can directly access the full row from the DataTable.

This way the Excel file is accessed only one time, and all searching happens in memory, which is much faster than repeatedly reading Excel ranges.

If the file is extremely large (hundreds of thousands of rows), another option is using Workbook → Read Range with a filter / SQL query so only the required rows are fetched instead of the full sheet.

This usually reduces execution time from minutes to a few seconds.

5

Hi @Theo_l

I think You can use this one to read the sheet (or only required columns) once into a DataTable and then filter it in memory using the invoice number. That’s much faster than reading Excel multiple times.

If the file is extremely large, you can use Excel Find to get the row index and then read that row once, but avoid doing Read Range in a loop.

6

Hello @Anil_G

I’m using excel online but the read Column doesn’t work on my sheet

image
image1353×615 92.6 KB

7

@Theo_l

As per error looks like the data is huge and it is unable to get the value

alternately try to use vlookup activity and get each column value you need using the invoice number value..this way data is looked up and multiple vlookup activities are used to retrieve each column value..but it might be faster

cheers

cheers

8

Try using the Activities - Filter activity to filter out the row that you’re looking for. Should work much faster since its done on the excel side, without requiring the robot to scan through the data of the document.

9

@Hjorth

FYI

Its studio web ..normal excel activities are not supported

The below excel online activities are to be used

Cheers

10

Ah.. We’re using Studio, my apologies.

1 Like
12

If Im dealing with a lrga espredsheet like that I generally use some code to convert the sheet to a csv then do a read csv.

Dim excelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = excelApp.Workbooks
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Open(xlsxPath)

Try
    workbook.SaveAs(csvPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
Finally
    workbook.Close(False)
    excelApp.Quit()

    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
End Try
13

I found the solution, thanks everyone !!

The solution I used :

Flow 1 :
Schedule a flow every morning that :

  • Download my sharepoint file
  • Made a read range WORKBOOK
  • Data table to string
  • Text to storage bucket

Flow 2 : calling

  • Read text from storage bucket
  • VB formula to find the corresponding row
    (<less than 3sec)

Thank you all

1 Like
14

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