Migrating Data From Excel to a Database

  • Courses
  • Migrating Data From Excel to a Database

Premium course

Intermediate

Video/Text

Video/Text

Databases

107 Lessons

0%

Step your data up to the next level, by moving it from a spreadsheet to a database! Gain the power, security and power of a full database engine. Learn how to plan the migration, design the database, cleanse the data and then make the move to SQL Azure. Together, we'll build a police and crime database that would serve any CSI team well!

You can't just copy and paste the data from your spreadsheet - however well it works - into a SQL database. It just won't work.

In this course, we teach you the right way, including topics such as

  • Planning your database
  • Normalizing your data
  • Cleansing your data
  • Creating a SQL database in Azure
  • Creating tables
  • Understanding and creating relationships
  • Creating indexes 

Ready to get started?

Migrating Data From Excel to a Database

Introduction to relational databases

4 Lessons

Premium course

Welcome along! Let's take a look at where we're going with this course, including the key steps, and a sneak preview of where we're going to arrive at the end.

Video lesson

Premium course

If you simply want your data to exist in a database, then it's possible to just dump it from one place to another. This is a simple solution, but there are big reasons why it's not a good idea...

Video lesson

Premium course

At the heart of this course is a plan to create a new relational database which will hold your existing data, and then move your data into it. But before we can do that, we need to take a step back and make sure we're completely clear on the fundamental question of what a relational database actually is.

Video lesson

Premium course

If you're planning to move to a relational database, which tool should you use? There are a number of options just from Microsoft, so in this lesson we'll consider each of these.

Video lesson

Planning a relational database

8 Lessons

Premium course

Let's take a look at what we're going to cover in this unit of the course.

Video lesson

Premium course

In this lesson, we'll take a look at how you figure out what will be in your database, including who needs to be involved in the discussions, and the sorts of questions you need to ask.

Video lesson

Premium course

What's your database going to track? Parts in a manufacturing process? Pupils in a school? Staff in your organisation? Answering this question fully is your first step in building your database design

Video lesson

Premium course

You'll need to know certain facts about each of the "things" you identified in the last lesson. In this lesson, we'll start to fill in these details, making sure that your database will be able to hold everything you need.

Video lesson

Premium course

You need to identify everything in your database - in a way which is guaranteed to be accurate. In this lesson, we'll take a look at how you do that. We'll also look at why some ways of identifying this will work, whereas others will cause you endless pain down the road...

Video lesson

Premium course

Now it's your turn to apply what you've learned to the data you've got in your spreadsheets.

Video lesson

Premium course

Obviously, it's not possible for me to look at every scenario that you'll have. But in the review, I'll take a look at a few different examples of data, and we can see what we can come up with as early designs.

Video lesson

Premium course

We've already covered quite a lot of ground. So let's take a look back at what we've done before pressing ahead with the next stage.

Video lesson

Fixing the design

7 Lessons

Premium course

Why is your design flawed? Almost always, it's because there's information about more than one thing mixed up. This leads to BAD DATA - and will cause you endless problems, as you'll see....

Video lesson

Premium course

In this lesson, we'll introduce the concept of normalization, and give you the first rule to apply to your data. We'll look at an example together, and see what the rule means in practice.

Video lesson

Premium course

In the next stage of normalization, we'll look at how to make sure your table doesn't contain horses with wheels, or cars with legs. And we'll look at why this matters (if that's not already obvious...)

Video lesson

Premium course

More about ensuring that each of your database tables has information about one thing - and one thing only...

Video lesson

Premium course

We've looked at normalization together - now it's your turn to apply what you've learned to your own data.

Video lesson

Premium course

Let's take a look together at another example of normalization

Video lesson

Premium course

Let's take a look back at what we've covered in this unit.

Video lesson

Relationships

9 Lessons

Premium course

Let's take a look at why relationships matter, and at what we'll be covering in this unit.

Video lesson

Premium course

One-to-many relationships are far and away the most common type of relationship. In this lesson, we'll look at what we mean by a one-to-many relationship, and some scenarios where you might come across them

Video lesson

Premium course

When we talk about database design, and particularly about relationships, it's often useful to create diagrams of our design. In this bonus lesson, we'll look at three different tools that you can use to create database diagrams, and I'll share my personal recommendation.

Video lesson

Premium course

