Hello guys i have an excel 18000 row.
When i try to read and write into a single cell it is taking tomuch time.
Is there any way to reduce the time to read and write into the cell.
Hi there,
is it taking too much time writing into individual cells?
If that is the case, you can share some of your code and let us help optimize it.
or is it taking too much time to read the file into the datatable?
If this then, you might need to convert the file into csv before reading it or read the file in smaller chunks.
Let us know specifically which part of your solution is taking too much time.
I want to read the entire sheet on that excel sheet , i want to write cell under some condition.
It is taking toomuch time for both read and write.
Are you able to share a sample file and also describe what the conditions are, and the desired output?
You can replace sensitive information with dummy values
Bro the excel file containing confidential data around 18000 if i want replace that with dummy data it will take toomuch time
bro ill try that update you the status
Well, I am asking you to give us an idea of what you are working with
you need to give us a sample file even with 10 rows it doesn’t matter, otherwise, it’s difficult for anyone to help. We need to see the data/file structure.
Hi @Gokul_Murali - After looking at the screenshot i can make out that you are updating the content using write cell in for each row.
To make it optimized you can update the cell in your dt variable and use write range after the for each row activity.
Hope this will make things faster.
Sample sheet.xlsx (12.9 KB)
Bro this is the sample sheet it is having 18000 rows like this sometimes it will contains like 45000 rows
1 Like
try using excel application scope as you are doing in loop
better way would be to use excel as db, this would be much faster
cheers
-
to use excel as a db might be the best solution but it does require some sql skills and also you might need admin settings to the machine to do the configuration required for it to run.
-
Converting the input file to csv before reading will definitely improve the bot performance on reading the input.
-
Another solution to improving both the reading and writing performance would be writing VBScript and have the bot execute the script on the file.
The screenshots of the code you shared earlier indicates to me that, based on the information under the Product Code column you write information to the BO column of the MOT sheet.
Just explain this coding to me.
I am even thinking since this is the same file you are reading and writing to, you might as well just have the bot write the formula on the first cell in BO column and autofill to the last row, that would take seconds to complete, literally.
bro i have used write csv to convert data from excel to csv.
but the thing is that the excel is having equations, formulas,
so is it possible to use the csv file for this.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.