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?
- Download and install SQL Server 2019
- Download and instal Microsoft SQL Management Studios
- Create a local database
- Import a chosen Excel file
- Run SQL code
Why Would You Want To Import Excel Data to SQL?
There are a few reasons.
- You can apply this method both at work and on your personal computer. However, the work version might not be free.
- 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.
- It will take you between under 30min from downloading the required resources to running your first line of SQL code.
- You will be able to use the data you love. Thus, making your learning much more enjoyable.
- 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?
- Excel file you want to use
- SQL Server 2019
- 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.
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.
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.
Connect to your local server. If you have installed SQL Server 2019, it should be as easy as pressing Connect.
You will get a window.
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
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.
These days business problems require not just data crunching but also telling stories to make the right decisions. To put it simply business stakeholders need insights.
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
Subscribe to our newsletter!