In this lesson, we'll take a look at the next type of relationship - the one-to-one relationship.

Video lesson

Premium course

Logically, there's only one kind of relationship left - the many-to-many relationship. In this lesson, we'll take a look at this type of relationship - and at why it's bad news!

Video lesson

Premium course

We're going to finish this unit by taking one last look at normalization in the light of what we've learned about relationships.

Video lesson

Premium course

Over to you now to apply what you've learned in this unit, and refine the structure of your database.

Video lesson

Premium course

In this review, we'll apply all that we've learned in this unit to refine the design of our sample database.

Video lesson

Premium course

Let's take a look at what we've learned in this unit

Video lesson

Introduction

5 Lessons

Premium course

In this lesson, we'll take a look at how to create a new database in SQL, in MS Access and in Azure. By covering all three technologies together, you can compare and contrast the task at hand.

Video lesson

Premium course

Once you've got a database, you'll need to start creating tables. This is a pretty simple process in essence, but I'll give you some tips to save you a lot of heartache - no matter where you're building your database.

Video lesson

Premium course

An absolutely crucial decision to get right from day one is the data type for each column in your database. In this lesson we'll look at the main data types available to you, and I'll give you some recommendations for best practice in choosing between similar options.

Video lesson

Premium course

We're at the point where you're going to actually build a database. It's time to choose whether you're going to use MS Access or SQL... at least for this course. Here's why you should use MS Access.

Video lesson

Premium course

We're at the point where you're going to actually build a database. It's time to choose whether you're going to use MS Access or SQL... at least for this course. Here's why you should use SQL Server.

Video lesson

Creating your tables in MS Access

8 Lessons

Premium course

Let's see where we're heading in this unit of the course.

Video lesson

Premium course

In this lesson, we'll put together everything we've learned so far, as we build a couple of tables, including key fields, appropriate data types and apply the tips I shared in the previous unit.

Video lesson

Premium course

Now you've got a couple of tables, you'll want to create a relationship between them. In this lesson, we'll look at how you do that - and what might stop that from working!

Video lesson

Premium course

We mentioned indexes in our introduction to this section of the course - but how do you create an index on an Access table? In this lesson, we'll reveal all!

Video lesson

Premium course

Dealing with NULL values in Access is a key factor in creating a robust design - and one where the Access developers at Microsoft haven't been as helpful as they perhaps might have been.

Video lesson

Premium course

Now it's your turn to apply what you've learned and actually create the tables you need in your database. Once you've created them, you can set some key properties, and then relate them together.

Video lesson

Premium course

Let's tackle this together, as we add the tables we need to our crime database.

Video lesson

Premium course

Let's take a look back over this key unit - where we finally got to build ourselves a database for real!

Video lesson

Creating your tables in SQL

9 Lessons

Premium course

There are two essential ways of creating a table in SQL - you might think of them as "the easy way" and "the hard way". Let's start with the easy way - by using the table design tool

Video lesson

Premium course

In this unit we're going to build a database in SQL. Let's see what's involved...

Video lesson

Premium course

If that last way was "the easy way" then this must be "the hard way", right? Well yes, but there are good reasons to know how to create tables this way, and it isn't really THAT hard...

Video lesson

Premium course

As we go through this course, we're going to link SQL and Access together and draw on the strengths of each. But before we do that, we're going to have to address a BIG problem...

Video lesson

Premium course

As with creating tables in SQL, a relationship can be created either through a Graphical User Interface, or through code. In this lesson, we'll take a look at the GUI method.

Video lesson

Premium course

Now it's time to create a relationship between two tables by using SQL code.

Video lesson

Premium course

We talked in the unit introduction about the need for indexes - now it's time to actually create a few indexes on our SQL tables.

Video lesson

Premium course

You've seen how it's done - now it's time to do it for yourself. In this exercise, you'll create tables in your SQL database.

Video lesson

Premium course

Let's go through the exercise together, by creating tables in our crime database.

Video lesson

Security and SQL Server

8 Lessons

Premium course

One big reason for using SQL Server to manage your data is security. But, if you want security, you'll have to manage that. That's the topic for this unit!

Video lesson

Premium course

There are some key things to know about allowing people to access your database - in this lesson, we'll give you an overview of the issues involved.

Video lesson

Premium course

