How to load data into Microsoft Excel from a database using Power Query

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How we enable Unified Auditing in Oracle Database - 7 October 2024
• What is PostgreSQL and how do we do a full installation - September 2, 2024
• How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024

The Microsoft Excel one of her first programs Microsoft . From its first versions, it had the ability to collaborate with software from other companies.

In this article we will explore how to connect Excel to any RDBMS and load data into its sheets.

The method we will use is the Power Query via ODBC.

For starters we'll have to make one ODBC Data Source on Windows.

User DSN - Add... - We select the Provider we want

We choose a name for ODBC and put the Server details

Details of the user who will connect to the database.

In excel now New Query – From Other Sources – From ODBC

*It is not necessary to use ODBC. We can choose From Database without using ODBC on the PC, but we will be limited to the RDBMS options currently provided by Excel.

* In case our RDBMS option is not there, we can choose from From other Sources The choise ODBC.

In the Data source name field we put the name with which we created the ODBC at the beginning. Also in the SQL statement field we can put SQL query for the data we want to load.

** In the case that the DSN box does not exist, we must write the DSN by hand in the Connection String field, e.g. DSN=sql .

Select Database and fill in username / password and connect.

Close & Load will load the data.

If we need to make any changes to the query for the data being loaded.

Workbook Queries – right click – edit – Advanced Editor

As we can see, the data was loaded on the page (Sheet) with the button Refresh All we can refresh the data. With his method Power Query we can have several Workbook Queries on several different pages (Sheets).

Of course we do not forget for security reasons to create users with minimum rights exclusively for this use.