Running SQL queries on local excel files
While working I used to wonder if there is a way we can create databases out of flat files or local excel files and perform query operations on them. After a bit of exploration I figured out a way we can do so and thought of documenting and sharing for anyone looking for the same.
The article is structured in the following way where I will go in detail about:
- The data we will be using
- The tool we will be using
- The process of importing the data into databases
- Starting up the query console
- Querying the database and the tables
About the data
Here I am using the amazon reviews and sales data which I was able to create from the following places:
1. Reviews data: Amazon and Best Buy Electronics — dataset by datafiniti | data.world
2. Sales data: Mockaroo — Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel
From these resources I was able to create 3 excel flat files which I will import into SSMS and query them.
A brief description of the files:
1. ProductIdTable: Has ASIN level data for 50 products.
2. ProductReviews: Has review data for ASINs and when each product review was viewed.
3. ProductSales: Has sales data for the ASINs.
About the tool
I am using SQL Server Management Studio (SSMS)and SQL Server 2019 configuration on windows for this exercise. For the set up procedure you can follow along this video, it shows you the set up process step by step: How To Install And Configure Sql Server 2019 in Windows 10 —(Please like the video and subscribe to the channel)
About importing excel files as tables into a database
After we have our SSMS up and running we can go ahead with uploading the files, please follow along for the process:
- Log into SSMS and connect to your local SQL server.
2. In the object explorer, right click on “Databases” and select “New Database…”, the New Database dialog box will open up, then name the database and click “OK”.
3. You will notice that the database would have been created in the object explorer. Now we will add our tables to the database. To do that we would need to right click on the database and go to Tasks>Import Data, which would open the SQL Server Import and Export Wizard.
In the Data Source drop down you must select Microsoft Excel which will let you browse and select the excel file you want to import. Click on Next.
Next you would need to choose the destination source which should set to “SQL Server Native Client 11.0”. Click on Next.
After importing the three datasets we can see them featuring as tables under the AmazonPraticeDB we created.
About accessing the query console.
After all of the above steps we can finally start querying from the database and the tables. Click on “New Query” which would open up the query tab and you can start from here.
Let do some sample querying!
I’ll start by pulling the first few rows of the products table.
Let’s see category wise count of ASINs available in the table.
Let’s join products table with the sales table and check out brand wise sales.
Closing words and resources.
Hope this was helpful for anyone trying to learn querying on their own over data they are comfortable working with because I know working on online IDEs sometimes feel like just editing pre-written queries leaving little space for experimentation.
I will link some really awesome resources below where you can learn SQL and practice querying online as well.
SQL Tutorial (w3schools.com)
SQLZOO
SQL Data Exploration — YouTube (This was the source where I learned about the importing process — Please like the video and subscribe to the channel, it is a very good place and has a lot of good content)
Stay tuned for the next part where I will go in depth and break down some complex queries and hopefully learn something interesting from data.
