How to Open Excel Files in a Directory using Python 3?

Working with multiple Spreadsheet files in xlsx format in a directory can be time-consuming, especially if you need to perform the same operation. Python can make your life easier by providing automation tools for repetitive tasks. In this post, we’ll guide you on how to open Excel files in a directory using Python 3.

Step 1: Install Required packages
To work with Excel files, we need to install the openpyxl package, a Python library to read and write Excel files. Open your terminal and type the following command:

pip install openpyxl

Step 2: Import Required Libraries
Open your IDE and start a new Python file. Import the required libraries by adding the following lines of code:

import os 
import openpyxl

We use the os module to interact with the operating system and the openpyxl module to work with Excel files.

Step 3: Define Directory Path
Now we need to define the directory’s path containing the Excel files. Add the following code to your Python file:

Python

dir_path = '/path/to/your/excel/files'

Make sure to replace ‘/path/to/your/excel/files’ with the actual path of your directory.

Step 4: Loop Through the Excel Files

We can use the os module to loop through all the files in the directory and identify the Excel files. Add the following code to your Python file:

for filename in os. listdir(dir_path):     
       if filename. endswith(' xIsx'):         
                file_path = os.path. join (dir_path, filename)         
                workbook = openpyx1. load_-workbook(file_path)        
                # Do something with the workbook

The above code iterates through all the files in the directory and identifies the Excel files by checking if the file extension is ‘.xlsx’. We then use the os.path.join() method to create the full file path and load the workbook using openpyxl.load_workbook() method. You can replace the comment with any operation you want to perform on the workbook.

Step 5: Run the Script
Save your Python file and run it by typing the following command in your terminal:

python your_file_name.py

Make sure to replace ‘your_file_name.py’ with the actual name of your Python file.

In Conclusion:

This post shows you how to open Excel files in a directory using Python 3. With the os module and the openpyxl library, it is easy to loop through all the files in a directory and identify the Excel files. You can then perform any operation that you want on each workbook.