Handling very large files with openpyxl in Python
Sometimes, we can have huge Excel or CSV files with many rows and columns. In this case, loading such files directly in Python can cause the interpreter to crash. Thus, we can make use of the openpyxl library in Python to handle these massive amounts of files. Therefore, in this tutorial, we will learn to handle large files with openpyxl in Python.
Loading large files using the read-only mode
Openpyxl is a library used to read and write excel files in Python. The common functions available in openpyxl won’t be able to handle reading and writing extremely large files. But, in openpyxl there are two modes available through which we can read or write such large files in nearly constant memory consumption.
In this case, our dataset contains 1192 rows and is of size 1MB. Using normal functions in openpyxl won’t be enough to load this huge amount of dataset. So we open the workbook in
read_only mode and print every cell of every row in the current worksheet.
from openpyxl import load_workbook wb = load_workbook(filename='dataset.xlsx', read_only=True) ws=wb.active for row in ws.rows: for cell in row: print(cell.value) wb.close()
https://www.udemy.com/trading-forex-lanalyse-des-volumes/ 70 4 3 13 All Levels 0.97 2 2017-06-29T21:32:21Z Business Finance 1239206 Win 90% of Trades; Quantitative Investments Models in Excel https://www.udemy.com/quantitative-investments/ 200 4 1 16 All Levels 0.97 1.5 2017-06-28T15:09:21Z Business Finance 625670 Technical Charting https://www.udemy.com/technical-charting/ 20 4 1 6 All Levels 0.97 1 2015-11-02T20:23:54Z Business Finance 1153854 Working Capital assessment for bankers, credit analysts https://www.udemy.com/working-capital-assessment-for-bankers-credit-analysts/ 20 4 0 29
Slicing and extending data from huge Excel files using openpyxl
We will import the
range_boundaries function which generates a tuple of cell boundaries from a given range mentioned in the parameters: (
min_col, min_row, max_col, max_row). Next, we use the iter_rows () function to read and iterate through every cell corresponding to the rows as per the coordinates mentioned in the parameters. We will then slice the number of columns only up to column E by indexing each
row_cell. We can also apply the extend function to extend the data generated above. We will add the value from column B till the end, to every row. Lastly, we will print the output data.
from openpyxl.utils import range_boundaries from openpyxl import load_workbook wb = load_workbook(filename='dataset.xlsx') ws=wb.active min_col, min_row, max_col, max_row = range_boundaries('A1:E3') for row_cells in ws.iter_rows(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row): # Slice Column Values up to E data = [cell.value for cell in row_cells[:5]] # Extend data with sliced Column Values from B up to End data.extend([cell.value for cell in row_cells[2:]]) print(data)
['course_id', 'course_title', 'url', 'price', 'num_subscribers', 'url', 'price', 'num_subscribers'] [49798, 'Bitcoin or How I Learned to Stop Worrying and Love Crypto', 'https://www.udemy.com/bitcoin-or-how-i-learned-to-stop-worrying-and-love-crypto/', 0, 65576, 'https://www.udemy.com/bitcoin-or-how-i-learned-to-stop-worrying-and-love-crypto/', 0, 65576] [48841, 'Accounting in 60 Minutes - A Brief Introduction', 'https://www.udemy.com/accounting-in-60-minutes-a-brief-introduction/', 0, 56659, 'https://www.udemy.com/accounting-in-60-minutes-a-brief-introduction/', 0, 56659]
Thus we have reached the end of this tutorial on how to handle large files in Python using openpyxl. To learn more about openpyxl, click on the following link: How to append data in excel using openpyxl in Python