Although there are more than two ways to do this, in this lesson we're going to look at the two most common ways of providing security to your database - authentication via SQL, or via Windows.

Video lesson

Premium course

Before people can access your database, you'll need to allow them access to the server as a whole.

Video lesson

Premium course

Once you've granted the permission for someone to connect to your server, you'll then want them to access your database - in other words, they will become USERS of your database. Let's see how to do it.

Video lesson

Premium course

It's not easy keeping track of lots of people, who have a habit of arriving, changing jobs, leaving and then coming back. So rather than working with individuals, it's more common to put people into groups. Step forward the idea of a database role....

Video lesson

Premium course

Now we've got people (or roles) who can access our database, it's time to decide what we'll let them do.

Video lesson

Premium course

What have we learned in this part of the course? Let's take a look back...

Video lesson

Linking databases

5 Lessons

Premium course

Having thought long and hard about it, and made your decision to use one tool or the other, why would you now re-think it and use both? Let me explain...

Video lesson

Premium course

If I go to a client, and they've made this mistake, I KNOW I'm dealing with people who don't know what they're doing. It's an easy mistake to fix, and an ESSENTIAL one if you want your database to work reliably.

Video lesson

Premium course

Let's look at how you link one access database to another.

Video lesson

Premium course

Let's see the steps involved in linking a SQL database to an Access database - including a REALLY quick example of why you should!

Video lesson

Premium course

In addition to all the security measures that you'll see in SQL Server in your office, there's one more feature to be aware of if you use SQL Azure - the firewall.

Video lesson

Introduction: How and where to cleanse your data

3 Lessons

Premium course

Excel has some great tools for cleansing your data. But before we start along the journey, we need to be clear about what the end goal is.

Video lesson

Premium course

Do you have customers living at "1 North Road"? How about at "1 North Rd"? Or "1, North Rd.". Which of these is correct? This is what we mean by inconsistent data. Let's look at how we tackle this issue.

Video lesson

Premium course

There's more than one tool that can fix inconsistent data. Let's see how Excel stacks up against Access.

Text lesson

Your number one cleansing tool

9 Lessons

Premium course

At the end of the last unit, we saw that Access can be a fast way to cleanse your data. But it offers a number of benefits over Excel as we look to move our data into a database. Let's see what these benefits may be...

Video lesson

Premium course

Your number one tool for working with data in Access is the query. Let's take a look at the essentials of creating queries in Access

Video lesson

Premium course

Now you know the essentials of creating a query in Access, it's time to take a closer look at some of the tools at your disposal, including selecting the right columns and sorting the data

Video lesson

Premium course

There's a whole heap of ways in which you can filter data using a query. Let's take a look at the basics - filtering based multiple criteria using AND and OR.

Video lesson

Premium course

Given that we know our data may not be perfect, we need to understand how to find North Rd, North Rd. (with a dot) and North Road. Step in the wildcard...

Video lesson

Premium course

This is where things get really interesting. What if you could write one query and have it do multiple jobs? Well, guess what...?

Video lesson

Premium course

You've seen how it's done - now it's your turn. I'll give you a series of questions, and you need to use queries to come up with the answers.

Text lesson

Premium course

How did you do? Let's tackle the questions together and see if we come up with the same answers!

Video lesson

Premium course

Let's take a look back at what we've learned about creating queries in Access

Video lesson

Calculating new values in queries

9 Lessons

Premium course

Whether you want to stick first names to last names, or separate names out, total multiple rows of values or multiply values in two columns, anything is possible if you know how. And after this unit of the course, you will!

Video lesson

Premium course

In this lesson, we'll take a look at how you add a calculated value to each row of data in a query. The days of writing a formula and "filling down" in Excel may finally be over...

Video lesson

Premium course

Just as in Excel, we can concatenate text in our database if we need to. Here's how...

Video lesson

Premium course

Just as in Excel, there will be times when you want to stick two bits of text together to form a new column. Let's look at just how we do it.

Video lesson

Premium course

Just as in Excel, there are lots of functions within Access. In this lesson, we'll take a look at how you can use them to get at the data you need.

Video lesson

Premium course

So far, we've looked at adding new columns to our results, based on various calculations. But we can also calculate across rows, using the aggregate functions that will be familiar to you from Excel - SUM, MAX, MIN and so on. In this lesson, we'll explore how it's done.

