How to Open a SQL Database in Excel

by

To utilize SQL in Excel, follow the steps below:

  1. Open Excel and select New Workbook from the menu, or open an existing workbook to establish a connection between Excel and a SQL Database.

  2. Choose the DATA tab, then select GET DATA, followed by From Azure, and finally FROM AZURE SQL DATABASE from the menu bar at the top of the page.

  3. Enter the SERVER NAME you wish to connect to in the format <servername>.database.windows.net, for example, msftestserver.database.windows.net. You can also provide the name of your database if desired. To view the credentials window, select OK.

  4. After selecting DATABASE on the left side of the SQL SERVER DATABASE dialog box, enter your USER NAME and PASSWORD for the server you wish to connect to. To access the NAVIGATOR, select CONNECT.

  5. In the Navigator, choose your desired database from the list, along with the tables or views you wish to work with (e.g., VGETALLCATEGORIES). Then, click LOAD to transfer the data from your database to your Excel spreadsheet.

  6. Now that the connection has been made, you have multiple choices for loading the data. For instance, you can make a pivot chart using the information in your SQL Database.

  7. To proceed, follow the steps in the preceding section, but this time, choose LOAD TO from the LOAD drop-down menu instead of LOAD.

  8. Next, decide how you want your workbook to display this data. You can choose options like PIVOTCHART, ADD THIS DATA TO A DATA MODEL, or build a NEW WORKSHEET.

  9. If you want to save the connection details forever, you can make this connection a selectable option in the EXISTING CONNECTIONS dialog box and create a .odc file.

  10. To open the EXISTING CONNECTIONS dialog box, select the DATA tab from the menu bar, then click EXISTING CONNECTIONS.

  11. To access the SELECT DATA SOURCE dialog box, select BROWSE FOR MORE.

Running SQL Queries in Excel

To run SQL queries in Excel using xlwings, follow these steps:

  1. Ensure you have Python and Windows OS installed.

  2. Two Excel tables (from the same workbook) have been created for the purpose of demonstrating how to execute SQL queries in Excel.

  3. Type in the SQL query that Excel wants to run.

  4. In a new cell where you want the retrieved data to be displayed, enter =SQL( to start the SQL query.

  5. Click on the INSERT FUNCTION option, and a dialog box requesting the inputs TABLES and QUERY will appear.

  6. The results of the SQL query will now be visible to you.