How to Connect Microsoft Excel to Kaivo

By leveraging Power Query and Google's native ODBC driver, you can securely connect Microsoft Excel directly to your Kaivo-managed BigQuery data warehouse. This allows you to pull large datasets into your worksheets, build local reports, and set up automatic data refreshes.

Step 1: Install the ODBC Driver

1. Download and install the latest Google BigQuery ODBC driver onto your computer.

Step 2: Configure the ODBC Connection

1. Open the ODBC Data Sources application on your computer (you can find this quickly by typing "ODBC" into your Windows system search bar).

2. Navigate to the System DSN tab.

3. Select Google BigQuery from the system data sources list and click Configure.

4. In the configuration setup window, locate the Authentication section and set the OAuth Mechanism dropdown to User Authentication.

5. Click the Sign In button.

Step 3: Complete Google Authentication

1. Clicking sign-in will automatically open a secure authorization screen in your default web browser.

2. Log into the Google Account associated with your Kaivo workspace.

3. Review the access permissions and click Continue / Allow to grant the ODBC driver access to your data.

4. Once a success message appears in your browser, close the browser window and return to the ODBC configuration app. Your Refresh Token field will now be automatically populated.

Step 4: Map Your Kaivo Project and Dataset

1. In the lower section of the same ODBC configuration window, use the dropdown menus to select your specific data warehouse targets:

   • Catalog (Project): Select your unique Kaivo project ID.

   • Dataset: Select the specific dataset you want to connect to Excel.

2. Click OK to save your connection properties.

Step 5: Import Data into Excel Using Power Query

1. Launch Microsoft Excel and open your workbook.

2. Click on the Data tab in the top navigation ribbon.

3. Select Get Data ➔ From Other Sources ➔ From ODBC.

4. In the pop-up box, select Google BigQuery from the Data source name (DSN) dropdown menu.

5. (Optional) If you only want to load a specific subset of data, expand Advanced options and enter your custom SQL statement in the text area.

6. Click OK. Once the query executes and displays your data preview, click Load to populate your worksheet.

Step 6: Set Up Automatic Data Refreshes (Optional)

If you want your Excel sheet to stay completely up to date with the latest data pulled by Kaivo, you can automate your query intervals:

1. Under the Data tab, look for the Queries & Connections section.

2. Click the drop-down arrow beneath Refresh All and select Connection Properties....

3. In the Query Properties window under the Usage tab, locate the Refresh control block.

4. Check the box next to Refresh every and input your preferred interval time in minutes.

5. Click OK to save your preferences.

Note: Your data will refresh automatically within Excel as often as specified, but remember that fresh data is only pushed to your underlying BigQuery warehouse as frequently as your active Kaivo data connectors are scheduled to run.

If the setup feels a bit too intricate or you need help writing a custom SQL statement to segment your load data, don't hesitate to reach out to us using the support chat widget in the bottom right corner of your Kaivo application! Also check out other spreadsheet tools like Connecting Google Sheets to Kaivo for simpler setup. But if you need to stay inside the Microsoft stack, another way of going about it is to Connecting Power BI to Kaivo and then connect your Excel to the Power BI semantic model.