Video lesson

Premium course

Time for you to have a go! Put your new skills into practice with a series of challenges.

Text lesson

Premium course

Let's tackle those challenges together, and see if you came up with the same answers - and whether you got there in the same way as I did!

Video lesson

Premium course

You've now got a really useful set of skills under your belt. Let's take a look back and see what we've picked up.

Video lesson

Putting your queries to work

9 Lessons

Premium course

So far, your queries provide lots of ways of seeing your data, including summarising it and calculating it in various ways. Now it's time to make your queries do some real work.

Video lesson

Premium course

Your data is in Excel. You want it in a database. But there's more than one way of achieving that. Let's take a look at the benefits and drawbacks of your two main options.

Video lesson

Premium course

It may be no surprise to hear that the "Make table" query is designed to, well, make tables. Let's see how it works.

Video lesson

Premium course

You'll often need to make changes to your data once it's in the database. Much faster than using find-and-replace, update queries are a powerful and flexible way to do this.

Video lesson

Premium course

Your Excel data is likely to be in a one or more large spreadsheets, and will need to be moved into the various tables you've created. In this lesson, we look at how append queries can do this for you.

Video lesson

Premium course

Once your data is in the right place, you're likely to want to remove it from any "wrong" places. Step in the delete query....

Video lesson

Premium course

It's time to put into practice all the skills you've learned about during this part of the course.

Text lesson

Premium course

Let's take a look at that last challenge and go through the exercise together.

Video lesson

Premium course

We've learned about some very powerful tools in this unit of the course. Before we actually use them to perform the migration of our data, let's just look back and make sure we're comfortable with each of them.

Video lesson

Premium course

There are a number of steps in performing your migration - and you may need to take more than one go at the job. Let's start by being clear about what we need to achieve.

Video lesson

Premium course

You may have one spreadsheet of data or a dozen. You may have 2 tables or 22. So where do you begin with the migration. In this lesson, we'll make a plan.

Video lesson

Premium course

Documenting the import process isn't a "nice to have". You'll often find that you need to re-start the process as you find things that didn't work the first time, and having documentation of all your queries is essential. In this lesson, we look at how to document the queries that you're running in each step of the process.

Video lesson

Premium course

You've made a plan as to where to start. Before you move your data, let's use our new tools to see if we have any data which needs to be cleaned up.

Video lesson

Premium course

We've cleansed the data, we've got the tools at the ready.... Let's FINALLY move the data into its correct table.

Video lesson

Premium course

By linking the data you've put into its final table to the data that's still in its raw form, you can get to the newly created IDs. This is a key step in importing data into subsequent tables. Let's see how to do it.

Video lesson

Premium course

Now you have all that you need to import the data from your original spreadsheets into the remaining tables. Let's see what that process looks like

Video lesson

Premium course

This isn't so much an exercise as you actually doing the job, on your own data, with your own database. Over to you!

Video lesson

Premium course

In this unit you've achieved what you set out to do. You've now got your data into your database. Congratulations! Let's look back at the steps you took to get there.

Video lesson

Premium course

We've achieved what we set out to do, and your data now lives in a database, rather than a set of unmanaged spreadsheets. So what's left for you to do now?

Video lesson

Premium course

A database is the perfect place to hold your data. But that doesn't mean that Excel doesn't have a real use in analysing, charting and working on your data. In this lesson, we'll look at the ways in which you can get your data - or some part of it - back into Excel.

Video lesson

Premium course

One of the benefits of having a database rather than a spreadsheet is that you can build a full user interface. Although that's beyond the scope of this course, in this lesson we'll take a sneak peek at the some of the first steps you'll take to do this.

Video lesson

Premium course

Where are you likely to go next in this process? We'll take a look at the steps needed to build your database up into a fully-featured application which can be used by people throughout your organisation and beyond.

Video lesson

Premium course

Well done! You've made it to the end. Let's take a look back at what you've achieved.

Video lesson

Follow

About the teacher

Andrew Richards

Andrew Richards is the Managing Director of TheIT Service. His background in training goes back to 2000, and he was involved in IT networking and support beyond that. Now he spends his time training cyber-security awareness courses (fun!) and building databases (and training others how to do so too). When not engaged in such important things, he can be found attempting to train his various sheep, chickens and cats.

Comments are closed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Pen