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.

AB_NYC_2019Download

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.

Subscribe to our newsletter!

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. Simply put, business stakeholders need insights into their projects and deliveries.

This is where I come in. I have learned and applied Python, Power BI, SQL 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

Best Books


Latest Blog Posts

Need Project Manager’s Help!?

Check out the Fiverr marketplace if you do not have time to run your own projects or just need extra help. They do have multiple project professionals, including project managers. Maybe you will find just the right fit to take some burden from you. I have used Fiverr in the past. The prices are also not too bad. If you seek PM via the corporate route, it will be easily 5x the price.