How to Open Your Database in Excel in 5 Minutes using TransformZ (No Coding Required)

Introduction

Stop exporting CSV files. Stop copy-pasting stale data. Access your database in real time directly in Excel in just 5 minutes — no coding required.

What You'll Get

In this guide, you'll learn how to connect your database to Excel to access live data, regardless of which database management system you're using.

• ✅ Live database data accessible in Excel
• ✅ One-click refresh capability
• ✅ No code or technical setup
• ✅ Works with MySQL and PostgreSQL, more databases are coming soon.

What You Need

1. A TransformZ account: Sign up here.
2. A connected database: Guide to connect a database to TransformZ.
3. Microsoft Excel.

Steps to Open Your Database in Excel in 5 Minutes

Step 1: Connect Your Database to TransformZ

Already connected? Skip to Step 2.

New user? Follow this quick guide: How to Connect Your Database to TransformZ (5 minutes).

Step 2: Open the Excel Integration Wizard

1. Log in to your TransformZ dashboard
2. Click Data Sources in the left sidebar
3. Select your connected database
4. Scroll to the "OData API" section
5. Click the "Open in Excel" button

The Excel integration wizard opens.

Step 3: Get Your Power Query Script

From the Excel integration wizard, apply the following:

1. Select a table from the "Choose Table to Open" dropdown
2. Click "Copy" to copy the auto-generated "Power Query" script

The script looks like this:

// TransformZ Power Query Code
let
    Source = OData.Feed("https://app.transformz.ca/odata/PuhJMRfgOD/default/customer_addresses"),
    Table = Source
in
    Table

Keep the wizard open—you'll need the step-by-step guide next.

Step 4: Load Data into Excel

Steps to apply in Excel:

1. Open Excel: Launch Excel and create a new workbook or open an existing one where you want to import the data.
2. Access Power Query: Go to the 'Data' tab in Excel ribbon > Click on 'Get Data' > 'Blank Query'.
3. Paste the Script: In the Power Query Editor that opens, paste the copied script by replacing any existing code.
4. Load Your Data: Click 'Next' to close the Editor, then click 'Close & Load' to open your data into Excel.

Done! Your database data is now live in Excel, no batch processing needed.

Refresh Your Data Anytime

To get the latest data from your database:

1. Click anywhere in your data table
2. Go to Data tab
3. Click Refresh All

Your data updates instantly. No exports, no manual work.

Pro tip: Set up automatic refresh:

• Left-click on your table → go to Data tab → Properties
• Check "Refresh data when opening the file"

What You Can Do Now

With your database connected to Excel:

• Create pivot tables and charts on live data
• Build dashboards that update automatically
• Share the script with your team for consistent access
• Stop manual exports forever

Quick Troubleshooting

• "Data source is missing credentials"
  → Copy the complete script from TransformZ again

• "Query didn't refresh"
  → Check your database connection status in TransformZ

• "Too many rows"
  → Apply filters in Power Query Editor to reduce data

• Data types look wrong
  → Change column types in Power Query Editor

---

Need help?

• 📧 Email: info@automathing.ca
• 🌐 Website: transformz.ca
• 💼 LinkedIn: Follow Orléando Dassi

---

Related Articles:

• How to Connect Your Database to TransformZ
• The 5 Pillars of Data Accessibility