How to run SQL queries in Microsoft Excel

Microsoft Excel It is one of the most widely used tools in the business and data analysis field. However, what many people don't know is that it is possible to run SQL queries directly on Excel spreadsheets, making it easy to manipulation and filtering of large volumes of data efficiently.

In this article, we will explore how we can Perform SQL queries on Excel files, open a SQL connection, retrieve specific information and run some of the most useful queries for filter and modify data without having to manually scroll through hundreds of rows.

How to open a SQL connection with an Excel file

Before you start running SQL queries on an Excel file, it is essential to establish a connection to the document. To do this, you must configure a proper connection string, which allows the query to interact with the information contained in the file.

To achieve this, you can use the following: connection string in your software or automation process:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\file.xlsx;Extended Properties=»Excel 12.0 Xml;HDR=YES»;

Where Data source should aim at the exact location of the Excel file on your device.

Run SQL queries in a password protected Excel file

If Excel file is password protected, you need to remove protection before running SQL queries. To do this, follow these steps:

• Open the file manually or using automation software.
• Please enter the password correctly.
• Go to File > Information > Protect book > Encrypt with password.
• Remove the password by entering a new one empty string in the corresponding field.
• Save the file without protection and use the SQL connection as explained in the previous section.

Reading the contents of a spreadsheet with SQL

To get all the data in an Excel sheet you can use the following SQL query:

SELECT * FROM [Hoja1$]

If you want to get only rows that meet a specific criteria, you can use a query with the clause WHERE:

SELECT * FROM [Hoja1$] WHERE [NombreColumna] = 'Ejemplo'

Delete data from a row in Excel using SQL

Unlike a traditional database, Excel does not allow the use of DELETE in SQL queries. However, it is possible Modify a row by setting cell values ​​to null:

UPDATE [Hoja1$] SET [Columna1]=NULL, [Columna2]=NULL WHERE [Columna1]='Ejemplo'

Filter information by excluding a particular row

If you need to get all the records from an Excel sheet except those that have a specific value in a column, you can run the following SQL query:

SELECT * FROM [Hoja1$] WHERE [Columna1] IS NOT NULL OR [Columna2] IS NOT NULL

Running SQL queries from Excel with linked servers

Another advanced option to run SQL queries in Excel is through linked serversIt is possible to connect Excel with SQL Server and perform queries directly from the database with the following steps:

• Set up a OLE DB data source in SQL Server.
• Add a server linked to the sp_addlinkedserver stored procedure.
• Execute queries directly from SQL Server using Excel as a data source.

Example of a SQL query on a linked server:

SELECT * FROM OPENQUERY(ExcelServ,''SELECT * FROM [Hoja1$]'')

Take advantage of Versatility of SQL within Excel allows you to manipulate data more efficiently, reducing the Time required to perform complex filtering and analysis.