Most Excel spreadsheets need you to manually insert data into cells before analyzing it or performing calculations using formulae or other functions. You may use Excel to get data from a big data source, such as an Access database, an SQL Server database, or even a huge text file. SQL statements in Excel allow you to connect to an external data source, parse fields or table contents, and import data without having to manually enter the data.
After importing external data using SQL commands, you may sort, analyze, and conduct any necessary computations. Here, we will be discussing how to execute SQL statements in MS Excel. For this, an open-source package called ‘xlwings’ is required. So, before we begin with the process of running SQL queries in MS Excel, we will have to install xlwings. For running SQL queries in MS Excel using xlwings, having Windows OS and Python is a must.
Install Xlwings
Make sure you have installed pip for Python beforehand. If not, refer to this GeeksforGeeks link. Once you have installed pip, open your Command Prompt type pip install xlwings, and hit Enter. Once this command is executed completely, type xlwings add-in install and hit Enter. Now, open Excel, and you’ll find xlwings section added.
SQL Queries in Excel
Step 1: Creation of Tables in Excel.
For the execution of SQL queries in Excel, in this article, two tables have been created in Excel (same workbook) and will be used for demonstration of the same. The two tables are – Employee Table and Department Table, as depicted below:
Table 1: Employee Table.
Employee Table
Table 2: Department Table.
Department Table
Step 2: Write the SQL query in Excel.
Type in the SQL query to be executed in Excel. (You may first Merge and center the cells and then type in the SQL query).
Note: When only one table is being referred to, use ‘a’/’A’ for referring to it. If there are two tables, for example, when Joins are used, use ‘a’/’A’ for the first table and use ‘b’/’B’ for referring to the second table.
SQL Query
Step 3: Running the SQL query in Excel.
For executing the SQL query, type in =sql( in a new cell, where you need the retrieved data to be displayed. Then, click on the Insert Function option, displayed to the left of the Formula Bar.
Executing Query
On clicking the Insert Function option, a dialog box appears, which requires 2 inputs – Query and Tables. For the Query input, select the SQL query cell (above step) or simply manually type in the query to be executed.
For the Tables input, hold and drag the entire table to be used for the SQL query. If there is more than one table, add the table(s) in a similar fashion in the Tables input. After this, click on the Ok button, and presto, the data is retrieved!
Output: Now you can see the output of the SQL Query.
output
More Sample SQL Queries in Excel
- SELECT STATEMENT SQL: The SELECT statement is used to get information from a database. The information returned is saved in a result table known as the result set.
Select statement syntax: SELECT Age FROM a
Output
SELECT Name, Gender FROM a
Output
- WHERE CLAUSE SQL: To filter records, use the WHERE clause. It is used to extract only records that meet a predefined requirement.
Where clause syntax: SELECT * FROM a WHERE Gender = ‘Female’
Output
- OR OPERATOR: The OR operators are used to filter records based on several criteria. If any of the conditions separated by OR is TRUE, the OR operator shows a record.
Or operator syntax: SELECT * FROM a WHERE Gender = ‘MALE’ OR Age < 40
Output
- NOT OPERATOR: If the condition(s) is NOT TRUE, the NOT operator shows a record.
Not operator syntax: SELECT * FROM a WHERE NOT Gender = ‘Female’
Output
- MIN() FUNCTION: The MIN() method returns the column with the lowest value.
Min function syntax: SELECT MIN(Age) FROM a
Output
- AVERAGE FUNCTION SQL: AVG() returns the average value of a numerical column.
Avg function syntax: SELECT AVG(Age) FROM a
Output
- GROUP BY STATEMENT: The SQL GROUP BY clause is used in conjunction with the SELECT statement to group identical data. In a SELECT statement, the GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.
Group By statement syntax: SELECT AVG(Salary) AS Avg_Sal, Gender FROM a GROUP BY Gender
Output
- SQL INNER JOIN: The INNER JOIN keyword selects records from both tables that have matching values.
Inner join syntax: SELECT a.Name,a.Dept,b.D_Name,b.D_City FROM an INNER JOIN b ON a.Dept=b.D_Name
Output