Import Excel Data to SQL

How To Import Excel Data to Local SQL Database?

How To Import Excel Data to Local SQL Database?

How to easily load Excel and master the magic of SQL in 5 steps?

I wish I knew this trick when I was learning SQL a few years back. The method will show you how to import excel data to SQL database in 5 steps. The method is manual but perfect for learning and improving SQL skills. You will be able to run SQL code, get insights and use the data you choose. You may be able to apply the same method at work as well.

So, what are those steps?

  1. Download and install SQL Server 2019
  2. Download and instal  Microsoft SQL Management Studios
  3. Create a local database
  4. Import a chosen Excel file
  5. Run SQL code

Why Would You Want To Import Excel Data to SQL? 

There are a few reasons. 

  1. You can apply this method both at work and on your personal computer. However, the work version might not be free.
  2. SQL is great to run the calculation on large sets of data. Thus, if you have a large file and need to do some grouping, simple calculation or logic, you can do it.
  3. It will take you between under 30min from downloading the required resources to running your first line of SQL code.
  4. You will be able to use the data you love. Thus, making your learning much more enjoyable.
  5. You will be able to do a lot of filtering by specific words using the function “IN.”

SQL, Excel, Python and Power BI are tools that allow you to achieve your analysis goals. But, using tools without purpose is quite dry and can get boring very quickly. Learn more about data analyst tools in my previous blog post.

So, shall we learn how to make SQL learning more engaging?

5 Step Process To Follow To Import Excel Data to Local SQL Database

What will you need?

  1. Excel file you want to use
  2. SQL Server 2019
  3. Microsoft SQL Management Studios

Step 1: Download and install SQL Server 2019

First, you need to download and install SQL Server 2019. The application is free for personal use and learning. Download the file and follow through with the installation process. I would recommend using default folders: SQL Server 2019 Link.

Or, download a free specialised edition 
Developer 
SQL Server 2019 Developer is a full-featured free edition, licensed for use as a development and 
test database in a non-production environment. 
Download now 
Exp ress 
SQL Server 2019 Express is a free edition of SQL Server, ideal for development and production 
for desktop, web, and small server applications. 
Download now

Step 2: Download and Instal  Microsoft SQL Management Studios

Second, after you have completed an installation for SQL Server 2019. Download  Microsoft SQL Management Studios. Complete installation.

Download SSMS 
@Download SQL Server Management Studio (SSMS) 
SSMS 18.5.1 is the latest general availability (GA) version of SSMS. If). 
SSMS 18.5.1 upgrades it to 18.5.1.

Step 3: Create a Local Database

We are ready to load our Excel file after systems are installed. Open Microsoft SQL Management Studios on your computer.

All Apps 
Best match 
Documents 
Web 
More 
Microsoft SQL Server 
Management Studio 18 
App

Connect to your local server. If you have installed SQL Server 2019, it should be as easy as pressing Connect.

Connect to Server 
type: 
Server name: 
User name: 
Password: 
x 
SQL Server 
Database 
Windows Athentication 
LENOVO-PPPcuauli 
Remember password 
Connect

You will get a window.

Microsfot SQL Sever Management Studio

Time to create a database. With the right mouse button, press on the database folder. Next, you need to select New Database.

In the next window, give a name to the database. You do not need to change any other parameters. I will name mine AirBnB_Example.

Step 4: Import a Chosen Excel File

Your fresh new database is ready. Now we need to load your excel file. I will use AB_NYC_2019.csv sample file. The data is Airbnb sample for New York City rent prices. Select with the right button on your newly created database, go to Tasks and Import Flat File.

In a new window, you will see:

After pressing next, the installation wizard will ask you to select the file you want to import. Plus, you will be able to create a name for your table. I keep the name AB_NYC_2019 as a file name.

Next window will give you a preview of your data, which look a lot like in excel file.

After a preview, you will be asked to specify Data Types, Primary Keys and if you allow Null values in your data. The system made some suggestions for us. Yet, I will make some changes, for instance, I know some of my data has more characters. Thus, I will increase the number of characters allowed in those fields.

Finally, you get to the Results window. If you get an error message, then click on it and have a look. You will probably need to change some columns to accept Nulls or change Data Types to allow more characters. If it says Success you are done!

Step 5: SQL Sample Code

Let’s run the SQL code to test our brand new table. In the code, I am creating a group out of neighbourhood_group and room_type and look into the average price.

SELECT 
	neighbourhood_group
	,room_type
	,AVG(price) AS "Avg Price"
FROM AB_NYC_2019
GROUP BY
	neighbourhood_group
	,room_type
ORDER BY neighbourhood_group, room_type

Well done! 

Now, you can experiment with your files using SQL, Excel. There are a lot of resources and sample files in https://www.kaggle.com/. I got my example there as well.

Let me know where you are going to use this new trick. What sort of fresh insights have you gained? Do you have some cool tips for us as well? Leave in the comments below.


Paulius Petravicius PhotoAbout Me

I am an experienced ex. Business & Data Analyst and now a Project Manager with multiple years of experience gained in several international companies.

These days, business problems require data crunching and telling stories to make the right decisions. To put it simply business stakeholders need insights into their projects and deliveries.

This is where I come in. I have learned and applied PythonPower BISQL and Excel to analyse and present data. Also, I gained experience in Project Management and Business Analysis. So, I can not only spot insights but execute business decisions. Moreover, I can teach you as well. Read More



Subscribe to our newsletter!