Using Python with Excel¶
In this submodule, you will learn about using Python to do Excel automation work that we’d usually do with VBA. We will explore a few examples motivated by common scenarios such as:
You have a whole folder full of csv (or Excel) files with the same file structure and you need to combine them into a single file. You might also need to make some changes to the consolidated file.
You have an Excel file with multiple sheets of similarly structured data and you want to consolidate them into a single sheet.
You have an Excel file with data in “wide” format and you need to convert it to long format, and then perhaps export out individual files (one per the key column(s) in the long formatted data).
You have an Excel file acting as a simple flatfile database. Periodically, you get new Excel files that need to get appended to the “database” file.
We’ll also learn about the newish pathlib Python library for working with file paths - very nice. There are several different tools for working with Excel from Python and we’ll explore a few of them. Near the end of the notebook you’ll find a list of very good blog posts from Practical Business Python that are directly relevant to this topic - highly recommended.
Downloads¶
This downloads file will be used throughout all of the activities in this submodule.
Activities¶
You’ll be working the using_python_with_excel.ipynb notebook. Just work your way through it, exploring the code and trying out the challenge task (answer at end of notebook).
