Python & Excel: Easily migrate spreadsheets to a database
3.8 (93 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,528 students enrolled

Python & Excel: Easily migrate spreadsheets to a database

Automate the reading / writing of Excel document and/or migrate them to a database
3.8 (93 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,528 students enrolled
Created by Peter Hijma
Last updated 5/2018
English
Current price: $20.99 Original price: $34.99 Discount: 40% off
1 day left at this price!
30-Day Money-Back Guarantee
This course includes
  • 1 hour on-demand video
  • 4 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Reading data from Excel documents
  • Writing data to Excel documents
  • Migrate data from Excel documents to a database
  • Create objects that represent tables in a database
Course content
Expand all 18 lectures 01:06:00
+ Introduction
1 lecture 01:05

Learn where to download Python and Pycharm.

Preview 01:05
+ Learning the basics of openpyxl
7 lectures 20:21

Learn how to install openpyxl with the use of PyCharm

Preview 01:21

Learn to create a virtual environment with PyCharm.

Preview 01:26

Learn how to load a Excel document with openpyxl in Python.

Preview 02:21

Learn how to read single cell, single row, and single column values with openpyxl.

Preview 04:11

Learn how to iterate through multiple or all rows/columns in a Excel document with openpyxl.

Iterating through rows
02:34

Learn how to write to (existing) Excel documents with openpyxl.

Writing to Excel sheets
05:48

Learn how to read cells where the value is a formula.

Formula's in openpyxl
02:40

Just a short test to see if you have a good basic understanding.

Basics of openpyxl
3 questions
+ Migrating multiple excel sheets to a database
3 lectures 17:56

Read the data from multiple Excel documents with openpyxl and the os module.

Reading from multiple excel documents
07:33

Learn to use Peewee to create tables in a database and use DBeaver to show contents of a database.

Peewee and DBeaver
06:23

Store the data from multiple Excel documents in a database.

Store excel data in a database (sqlite)
04:00
+ Beyond the very basics
7 lectures 26:38
Iterating through rows again, with a look into the source code
04:45
Dealing with missing rows / cells
05:16
Creating the database models (with foreign key)
04:30
Storing the books with foreign keys to categories
05:31
Query using the foreign key
02:50
Raising an exception to close the connection
02:10
Requirements
  • You should know the basics of Python
  • You should know what a Excel document is
  • You should know the concept of a database
Description

Welcome! Learn to use Python for migrating your excel files in almost any format to a relational database.

  • The first section of this course is a basic introduction to
    openpyxl: the ideas behind reading and writing data from and to Excel.
  • In the second section we will see how to read from multiple Excel
    documents and save them to a database. The database could be any relational database, but for this section we will use SQLite, as it's fast to setup and easy to use.
  • The third section is a more advanced case, where we will learn how to deal with a more 'messy' Excel document and with a more complex database structure using a foreign key.
  • For the next sections, I like to hear from you what you would like! :-) At the moment I'm first thinking of some other kind of databases, like MySQL and PostgreSQL. But let me know
Who this course is for:
  • Those who want to parse or create Excel documents with Python
  • Those who need to migrate their Excel documents to a database