Filtering Huge Data from Excel

Help Studio
, , ,
1

Hi all, currently I’m trying to filter data from an excel file that contains more than 900k rows. Reading the file and filtering it inside studio seems to hand out the machine as the amount of data is massive. Is there any way that I can filter the data before assigning it to a variable?

I did some googling here and there some people suggested to use power query but I’m not sure how that works.

Any suggestion would be helpful, thanks!

1 Like
2

Hey!

If we’re dealing with huge data… we have lot of options to get it done…

Vlookup
AutoFill
LINQ
FilterDataTable

Based on the requirement we can choose the best method…

Regards,
NaNi

1 Like
3

It is may be slow to process large Excel file. You can import Excel data to tools like Power BI Desktop (free for downloading) or SQL database. Then you can use UiPath Studio bot to process the data.

1 Like
4

@Mirai

  • You can try with excel as a database and write the sql queries to manipulate the excel data. Below links for ref
  • You can use LINQ which can filter huge data with simple expressions
1 Like
5

Hi NaNi,

Thanks for the suggestion given, from the list that you had mentioned I believed LINQ and FilterDataTable requires the data to be saved into a variable first I believe. This causes the system resources to be spike to 100% which causes the machine to hang.

As for VLookup I believe it is only usable if we’re only looking on a single column right?
I haven’t tested on AutoFill yet as I’m not sure how to use it currently yet.

Thanks,
Mirai

6

Hi @ushu,

Thanks for the suggestion, I believe this method is achievable by filtering the data using queries first before getting it into a variable yes? If yes, this can be very helpful in reducing the amount of data being pushed into the machine resource.

Thanks,
Mirai

7

Hi @liu_shubin,

I’m trying to see if I can work with Power BI, if it is quite tedious I may use the SQL method.

Thanks!
Mirai

8

@Mirai That’s right. Excel as a Database helps to manipulate the data using SQL queries without using any variable in prior to store the input data. Below docs for ref

1 Like
9

Hi @ushu,

Thanks for the article, just tested now and it works flawlessly.

Mirai

10

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