I have a excel sheet with millions of rows. Create a Pivot table from that sheet

Help Studio
, ,
1

I have excel sheet with millions of records. I want to create a Pivot table out of it. How to do that? How will it read millions of records and create pivot table

2

Hi @khooshbu.jani,

My suggestion would be to use the python script because the size of Excel is very large.

Then you can run script with UiPath.

https://docs.uipath.com/activities/docs/invoking-a-python-script

Regards,
MY

3

is it compulsory to use >=2.7 and <=3.6 version of python and 32 bit?
Also what do you use to build a script? what is that software?

4

Hi.
No need to read any row!.
In a new Excel workbook only, insert/create the pivot table using an “external data source” feature pointing to the large Excel workbook.
This large workbook can be updated at any time.
Don’t forget to mark the option in the pivot table “Load data when the workbook is opened”

5

it is asking me username and password. We don’t have username and password, we use PIN to login.
Use windows NT integrated security option is disable

6

I do like the python solution shared above, it is actually very good. But if you want to try something else or you can’t have a python interpreter running, there are a couple of alternatives:

#1 If the file is properly formatted you can also use it as an ODBC source and query it as you would do a database and create aggregate functions

Please note it will require that the filename doesn’t change its location/name

VBA-Excel: Make Excel File as ODBC Source(Database) using Microsoft Excel Driver (tutorialhorizon.com)

I really don’t recommend working with excel at high volumes since it is likely to be unstable, therefore the second option:

#2 usually those huge excel files come from another source, many times a database, being the excel file generated for the sole purpose of the end user visibility. If going back to requirements is an option you can get together with your business analyst/architect to attempt retrieving the data directly from the source via SQL query or an API

1 Like
7

the file get download with filename+today’s date. So it will always change filename. Since client don’t want to use db and want to use downloaded excel file to create pivot table, we can’t use db. Also the records are in millions, while downloading, it downloads in txt format (may be four or five text files) and then we convert the text file to excel files. from these excel files, we want pivot table.