Automate with Python: Files, Excel and APIs
Automate with Python (Files, Excel, APIs) is an intermediate-level course of 5 lessons (about 3 h 5 min), with 15 exercises and 15 quiz questions. Prerequisites: Intermediate Python (Functions, Modules, OOP). Exercises run in the browser, with nothing to install.
- Python
- Intermediate
- 5 lessons
- 3 h 5 min
- 15 exercises
- Exercises run in the browser
The course in brief
This course teaches you to hand an analyst's repetitive daily tasks over to Python. You work with files and folders, convert CSV and JSON, produce formatted Excel workbooks, query web APIs robustly, then assemble a configurable, logged, schedulable script. All exercises simulate files and APIs in memory, with no network.
What you will be able to do
- Work with files and folders portably using pathlib
- Read and write CSV and JSON files, including French formats
- Generate formatted multi-sheet Excel workbooks with pandas and openpyxl
- Query a web API while handling errors, timeouts, pagination and rate limits
- Write testable code without a network using dependency injection
- Build a configurable, logged, idempotent and schedulable script
Syllabus: 5 lessons
- Build portable paths with pathlib
- Read and write text files, specifying the encoding
- Browse, create, rename and organize the files in a folder
- 02
Reading and Writing CSV and JSON
- Read and write CSV files with the csv module (DictReader, DictWriter)
- Serialize and deserialize JSON data
- Choose between the csv module, json and pandas depending on the need
- 03
Automating Excel with pandas and openpyxl
- Read one or more Excel sheets with pandas
- Produce a multi-sheet workbook with ExcelWriter
- Format an Excel report with openpyxl (headers, widths, formats, formulas)
Reading and writing Excelopenpyxl formattingAutomated Excel report
- 04
Querying a Web API
- Understand an HTTP request: URL, method, parameters, headers, status code
- Query a JSON API with requests while handling errors, timeouts and pagination
- Structure the code so it can be tested without a network using a fake function
HTTP requestsThe requests libraryPaginationTesting with test doubles
- 05
An End-to-End Automated Script
- Organize an automation script into functions with an entry point
- Configure the script with argparse and trace its execution with logging
- Make the processing robust and schedule its execution
Final project
Automated Sales Reporting
Build a script that produces a small business's sales report every morning. It retrieves the previous day's orders from an online store API (simulated by a paginated fake function, with a rate limit), reads the stores' sales CSV files dropped in a folder, consolidates everything, then generates a formatted Excel workbook (summary by channel and by store, detail, total row) and archives the processed files. The script is configurable on the command line, logged, idempotent and comes with the cron line or the Task Scheduler configuration.
Deliverables
- A Python module with separate functions (API reading, file reading, consolidation, Excel export, archiving) and a main(argv=None) function
- A paginated fake API function and tests (assert) covering pagination, retry on 429 and invalid files
- The generated Excel workbook with at least two formatted sheets and a total formula
- A sample log file and the documented scheduling command
Assessment criteria
- The script runs end to end and can be rerun without creating duplicates
- Errors (invalid file, unavailable API) are isolated, logged and reflected in the exit code
- API calls use timeout, status checking, pagination and retry
- The Excel workbook is readable: headers, currency formats, widths, frozen panes
- The code is testable without a network and secrets are never hard-coded
Frequently asked questions
What can you automate with Python in the office?
Sorting and renaming files, consolidating CSV or Excel exports, producing formatted Excel reports, retrieving data from APIs (CRM, online store, marketing tools) and running these jobs at a set time.
Do you need Excel installed to generate Excel files with Python?
No. pandas and openpyxl read and write .xlsx files directly. Excel (or another spreadsheet) is only needed to open the result and calculate the formulas written into it.
Do the exercises call real APIs?
No. The exercises simulate APIs with fake functions and files in temporary folders or in memory. This way you learn the dependency injection technique, which also makes your code professionally testable.
What level do I need for this course?
You need to be comfortable with functions, modules and exceptions in Python, as in the Intermediate Python course (PY2). Some pandas knowledge is useful for the Excel section.
This course, told for your job
Say what you want to be able to do: “Automate with Python (Files, Excel, APIs)” takes its place in your path, with examples from your field. The concepts stay those of the course.