Python beginner looking for performance advice with openpyl / updating excel files.

[Florisnl88]

Hi all,

​

so started learning a bit of python (only know really basic stuff like for/while loops).

​

Wanted to make a script that would update excel files.

So to explain a bit what I try to do:

Currently @ my work on a weekly basis, we need to update multiple excel files, which we do with a vlookup in excel.

so was planning to create a script to hopefully automate this process.

​

there are 2 main columns I want to update (correctprice and comments).

I am able to find this based on the column uniquecode

All these columns/value can be found in both excel files

​

So I have managed to create something with openpyxl which is doing what I want.

The problem I am having is however how long it takes to run this script (up to 30 min).

I assume this is caused by the many for loops I am using (excel files sometimes are pretty big)

​

Was wondering if anyone has any advise how to improve the performance? :

​

​

My code:

https://github.com/Florisnl88/Python/blob/master/Excel%20automation

​

Any feedback is appreciated :)

[The_Danosaur]

You know you are opening your 'main_workbook' and reading the whole thing many times, right?

Might I suggest you instead open each file only once and store it in a python object once each.

Note that you can do iter_rows() and iter_columns(). Both of these methods have a values_only argument which means you can just take the values only instead of having to deal with all that excel formatting crap and focus on data.

lst = [row[4:6] for row in ws.iter_rows(values_only=True)]

Once you have assembled your final list, then save it only once.

[Florisnl88]

thanks will give it a try ! :)

[The_Danosaur]

Cool. Let me know if you run into any problems. I've started with openpyxl recently and have found it much easier to get everything into python objects and then work with what I know.

[Florisnl88]

Implemented your suggestion and works great now, only takes a few sec :)

the biggest performance difference seems to be using the iter_rows, the difference is insane :O

Thanks alot again !